none
Tabular Model Hierarchy and SSRS

    Question

  • Hi All,

    I have a SSAS Tabular Model that contains a hierarchy like;

    CEO

    --CFO

    ----FINANCIAL CONTROLLER

    ----FINANCE MANAGER

    ------FINANCE ANALYST

    --CIO

    ----IT MANAGER

    ------IT ANALYST

    I have a FACT table that contains salary information which is rolled up by the above hierarchy. I can see my data through Excel and now want to build a SSRS Report.

    I'm guessing I take the DAX generated by the Model Browser and create a dataset in SSRS with a @paratmeter for the hierarchy. However, I notice that the DAX generated is dynamic meaning if I select something at the bottom of the hierarchy the syntax is including a filter line for each of the parents above.

    How do I go about doing this, i.e. implementing the DAX syntax to be dynamic in my dataset?

    Thanks,

    Moe


    • Edited by MSBIKB Monday, May 14, 2018 9:05 AM
    Monday, May 14, 2018 9:03 AM

All replies

  • Hi Moe,

    Thanks for your question.

    >>>How do I go about doing this, i.e. implementing the DAX syntax to be dynamic in my dataset?
    According to your description, it seems like a Parent-Child hierarchies. Please refer to below blog about Parent-Child Hierarchies:
    https://www.daxpatterns.com/parent-child-hierarchies/

    If it is not a parent child hierarchy, then please refer to below blog:
    https://www.red-gate.com/simple-talk/sql/database-administration/using-dax-to-create-ssrs-reports-the-basics/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, May 15, 2018 3:13 AM
    Moderator
  • Hi Willson,

    Thanks for the links... I have seen these articles before. Let me expand on my question.

    My tabular model has a FactSalary table and a DimRole table. In my tabular model I have defined the below organisation hierarchy using data from DimRole;

    CEO
    --CFO
    ----FINANCIAL CONTROLLER
    ----FINANCE MANAGER
    ------FINANCE ANALYST
    --CIO
    ----IT MANAGER
    ------IT ANALYST

    I would like to create a shared dataset (tabular) which returns [Sum of Salary] from FactSalary based on input parameter @pJobRole. I'm expecting the input parameter to query the above organisation hierarchy.

    I noted in SQL Server Management Studio that when browsing the tabular model the DAX auto generated will change given which part of the above hierarchy I select.

    This leads to my question.... if the syntax is changing at runtime how can one incorporate DAX in a shared dataset for the above scenario?

    Thanks,
    Moe
    Tuesday, May 15, 2018 5:35 AM
  • Hi Moe,

    Thanks for your response.

    >>>This leads to my question.... if the syntax is changing at runtime how can one incorporate DAX in a shared dataset for the above scenario?

    Would you mind elaborating more about above question? I can not understand that part " the DAX auto generated will change given which part of the above hierarchy I select ". It is much better if you can share the DAX formula that generated from SQL Server Management Studio.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, May 16, 2018 5:19 AM
    Moderator