Connecting Microsoft Power BI to QPR Web Service

This page contains examples on how to query QPR data in Microsoft Power BI. To develop your own queries, it's recommended to familiarize yourself with the QPR Web Services. You can use the QPR Web Services documentation in the QPR Knowledge Base and the QPR Service Tester found at https://<your hostname>/<QPR version>/Portal/qpr.isapi.dll/wsforward/servicetester.aspx to refine your queries before you use them in Power BI.

Simple Query

The following is an example Power BI query that allows you to connect to the QPR MEA Web Service and import data. It first authenticates with the service to obtain a session ID, and then uses that session to query for specific data. The example query retrieves a list of all QPR EnterpriseArchitect/ProcessDesigner models, including their names and full IDs. This example requires that QPR Authentication is enabled on the server and that the user account used for authenticating is allowed to access the Web Service and data.

let
    authUrl = "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/Authenticate",
    authBody = "{""logOnName"":""qpr"",""password"":""demo""}",
    
    authResponse = Web.Contents(
        authUrl,
        [
            Headers = [
               #"Content-Type"="application/json",
               #"User-Agent"="PowerBI"
            ],
            Content = Text.ToBinary(authBody)
        ]
    ),
    
    sessionIdJson = Json.Document(authResponse),
    sessionId = sessionIdJson[d],

    dataUrl = "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/QueryObjects",
    
    dataBody = "{""sessionId"":""" & sessionId & """,""query"":""[PG].Models"",""attributes"":""name,fullid""}",

    dataResponse = Web.Contents(
        dataUrl,
        [
            Headers = [#"Content-Type"="application/json"],
            Content = Text.ToBinary(dataBody)
        ]
    ),
    
    JsonData = Json.Document(dataResponse),

    // Step 1: Get the list of ResultObjects
    ResultObjects = JsonData[d][ChildElements],

    // Step 2: Define a helper function to extract a named attribute
    // from a ResultObject's ChildElements list
    GetAttribute = (obj as record, attrName as text) =>
        let
            props = obj[ChildElements],
            match = List.Select(props, each _[Name] = attrName),
            result = if List.Count(match) > 0 then match{0}[Value] else null
        in
            result,

    // Step 3: Build a table by extracting each attribute directly
    FinalTable = Table.FromList(
        ResultObjects,
        Splitter.SplitByNothing(),
        null, null, ExtraValues.Error
    ),

    // Step 4: Add a column for each attribute using the helper function
    WithName = Table.AddColumn(FinalTable, "name", each GetAttribute([Column1], "name")),
    WithFullId = Table.AddColumn(WithName, "fullid", each GetAttribute([Column1], "fullid")),

    // Step 5: Remove the original Column1
    FinalResult = Table.RemoveColumns(WithFullId, {"Column1"})

in
    FinalResult

How the Query Works

The query performs the following steps:

• Authentication: The query sends a request with a username and password to the QPR Web Service authentication endpoint.
• Session ID: Upon successful authentication, a temporary session ID is received.
• Data Request: The session ID is used with a second request to the data query endpoint. This request specifies what data to retrieve.
• Data Processing: The query receives the data in JSON format, parses it, and transforms it into a structured table with columns for each requested attribute.

How to Use and Customize the Query

To use this query in Power BI, you will need to copy the M code into the Power BI Advanced Editor. You must customize the following parameters to match your specific QPR environment and data requirements:

• authUrl: The URL for the QPR Web Service authentication endpoint. Replace "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/Authenticate" with the URL of your QPR server's authentication service.
• authBody: The login credentials (username and password) required to access the web service. Modify the "logOnName" and "password" values. For example, to use the username "myuser" and password "mypassword", change the line to: authBody = "{""logOnName"":""myuser"",""password"":""mypassword""}",
• dataUrl: The URL for the QPR Web Service data query endpoint. Replace "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/QueryObjects" with the URL of your QPR server's query service.
• dataBody: Defines the specific data query to be executed. It contains the session ID, the query itself, and the attributes to be returned.
  • query: Modify the value to target different objects. The example "[PG].Models" retrieves all QPR EnterpriseArchitect/ProcessDesigner models. You can change this to query other objects as needed.
  • attributes: Modify the comma-separated list of attributes to retrieve. The example "name,fullid" returns the name and full ID of each queried object (which are models in the example). You can add or remove attributes based on what is available for the queried object type. See the Supported Attributes page in QPR Knowledge Base.

After customizing these variables, the query will connect to your QPR instance, retrieve the specified data, and load it into Power BI as a table for reporting and analysis.

Querying Navigator View Contents

Querying a navigator view's contents is done in two steps. Note that, as with the Simple Query example, you need to define the URL of your QPR server to the authUrl and dataUrl, and give a valid username and password as values to logOnName and password.
1. Query the navigator view settings. It's advisable to do this with the servicetester, as you won't use this query in Power BI.

• Query: [PG.1374444994].HierarchyRoots(hierarchy="navigatorviewsettings")
• Attributes: id, name, columns, columnnames

From the results, note the colums attribute contents of the navigator view you want to use. In this example, we want to query the "Risks" navigator view:

2. Query the full hierarchy in Power BI, with child elements as attribute using the columns attribute list from the previous servicetester query. In this example, the "Risks" navigator view in the Dentorex example model is queried.

let
    // 1. Define your full URLs (avoiding RelativePath with ISAPI)
    authUrl = "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/Authenticate",
    dataUrl = "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/QueryObjects",

    // 2. Authenticate to get a Session ID
    authResponse = Web.Contents(
        authUrl,
        [
            Headers = [
                #"Content-Type" = "application/json",
                #"Accept" = "application/json",
                #"User-Agent" = "PowerBI"
            ],
            // Json.FromValue ensures the body is perfectly formatted JSON
            Content = Json.FromValue([
                logOnName = "qpr",
                password = "demo"
            ])
        ]
    ),
    sessionIdJson = Json.Document(authResponse),
    sessionId = sessionIdJson[d],

    // 3. Define Query and Attributes. Attributes are the columns from the Servicetester query.
    AttributeString = "RiskSubCategory.values,name,description,RiskOwner.values",
    QueryString = "[PG.1374444994].HierarchyRoots(viewsettings=""Risks"").Related(viewsettings=""Risks"", recursive=true, keeporiginals=true)",

    // 4. Request Data
    dataResponse = Web.Contents(
        dataUrl,
        [
            Headers = [
                #"Content-Type" = "application/json",
                #"Accept" = "application/json",
                #"User-Agent" = "PowerBI"
            ],
            // This will automatically escape the quotes in your QueryString correctly (e.g. \"Risks\")
            Content = Json.FromValue([
                sessionId = sessionId,
                query = QueryString,
                attributes = AttributeString
            ])
        ]
    ),

    // 5. Parse and Transform Results
    JsonData = Json.Document(dataResponse),
    ResultObjects = JsonData[d][ChildElements],

    GetAttribute = (obj as record, attrName as text) =>
        let
            props = obj[ChildElements],
            match = List.Select(props, each _[Name] = attrName),
            result = if List.Count(match) > 0 then match{0}[Value] else null
        in
            result,

    AttributeList = Text.Split(AttributeString, ","),

    TableRows = List.Transform(ResultObjects, (obj) =>
        Record.FromList(
            List.Transform(AttributeList, each GetAttribute(obj, _)),
            AttributeList
        )
    ),

    FinalResult = Table.FromRecords(TableRows)
in
    FinalResult

The result:

Querying QPR Metrics

To query QPR Metrics data in Power BI, you can copy the M code examples below into the Power BI Advanced Editor. You must customize the following parameters to match your specific QPR environment and data requirements:

• authUrl: The URL for the QPR Web Service authentication endpoint. Replace "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/Authenticate" with the URL of your QPR server's authentication service.
• authBody: The login credentials (username and password) required to access the web service. Modify the "logOnName" and "password" values. For example, to use the username "myuser" and password "mypassword", change the line to: authBody = "{""logOnName"":""myuser"",""password"":""mypassword""}",
• dataUrl: The URL for the QPR Web Service data query endpoint. Replace "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/QueryObjects" with the URL of your QPR server's query service.
• dataBody: Defines the specific data query to be executed. It contains the session ID, the query itself, and the attributes to be returned.
• query: Modify the value to target different objects. The example "[PG].Models" retrieves all QPR EnterpriseArchitect/ProcessDesigner models. You can change this to query other objects as needed.
• AttributeString: Modify the comma-separated list of attributes to retrieve. The example "name,fullid" returns the name and full ID of each queried object (which are models in the example). You can add or remove attributes based on what is available for the queried object type. See the Supported Attributes page in QPR Knowledge Base.

Querying Latest Data

The following query will get all measures and their latest values and periods in the USA Sales Office scorecard in the Dentorex Group Scorecard model.

let
    authUrl = "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/Authenticate",
    authBody = "{""logOnName"":""qpr"",""password"":""demo""}",

    authResponse = Web.Contents(
        authUrl,
        [
            Headers = [
                #"Content-Type" = "application/json",
                #"User-Agent" = "PowerBI"
            ],
            Content = Text.ToBinary(authBody)
        ]
    ),

    sessionIdJson = Json.Document(authResponse),
    sessionId = sessionIdJson[d],

    dataUrl = "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/QueryObjects",
    AttributeString = "name, scorecard.name, value, periodname",
    dataBody = "{""sessionId"":""" & sessionId & """,""query"":""[SC.1938773693.21].SubObjects"",""attributes"":""" & AttributeString & """}",

    dataResponse = Web.Contents(
        dataUrl,
        [
            Headers = [
                #"Content-Type" = "application/json",
                #"User-Agent" = "PowerBI"
            ],
            Content = Text.ToBinary(dataBody)
        ]
    ),

    JsonData = Json.Document(dataResponse),
    ResultObjects = JsonData[d][ChildElements],

    GetAttribute = (obj as record, attrName as text) =>
        let
            props = obj[ChildElements],
            match = List.Select(props, each _[Name] = attrName),
            result = if List.Count(match) > 0 then match{0}[Value] else null
        in
            result,

    AttributeList = List.Transform(Text.Split(AttributeString, ","), each Text.Trim(_)),
    TableRows = List.Transform(ResultObjects, (obj) =>
    Record.FromList(
        List.Transform(AttributeList, each GetAttribute(obj, _)),
        AttributeList
    )
),
    FinalResult = Table.FromRecords(TableRows),
    #"Sorted Rows" = Table.Sort(FinalResult,{{"name", Order.Ascending}})
in
    #"Sorted Rows"

Querying Data From a Specific Period

The following query will get all measures and their 2025 values and periods in the USA Sales Office scorecard in the Dentorex Group Scorecard model.

let
    // First, the authentication
    authUrl = "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/Authenticate",

    authResponse = Web.Contents(
        authUrl,
        [
            Headers = [
                #"Content-Type" = "application/json",
                #"Accept" = "application/json",
                #"User-Agent" = "PowerBI"
            ],
            Content = Json.FromValue([
                logOnName = "qpr",
                password = "demo"
            ])
        ]
    ),

    // Get the authentication response and store it to sessionId
    sessionIdJson = Json.Document(authResponse),
    sessionId = sessionIdJson[d],

    dataUrl = "https://<your hostname>/<QPR version>/Portal/QPR.Isapi.dll/wsforward/MainService.svc/webHttp/QueryObjects",

    // We define attributes. The "value" and "periodname" attributes contain the period definition and we use the "as" to define the property names (see the AttributeList below).
    // You can also use "current", "previous", "next", "latest", "latestforall", "latestforseries", "firstforseries", or "lastforseries" as the period definition. See the Supported Attributes topic in the QPR Web Services documentation.
    AttributeString = "name, scorecard.name, value(period=""2025"", as=""value""), periodname(period=""2025"", as=""periodname"")",

    // These are the property names expected in the returned JSON
    AttributeList = {"name", "scorecard.name", "value", "periodname"},

    // Send the query to QPR Web Services
    dataResponse = Web.Contents(
        dataUrl,
        [
            Headers = [
                #"Content-Type" = "application/json",
                #"Accept" = "application/json",
                #"User-Agent" = "PowerBI"
            ],
            Content = Json.FromValue([
                sessionId = sessionId,
                query = "[SC.1938773693.21].SubObjects",
                criteria = "type=2",
                sortBy = "",
                attributes = AttributeString,
                options = ""
            ])
        ]
    ),

    // Handle the JSON data that QPR Web Service returns
    JsonData = Json.Document(dataResponse),

    ResultObjects = JsonData[d][ChildElements],

    GetAttribute = (obj as record, attrName as text) =>
        let
            props = obj[ChildElements],
            match = List.Select(props, each _[Name] = attrName),
            result = if List.Count(match) > 0 then match{0}[Value] else null
        in
            result,

    TableRows = List.Transform(ResultObjects, (obj) =>
        Record.FromList(
            List.Transform(AttributeList, each GetAttribute(obj, _)),
            AttributeList
        )
    ),

    FinalResult = Table.FromRecords(TableRows),

    #"Sorted Rows" = Table.Sort(FinalResult, {{"name", Order.Ascending}})

in
    #"Sorted Rows"