Share via

Report Services support for DAX function ROLLUPADDISSUBTOTAL

Andy May 56 Reputation points
2021-02-12T00:26:54.96+00:00

I am attempting to create an SSRS report which will query an SSAS Tabular Model. I want to get a table in the report that has a subtotal row with percentages. Unfortunately while the tabular model has measures for the amount (numerator) and another for the percentage, it doesn't have the number that we need the percentage of (the denominator). The model is used to populate a Power BI RS report, and looking at the DAX query that Power BI runs uses ROLLUPADDISSUBTOTAL to get a subtotal row. I can create a DAX query in report services that gets the values in rows for the table, but because some of the columns are percentages creating a normal total row in the table produces sums for the percentages which are basically nonsense.

I Tried to manually edit the DAX query to add the subtotal row using this function, in the same way that Power BI does. In the query designer, the query runs and returns what I was expecting, but when I save the query and try to preview the report it locks up, and just stops working, it doesn't throw an exception just does nothing. If I return to design and remove the function from the query it works fine

Help please?

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,845 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,253 questions
Accepted answer
  1. Lukas Yu -MSFT 5,816 Reputation points
    2021-02-15T05:05:59.107+00:00

    Hi,

    What DAX query have you used for the percentage column to add up? Could you share us the not working ones ?
    Have you write the DAX query at SSRS Datasouce Editor or elsewhere?

    If you could share some sample, that could be very helpful to clarify the case.

    Regards,
    Lukas

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Joyzhao-MSFT 15,566 Reputation points
    2021-02-15T02:24:17.053+00:00

    Hi AndyMay,
    @Andy May ，
    I am sorry for the thoughtless reject of the question. My thought was that your question description is hard to diagnose which part went wrong, since the DAX query / data model / details are not quite clear, and this forum is not professional with the PBIRS/PBI design. Also I have not understood the "percentage column" in the description.

    From the current description , we want to find the subtotal percentage of a measure, right ?
    You could try directly calculate the subtotal percentage in the tabular model instead of adding them in SSRS/PBIRS.
    You can find similar request in these blog Calculating Percent of Subtotal or the DAX expression in this thread :Percentage of subtotal
    If you still find no solution for this case, you could provide some sample data and your query expression so I could do some test and offer more specific help.
    Best Regards,
    Joy

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments