How calculate a page header and footer running value in Telerik Reporting

Question

In Telerik Report Designer (desktop version), I am having the worst time trying to figure how to find a proper scope name to get the sum of a given expression for every dataitem processing until the end of each page.

For the sake of simplicity, my datasource only has 1 column: saleAmount.

Sample values that fit on page 1

• 100
• 200
• 150

Page footer running sum of page 1: 450

Remaining values that fit on page 2

• 20
• 30

Page footer running sum of page 2: 500

I also need to, at beginning of each page (starting on second page), present the accumulated sum processed until the end of previous page.

According to this Telerik Reporting article, it seems I should use RunningValue(scope, Sum(Fields.saleAmount))

While RunningValue function works fine on detail section and group footer section, presenting the accumulated sum until the end of that group, it does not work as expected on the following cases:

• On page footer section, instead of presenting the sum since first datasource item until the last item processed on current page, it presents the sum of all records contained in the datasource, which seems a major flaw;
• On page header section, instead of presenting the sum since first datasource item until the last item processed on the previous page, it presents the sum of all records contained in the datasource, which also seems a major flaw;

I've already spent a significant time trying to figure out what exactly should I use as scope parameter.

I am seeking a solution based on standard Telerik Report capabilities, I mean, without having to implement a custom class to achieve the result.

Here is my sample report definition (.trdx), loadable into Telerik Report Designer

<?xml version="1.0" encoding="utf-8"?>
<Report DataSourceName="csvDataSource1" Width="17cm" Name="MyPageRunningTotalSampleReport" xmlns="http://schemas.telerik.com/reporting/2021/2.0">
  <DataSources>
    <CsvDataSource RecordSeparators="&#xD;&#xA;" FieldSeparators="," Name="csvDataSource1">
      <Source>
        <String>QSwxMDANCkEsMjAwDQpCLDE1MA0KQiwyMA0KQywzMA==</String>
      </Source>
      <Columns>
        <DataColumn Name="category" />
        <DataColumn Name="amount" Type="Decimal" />
      </Columns>
    </CsvDataSource>
  </DataSources>
  <Items>
    <PageHeaderSection Height="0.8cm" Name="pageHeaderSection1">
      <Style>
        <BorderStyle Default="Solid" />
      </Style>
      <Items>
        <TextBox Width="3cm" Height="0.6cm" Left="14cm" Top="0.2cm" Value="RunningSum" Name="textBox6">
          <Style TextAlign="Right" VerticalAlign="Middle">
            <Font Bold="True" />
          </Style>
        </TextBox>
        <TextBox Width="2.7cm" Height="0.6cm" Left="0cm" Top="0.2cm" Value="pageHeader" Name="textBox12">
          <Style TextAlign="Left" VerticalAlign="Middle">
            <Font Bold="True" />
          </Style>
        </TextBox>
        <TextBox Width="3cm" Height="0.6cm" Left="10.8cm" Top="0.2cm" Value="Amount" Name="textBox4">
          <Style TextAlign="Right" VerticalAlign="Middle">
            <Font Bold="True" />
          </Style>
        </TextBox>
      </Items>
    </PageHeaderSection>
    <DetailSection Height="0.6cm" Name="detailSection1">
      <Items>
        <TextBox Width="3cm" Height="0.6cm" Left="10.8cm" Top="0cm" Value="= Fields.amount" Name="textBox1">
          <Style TextAlign="Right" VerticalAlign="Middle" />
        </TextBox>
        <TextBox Width="3cm" Height="0.6cm" Left="14cm" Top="0cm" Value="= RunningValue(&quot;MyPageRunningTotalSampleReport&quot;, sum(Fields.amount))" Name="textBox5">
          <Style TextAlign="Right" VerticalAlign="Middle" />
        </TextBox>
        <TextBox Width="2.7cm" Height="0.6cm" Left="0cm" Top="0cm" Value="detailSection1" Name="textBox13">
          <Style TextAlign="Justify" VerticalAlign="Middle">
            <Font Bold="True" />
          </Style>
        </TextBox>
      </Items>
    </DetailSection>
    <PageFooterSection Height="8.1cm" Name="pageFooterSection1">
      <Style>
        <BorderStyle Default="Solid" Top="Solid" Bottom="Solid" Left="Solid" Right="Solid" />
      </Style>
      <Items>
        <TextBox Width="3cm" Height="0.6cm" Left="14cm" Top="1.1cm" Value="= RunningValue(&quot;missingScope&quot;, sum(Fields.amount))" Name="textBox2">
          <Style Color="Red" TextAlign="Right" VerticalAlign="Top">
            <Font Size="14pt" Bold="True" />
          </Style>
        </TextBox>
        <TextBox Width="3cm" Height="0.6cm" Left="14cm" Top="2.5cm" Value="= Exec(&quot;MyPageRunningTotalSampleReport&quot;, sum(Fields.amount))" Name="textBox3">
          <Style TextAlign="Right" VerticalAlign="Middle">
            <Font Size="14pt" Bold="True" />
          </Style>
        </TextBox>
        <TextBox Width="2.7cm" Height="0.6cm" Left="0cm" Top="0cm" Value="pageFooter" Name="textBox14">
          <Style TextAlign="Left" VerticalAlign="Middle">
            <Font Bold="True" />
          </Style>
        </TextBox>
        <TextBox Width="4.2cm" Height="0.6cm" Left="9.6cm" Top="2.5cm" Value="Sum until end of report" Name="textBox15">
          <Style TextAlign="Right" VerticalAlign="Middle">
            <Font Bold="False" />
          </Style>
        </TextBox>
        <HtmlTextBox Width="11.4cm" Height="1.1cm" Left="2.4cm" Top="1.1cm" Value="&lt;span style=&quot;font-size: 9pt&quot;&gt;Running total since the begining of first page until the end of page &lt;strong&gt;{PageNumber}&lt;/strong&gt;&lt;/span&gt;" Name="htmlTextBox2">
          <Style TextAlign="Right" />
        </HtmlTextBox>
        <TextBox Width="3cm" Height="0.6cm" Left="14cm" Top="0.103cm" Value="= PageExec('detailSection1', sum(Fields.amount))" Name="textBox9">
          <Style TextAlign="Right" VerticalAlign="Middle">
            <Font Size="14pt" Bold="True" />
          </Style>
        </TextBox>
        <HtmlTextBox Width="10.7cm" Height="0.6cm" Left="3.1cm" Top="0.103cm" Value="Total of all detail items shown on page &lt;strong&gt;{PageNumber}&lt;/strong&gt;" Name="htmlTextBox3">
          <Style TextAlign="Right" VerticalAlign="Middle" />
        </HtmlTextBox>
      </Items>
    </PageFooterSection>
  </Items>
  <PageSettings PaperKind="A6" Landscape="True" ContinuousPaper="False">
    <Margins>
      <MarginsU Left="2cm" Right="2cm" Top="2cm" Bottom="2cm" />
    </Margins>
  </PageSettings>
  <StyleSheet>
    <StyleRule>
      <Style>
        <Padding Left="2pt" Right="2pt" />
      </Style>
      <Selectors>
        <TypeSelector Type="TextItemBase" />
        <TypeSelector Type="HtmlTextBox" />
      </Selectors>
    </StyleRule>
  </StyleSheet>
</Report>

Answer 1

So, you're dealing with Telerik Report Designer, trying to get those running sums in the page header and footer, right? It seems like Telerik doesn't make it super easy out of the box, but we can add a bit of custom code to make it work.

First off, you can create a space for some special code. We'll use this to keep track of the running sum as we go through your data.

<Code>
  <![CDATA[
    Dim pageRunningSum As Decimal = 0

    Public Function UpdatePageRunningSum(amount As Decimal) As Decimal
        pageRunningSum += amount
        Return pageRunningSum
    End Function
  ]]>
</Code>

Now, whenever you process an amount in your report, this code is going to update the running sum in the background.

Next up, let's use this backstage code in your report. In the PageHeaderSection, we're going to make sure that running sum gets updated every time we encounter a new amount.

= Code.UpdatePageRunningSum(Fields.amount)

And in the PageFooterSection, you do the same. This way, the running sum will be calculated based on the amounts processed on each page.

= Code.UpdatePageRunningSum(Fields.amount)

And with these changes, your report should now show the running sum correctly in both the page header and footer sections.

the custom code might need the green light to run, so double-check your report viewer settings to make sure it's allowed to do its thing.

Answer 2

It seems that you need custom code (Custom User Aggregate Function). The link to the forum answer below contains also example report. I copy part of the code here:

[AggregateFunction(Description = "Special sum aggregate. Output: (value1, value2, ...)", Name = "PageHeaderSumFromPrevPage")]
    class PageHeaderSumFromPrevPage : IAggregateFunction
    {
        [ThreadStatic]
        static decimal result;

        [ThreadStatic]
        static string currentGroupByValue;

        [ThreadStatic]
        static int currentPage = -1;

        [ThreadStatic]
        static List<object[]> currentPageValues;


        public void Accumulate(object[] values)
        {
            var page = (int)values[2];
            Debug.WriteLine($"NEW PAGE {page} CURRENT PAGE {currentPage}");
            if (page != currentPage)
            {
                currentPage = page;

                for (int index = 0; index < currentPageValues.Count; index++)
                {
                    this.AccumulateCore(currentPageValues[index]);
                }
                currentPageValues.Clear();
            }

            currentPageValues.Add(values);
        }

        void AccumulateCore(object[] values)
        {
            // The aggregate function expects one parameter
            object value = values[0];

            // null values are not aggregated
            if (null == value)
            {
                return;
            }


            var groupByValue = (string)values[1];

            if (groupByValue != currentGroupByValue)
            {
                //Debug.WriteLine($"RESETTING GROUP {currentGroupByValue} TO {groupByValue}");
                currentGroupByValue = groupByValue;
                result = 0M;
            }

            //Debug.WriteLine($"ACCUMULATING {(decimal)value} INTO {result}");

            result += (decimal)value;
        } 

Reference: https://www.telerik.com/forums/how-calculate-a-page-running-value-in-telerik-reporting https://docs.telerik.com/reporting/knowledge-base/summary-per-page-when-group-is-on-multiple-pages