Forum Discussion

MarcR's avatar
MarcR
VIP
4 years ago

Read/ Analyze contents of Binary columns

Hi there,  For a client i want to do a detailed analysis of the data (numbers) in the database. We want to investigate the percentage of numbers < 0.01. However, when i use the Data records i see t...
Planning
ChristianW's avatar
ChristianW
Valued Contributor

I just wanted to propose a fdx query for this request. Accessing the data table directly is not a sustainable way of doing it.

Sai_Maganti's avatar
Sai_Maganti
Contributor II
4 years ago

Ah thanks and noted Christian. Are you able to provide some samples of FDX BR queries? I tried to find some help in the documentation and there is nothing much.

  • ChristianW's avatar
    ChristianW
    Valued Contributor
    4 years ago

    Here is something I used for a custom IC matching report:

    'Retrieve the data for the first query
Dim subQueryDataTable As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si, cubeName, $"E#{entityName}.Base", "local", scenarioTypeId, $"S#{scenarioName}", $"T#{timeName}", "YTD", True, "Flow='None'", 4, True)

    And here is the explanation for the parameters:

     

    • nizar's avatar
      nizar
      New Contributor
      3 years ago

      Hi Christian,

       

      can you please give us an exemple with reel value ?

      i am strugle with this parametre, didn't find the correct syntaxt to get it.

      scenarioTypeId

      thanks

    • nizar's avatar
      nizar
      New Contributor
      3 years ago

      Hi Christian,

       

      i am using it like this : 

       

      Dim subQueryDataTable As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si, "ContributionMargin", "E#{C010101}.Base", "local",brapi.Finance.Scenario.GetScenarioType(si,0).Id, "S#{Actual}", "T#{2021M10}", "YTD", True, "Flow='None'", 4, True)

       

      can you please tell me what is wrong ?

       

      many tahnks

      • ChristianW's avatar
        ChristianW
        Valued Contributor
        2 years ago

        If you like to use the curley brackets {} with strings, you need to add a $ sign infront of the quotes, like this: $"E#{C010101}.Base", $"S#{Actual}" or $"T#{2021M10}". But if you don't use variables at all, there is no need for the curley brackets at all.

        Can you try this: Dim subQueryDataTable As DataTable = BRApi.Import.Data.FdxExecuteDataUnit(si, "ContributionMargin", "E#C010101.Base", "local",brapi.Finance.Scenario.GetScenarioType(si,0).Id, "S#Actual", "T#2021M10", "YTD", True, "Flow='None'", 4, True)

        Cheers

    • RafMarques's avatar
      RafMarques
      New Contributor III
      2 years ago

      Hi ChristianW,

      This works perfect!

      Is there any change to join the names with descriptions? 

      Example: Acc1 - Expenses

       

      Thanks

      Raf

      • ChristianW's avatar
        ChristianW
        Valued Contributor
        2 years ago

        Hi Raf

        Yes, you can loop the rows in the business rule and add the descriptions for the needed dimension members.

        Cheers