topic Remove Row Total Paginated Report in Report Server

Remove Row Total Paginated Report

amirghaderi — Fri, 21 May 2021 05:47:56 GMT

I have a paginated report which shows the row total as the first line. I have been trying to remove it from the report and no luck yet. Any idea about it?

Re: Remove Row Total Paginated Report

d_gosbell — Fri, 21 May 2021 07:29:54 GMT

That looks like it's probably being generated by your query. So either remove the code that is generating the total (or show us the query so we can tell you how to do that). Or apply a filter to your table to exclude rows with a blank in the Supplier column. Fixing the query is probably the better option as it means the server will have to do less work to return the results

Re: Remove Row Total Paginated Report

amirghaderi — Fri, 21 May 2021 08:53:26 GMT

This is thequery:

// DAX Query

DEFINE

VAR __DS0FilterTable =

TREATAS({"Hours"}, 't_PP11'[UOM])

VAR __DS0FilterTable2 =

FILTER(

KEEPFILTERS(VALUES('Date'[weekending])),

AND('Date'[weekending] >= DATE(2021, 5, 14), 'Date'[weekending] < DATE(2021, 5, 21))

)

VAR __DS0Core =

SUMMARIZECOLUMNS(

ROLLUPADDISSUBTOTAL(

ROLLUPGROUP(

't_PP11'[Employee or Supplier],

't_class'[Adjusted Exp. Cat. Name],

't_Project'[Project Engineer],

't_PP11'[Booking Code],

't_Project'[Job Code],

't_Project'[Job Description],

'Date'[weekending]

), "IsGrandTotalRowTotal"

__DS0FilterTable,

__DS0FilterTable2,

"SumActual_MH", CALCULATE(SUM('t_PP11'[Actual MH]))

)

VAR __DS0PrimaryWindowed =

TOPN(

502,

__DS0Core,

[IsGrandTotalRowTotal],

't_Project'[ Project Engineer],

't_PP11'[Employee or Supplier],

't_class'[Adjusted Exp. Cat. Name],

't_PP11'[Booking Code],

't_Project'[Job Code],

't_Project'[Job Description],

'Date'[weekending],

)

EVALUATE

__DS0PrimaryWindowed

ORDER BY

[IsGrandTotalRowTotal] DESC,

't_Project'[ Project Engineer] DESC,

't_PP11'[Employee or Supplier],

't_class'[Adjusted Exp. Cat. Name],

't_PP11'[Booking Code],

't_Project'[Job Code],

't_Project'[Job Description],

'Date'[weekending]

Re: Remove Row Total Paginated Report

d_gosbell — Fri, 21 May 2021 09:01:18 GMT

In the __DS0Core variable you can see the calls to ROLLUPGROUP and ROLLUPISSUBTOTAL functions which are generating this extra total row.

Obviously without your data model I cannot test the query below, but I think I've removed those two functions correctly.

// DAX Query
DEFINE
    VAR __DS0FilterTable =
        TREATAS ( { "Hours" }, 't_PP11'[UOM] )
    VAR __DS0FilterTable2 =
        FILTER (
            KEEPFILTERS ( VALUES ( 'Date'[weekending] ) ),
            AND (
                'Date'[weekending] >= DATE ( 2021, 5, 14 ),
                'Date'[weekending] < DATE ( 2021, 5, 21 )
            )
        )
    VAR __DS0Core =
        SUMMARIZECOLUMNS (
            't_PP11'[Employee or Supplier],
            't_class'[Adjusted Exp. Cat. Name],
            't_Project'[Project Engineer],
            't_PP11'[Booking Code],
            't_Project'[Job Code],
            't_Project'[Job Description],
            'Date'[weekending],
            __DS0FilterTable,
            __DS0FilterTable2,
            "SumActual_MH", CALCULATE ( SUM ( 't_PP11'[Actual MH] ) )
        )
    VAR __DS0PrimaryWindowed =
        TOPN (
            502,
            __DS0Core,
            [IsGrandTotalRowTotal], 0,
            't_Project'[ Project Engineer], 0,
            't_PP11'[Employee or Supplier], 1,
            't_class'[Adjusted Exp. Cat. Name], 1,
            't_PP11'[Booking Code], 1,
            't_Project'[Job Code], 1,
            't_Project'[Job Description], 1,
            'Date'[weekending], 1
        )
EVALUATE
__DS0PrimaryWindowed
ORDER BY
    [IsGrandTotalRowTotal] DESC,
    't_Project'[ Project Engineer] DESC,
    't_PP11'[Employee or Supplier],
    't_class'[Adjusted Exp. Cat. Name],
    't_PP11'[Booking Code],
    't_Project'[Job Code],
    't_Project'[Job Description],
    'Date'[weekending]

Re: Remove Row Total Paginated Report

amirghaderi — Fri, 21 May 2021 09:23:11 GMT

Thank you, this worked!