Solved

SSRS:  Copying a "Total" Field from a Subreport

Posted on 2013-12-20
4
3,632 Views
Last Modified: 2013-12-23
Hello:

Although my first attachment is called "POOR_RCG_Subreport.rdl", it is actually my main report.  The other attachment is a subreport of this main report, in fact.

I'm trying to pull the "Total" field from the subreport onto the main report, so that I can have a Grand Total on my main report.

I have tried everything, but I can't get this to work.  I even took a copy of the Data Set from the subreport and made it one of the Data Sets on the main report.

How can I get this accomplished?

Thanks!

Much appreciated!

TBSupport
POOR-RCG-Subreport.rdl
POOR-RCG-Detail-Subreport2.rdl
0
Comment
Question by:TBSupport
  • 3
4 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 39733054
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 39733066
Also I do alittle research and they said that

Pass value from sub-report to main report is not supported by the current version of Reporting Services.

Please add additional dataset in the main report. How you aggregate in the sub-report, then how you aggregate in the main report.

Hopefully this helps.
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 39733068
If On the Main report You create another dataset equal than the dataset that contains the subreport you will be able to accomplish more or less will be like this

Create two datasets - one for the main report one that is the same used in the sub report.

Add your sub report but grab the total from the second dataset on the main report.

I'm not actually taking the info from the sub report. I'm only using the sub report to display the information correctly. I'm using the second dataset just to total what's in the sub report.

=Sum(Fields!Total.Value, "DataSource1") + Sum(Fields!ThinkTotal.Value, "DataSource2")
0
 
LVL 8

Accepted Solution

by:
vr6r earned 500 total points
ID: 39733122
It will depend greatly on the state of your datasets.  If your subreport data is static, then k-designers above response may work very well for you.

However, if your subreport is dynamic and is generated using paramaters from your main report's dataset, things get much more difficult because in that case you wouldn't be able to add those sub-report datasets beforehand.

The best approach in this circumstance would be to re-examine your dataset queries, and the design of your main report and subreport.  For example you could potentially include the Grand Total in your main report dataset by joining and totalling the data on sql, then you wouldn't have to change your subreport at all.

Or you could redesign your subreport and instead make it a detail section of your main report, in which case you could use the normal sum functionality in SSRS without jumping through a bunch of extra hoops.

In most of my reports like this I have used a combination of the two examples I just gave.  Sub-reports can be helpful in cases where you'll need to display the same data in multiple different reports, but for most of my purposes I found that my data was so dynamic that it was easier to just combine all the data into a single report.  I also often calculate the grand totals in my sql queries because I'm already joining to get all the detail anyway, and it shifts that processing load to sql server instead of the reporting server which I have often found to be much faster in large reports with complex data.

Hope that helps!
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question