Export RMsis data to an Excel document

Owned by Sanjay Sahani
Last updated: Feb 12, 2019 by All (Deactivated)
4 min read

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:

  • RMsis version v1.8.9.2 or higher.
  • MS-Excel with developer module to write macro.

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. 
    • See attached document ListFilterDetails.xlsm for reference.
    • Enable macros and execute listRequirementsInFilter macro present in above file.
    • The macro uses vba-json(JsonConverter.bas) library to parse JSON response, you will have to import this module if you are writing your own macro.

    • The Excel document created in MS Excel which lists the requirements present in filter.

      The content is populated dynamically using excel macro after fetching it from RMsis.

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. 

    Excel Macro 
    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

 

 

 

Â