Thursday, September 15, 2011

Running totals per page in SSRS to display cumulative totals.

In Crystal Reports, you can easily make use of running totals on every page of your report. SQL Server Reporting Service does not have a standard functionality for this. Especially for companies who want to migrate from CRW to SSRS, running totals can be important to use. This blogpost will explain how you can implement running totals in your report.

  1. Add a tablix to your report and add a dataset to it. In my example I have 3 columns:
    Debtor code, Account Name and Outstanding amount.
  2. Add a 4th column to the tablix. Set the visibility of this column to Hide.
  3. Select in the 4th column the detail row. Select Text Box Properties and change the name of the textbox to 'RunningTotal'.
  4. Select Value and press the expression button. In this expression we will define the running total of the column. In my example, I have a columns Outstanding from the dataset 'CreditLimitDetails'

    =RunningValue(Fields!Outstanding.Value,SUM,"CreditLimitDetails")
    Now we have a running value of the outstanding amount in the 4th column.
  5. To display the running value on every page, we will use the page footer.
  6. Add a page footer to your report. In BIDS you can select, Report, Add Page footer.
  7. In this example I will add a page total and a running total to the report.
  8. Insert a 2 text boxes to the page footer.
  9. Open the expression definition of the first text box. In this text box we will define the totaal outstanding amount of the current page.
    ="Page Total: " + cstr(Sum(ReportItems!Outstanding.Value))
  10. Open the expression definition of the second text box. In this text box we will define the Running total of the outstanding column of the current page and all previous pages.
    ="Running Total: " +cstr(Last(ReportItems!RunningTotal.Value))
Now your report will print running totals at the bottom of every page.

25 comments:

Dany... said...
This comment has been removed by the author.
Dany... said...

Excellent information.
I have a doubt.
I could show the total of the previous page in the current pagnia? ...

I would appreciate your response...
thanks

Nima said...

Hi
that was greate and i used it in my reports (remote report).
but unfortunately it is not working for localReport.summaries dose not working when columns is hidden.
is there any work around?

Nima said...

Hi
that was greate and i used it in my reports (remote report).
but unfortunately it is not working for localReport.summaries dose not working when columns is hidden.
is there any work around?

Nima said...

Hi
that was greate and i used it in my reports (remote report).
but unfortunately it is not working for localReport.summaries dose not working when columns is hidden.
is there any work around?

Unknown said...

running total would work when set column width size to 0

Andriuch said...

I want to show this parcial total in a footer of tablix, is possible?

Unknown said...
This comment has been removed by the author.
Unknown said...

It works fine in preview and after exporting it to PDF, but in word it does not show page totals on every page instead it shows the complete totals of the records.

Unknown said...

Awesome works sweet as a nut

Unknown said...

Im trying to run my report by using the statement what u said but iam getting an error like "The value for the text box 'Running total' has a scope parameter this is not for an aggregate function.The scope parameter must be set to a string constant that is equal to either the name of containing group,the name of containing data region,or the name of the dataset" . Can u pls tell me how to resolve this.

Digital Weekday said...

https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//

Digital Weekday said...

https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//
https://globexdocuments.co//

Python said...

Thanks for Sharing.
Data Science Online Training
Python Online Training
Salesforce Online Training

ytriphd said...

Woderful content. I am regularly follow this blog. Thank you for updating such a good content. Please follow my Travel blog for more information about Eco Park in Kolkata.

My other posts are:
Victoria Memorial in Kolkata.
Scuba Diving in Goa.
Top visiting places in GOA.
Calangute Beach in Goa.
Candolim Beach in Goa.
Baga Beach in Goa.

Arefa Akter said...

Whatsapp Number Call us Now! 01537587949
please visit us: Graphic Design Training
sex video: Dropped phone repair Erie
pone video usa: mobile phone repair in West Des Moines
pone video usa: Social Bookmarking Sites List 2021

Unknown said...

I just got to this amazing site not long ago. I was actually captured with the piece of resources you have got here. Big thumbs up for making such wonderful blog page!
Pmp training in hyderabad

Service on Street said...

"After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article".
Best Refrigerator Repair Service in Hyderabad

Salman Nur said...

Thank you very much for publishing this of article, I like your article very much. Your post is very informative and helpful.dehumidifier supplier in Bangladesh

Salman Nur said...

Wow very nice blog site .Really it is a one of the best blog site I am very impressed to your post. Thank you so much for sharing this post.Best Software Company in Bangladesh

Salman Nur said...

Thank you very much for publishing this of article, I like your article very much. Your post is very informative and helpful. diploma in engineering

Salman Nur said...

Thank you very much for publishing this article, I like your article very much. Your post is very informative and helpful. web development company in Bangladesh

Salman Nur said...

Your post was really pretty. I like it a lot. Your blog site is very informative and helpful. The term Internet Service Provider (ISP) refers to a company that provides Internet access to both personal and business customers. ISPs make it possible for their clients to surf the web, shop online runs a business, and connect with family and friends - in exchange for everything. ISPs can also provide other services including email services, domain registration, web hosting, and browser packages. Our blog site is related to Internet Service Provider (ISP). I believe that my blog site is more helpful than all other blog sites. We provide the best service to everyone. best isp in Dhaka

shailendra said...

Best University for Legal Studies in Uttarakhand
This is good
Best University for Pharmacy in Uttarakhand

ALEX said...

this is one of the great best smtp services
smtp relay service provider