Remove Row Total Paginated Report

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?

Paginated Report Row Total

d_gosbell
5 years ago
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]

4 Replies

d_gosbell
Super User
5 years ago
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]
d_gosbell
Super User
5 years ago
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
- amirghaderi
  Helper IV
  5 years ago
  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],
        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]
amirghaderi
Helper IV
5 years ago
Thank you, this worked!

Forum Discussion

Remove Row Total Paginated Report

4 Replies

Related content

Paginated Report

Paginated report

Paginated Report headers not repeating

Paginated Reports

Paginated Report and PQO