The following example demonstrates the use of RMSis API's which is used to create a report in MS-Excel after dynamically fetching the data from RMsis using macros.

Pre-Requisites:

Following steps were taken:

  1. Populate data in RMsis

                                                                            The above RMsis screen displays list of requirements present in a filter.
  2. Create macro in MS-Excel to fetch the data from RMsis and populate it into the Excel document. 

The code of the excel macro is mentioned below,  it contains following steps:

  1. Login into JIRA user a valid username and password.
  2. Fetch data from RMsis using API's.

  3. Insert the formatted data into excel document. 

    Public Sub listRequirementsInFilter()
'Add header column names
Cells(1, 1).value = "ID"
Cells(1, 2).value = "Summary"
Cells(1, 3).value = "Status"
Cells(1, 4).value = "Priority"
Cells(1, 5).value = "Criticality"
Cells(1, 5).value = "Release"

Dim requirementIds As Collection, requirementId As Integer
Dim requirementKey, requirementSummary As String
Dim requirementStatus, requirementPriority, requirementCriticality As Integer
Dim RequirementStatuses, Priorities, Criticalities As Dictionary

rmsisApiString = "{getPlannedRequirementStatuses{id,name}}"
ResponseText = GetRequestfromRMsis(rmsisApiString)
Set StatusJSON = ParseJson(ResponseText)
Set RequirementStatuses = New Dictionary
RequirementStatuses.Add -1, ""
Dim key As Integer, value As String
For Each Item In StatusJSON("data")("getPlannedRequirementStatuses")
    key = Item("id")
    value = Item("name")
RequirementStatuses.Add key, value
Next

rmsisApiString = "{getPriorities{id,name}}"
ResponseText = GetRequestfromRMsis(rmsisApiString)
Set StatusJSON = ParseJson(ResponseText)
Set Priorities = New Dictionary
Priorities.Add -1, ""
For Each Item In StatusJSON("data")("getPriorities")
    key = Item("id")
    value = Item("name")
Priorities.Add key, value
Next

rmsisApiString = "{getCriticalities{id,name}}"
ResponseText = GetRequestfromRMsis(rmsisApiString)
Set StatusJSON = ParseJson(ResponseText)
Set Criticalities = New Dictionary
Criticalities.Add -1, ""
For Each Item In StatusJSON("data")("getCriticalities")
    key = Item("id")
    value = Item("name")
Criticalities.Add key, value
Next

rmsisApiString = "{getPlannedRequirementsByFilter(projectId:1,filterId:1)}"
ResponseText = GetRequestfromRMsis(rmsisApiString)
Set JSON = ParseJson(ResponseText)
Set requirementIds = JSON("data")("getPlannedRequirementsByFilter")

For i = 1 To requirementIds.Count
    requirementId = requirementIds(i)
    rmsisApiString = "{getRequirementById(id:reqId){key,summary,requirementStatus{id},priority{id},criticality{id}}}"
    rmsisApiString = Replace(rmsisApiString, "reqId", requirementId)
    ResponseText = GetRequestfromRMsis(rmsisApiString)
    Set JSON = ParseJson(ResponseText)
    requirementKey = JSON("data")("getRequirementById")("key")
    requirementSummary = JSON("data")("getRequirementById")("summary")
    
    If IsNull(JSON("data")("getRequirementById")("requirementStatus")) Then
        requirementStatus = -1
    Else
        requirementStatus = JSON("data")("getRequirementById")("requirementStatus")("id")
    End If
    
    If IsNull(JSON("data")("getRequirementById")("priority")) Then
        requirementPriority = -1
    Else
        requirementPriority = JSON("data")("getRequirementById")("priority")("id")
    End If
    
    If IsNull(JSON("data")("getRequirementById")("criticality")) Then
        requirementCriticality = -1
    Else
        requirementCriticality = JSON("data")("getRequirementById")("criticality")("id")
    End If
    
    Cells(i + 1, 1).value = requirementKey
    Cells(i + 1, 2).value = requirementSummary
    Cells(i + 1, 3).value = RequirementStatuses.Item(requirementStatus)
    Cells(i + 1, 4).value = Priorities.Item(requirementPriority)
    Cells(i + 1, 5).value = Criticalities.Item(requirementCriticality)
Next i
Call FormatHeaderRow
End Sub

'Function to fetch details using RMsis graph APIs
Function GetRequestfromRMsis(rmsisApiString) As String
Dim stringURL As String
Dim auth
auth = "(YWRtaW46YWRtaW4=)"
Set restRequest = CreateObject("Msxml2.ServerXMLHTTP.6.0")
stringURL = "http://10.0.1.15:8080/rest/service/latest/rmsis/graphql?query="
restRequest.Open "GET", stringURL & rmsisApiString, False
restRequest.SetRequestHeader "Content-Type", "application/json"
restRequest.SetRequestHeader "Authorization", "Basic " & auth
restRequest.Send
If restRequest.status = "401" Then
    MsgBox "Not authorized"
    Else
    'MsgBox restRequest.ResponseText
    GetRequestfromRMsis = restRequest.ResponseText
End If
End Function

Private Sub FormatHeaderRow()
' FormatHeaderRow Macro
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
End Sub