Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Connection to IBM Cognos TM1 via Web using Restapi

Hello,    I am a new bee in PBI. I have tried TM1 Restpi in GetData-> Web-> Advanced option in PBI Desktop. 'Get' Method mdx query executed succesfully and pulled the dimension elements. But now I ...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Thanks for your support@

     

    I have removed the paresdJSON.

     

    Fianlly the below code works for me and return the value:

     

    let
    url = "http://host:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
    body = "{
    ""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
    }",
    Source = Json.Document(Web.Contents(url, [Headers=[#"Authorization"="Basic YWRtaW46", #"Content-Type"="application/json"], Content = Text.ToBinary(body) 
    ])),
    in
    Source

     

     

     

v-lid-msft's avatar
v-lid-msft
Community Support
6 years ago

Hi Anonymous ,

 

We can try to put the json into the body by using the following queries to meet your requirement:

 

let
url = "http://localhost:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Parsed_JSON = Json.Document(body),
Source = Json.Document(Web.Contents(url,
[
Headers= [ #"Authorization"="Basic sddRtaW46", #"ContentType"="application/json; charset=UTF-8"], Content = Parsed_JSON
]
))
in
#"Source"

 

Have you tried to verify the request by posting this request with other tools?


Best regards,

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi, 

    Thanks for your reply. 

    I have tried your solution, but end up with "Expression.Error: We cannot convert a value of type Record to type Text."

     

    Any suggestion on this.

     

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi Anonymous ,

       

      Sorry for our late reply, Could you please try to use the following query ?

       

      let
url = "http://localhost:8000/api/v1/ExecuteMDX?$expand=Axes($select=Ordinal;$expand=Tuples($count;$expand=Members($select=Name))),Cells($select=Ordinal,Value)",
body = "{
""MDX"": ""SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])""
}",
Parsed_JSON = Binary.FromText(body),
Source = Json.Document(Web.Contents(url,
[
Headers= [ #"Authorization"="Basic sddRtaW46", #"ContentType"="application/json; charset=UTF-8"], Content = Parsed_JSON
]
))
in
#"Source"

       


      Best regards,

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi, Thanks for your reply.

         

        Again Expression.Error: Invalid binary encoding.
        Details:
        {
        "MDX": "SELECT {([kh_bgkto].[zTest1])} on 0,{([kh_ges].[test])} on 1 FROM [kh_bg] WHERE ([kh_verf].[KV1],[kh_jahr].[J02],[kh_perio].[P12C],[kh_versi].[V01],[kh_kons].[B01],[kh_konz].[KF01])"
        }

         

        Any suggestions. Thanks