Solved

SSRS Adding Two Fields from Two Separate Subreports onto the Main Report

Posted on 2014-02-21
4
2,373 Views
Last Modified: 2014-02-24
Hello:

Attached is my main report called "Contract Commitment Report".  Also attached are two of its subreports.

From the report containing "Past Due" in its name, the main report shows a total Past Due amount (column).  From the other attached subreport, the main report shows a "Total" amount (column) on the far right-hand side.

I need for the main report to add these two amounts together.  How do I take a value from one subreport and add it to the value of a second subreport?

Thanks!

TBSupport
Contract-Commitment-Report.rdl
POOR-RCG-PastDue-Detail-Subrepor.rdl
POOR-RCG-Detail-Subreport2.rdl
0
Comment
Question by:TBSupport
  • 2
4 Comments
 
LVL 27

Assisted Solution

by:planocz
planocz earned 250 total points
ID: 39879886
The sub-report parameters have to be in the main report parameters for them to be passed back to the main report. Then in the main report you just add the 2 parameters togather.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39879927
Thanks, planocz, for the quick response!

Do you have any general steps that I might follow, on how to add the two parameters together?

TBSupport
0
 
LVL 27

Expert Comment

by:planocz
ID: 39880042
Sample:
Main report has 2 place holders like Sub1 and Sub2 in the parameters.

Sub1 report has its parameters and also has a Sub1 parameter.

Sub2 report has its parameters and also has a Sub2 parameter.

When the parameters match (by name) in each report the reports know that parameter can be returned to the main report.

Now that the Sub1 and 2 have data in its parameters it can be seen by the main report.
In the main report just add the two Sub1 and 2 parameters.

Like Totals: (Sub1.value + Sub2.value)

Hope that helps you.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 250 total points
ID: 39881865
I always thought that passing a value from a subreport to the main report was not possible with SSRS. So I would very much like to get Planocz's technique to work. But I'm afraid I'm missing something. I've added a parameter to the main and sub report (in my test I only use 1 subreport) with the same name: "Sub1". When I run the main report it asks for the value of the parameter "Sub1". When I supply a value for this parameter the report runs but instead of the subreport I get an error message "One or more parameters were not specified for the subreport, ....". So I set a default value for the Sub1 parameter on the subreport. Now the subreport runs, but the main and sub report have a different value for the Sub1 parameter. I tried tried to link the parameters in the subreport and tried different combination of default values, but I'm only able to pass a value from the main to the sub report and not the other way around.

The only solution to TBSupport's problem I know is just adding two extra datasets to the main report that calculate the total. You can use the same query as on the subreport and let the report sum the values or you can change the query a bit so it only returns the total.
You can add a text field to the main report and refer to a value in a specific dataset like this:
=sum(Fields!PastDue.Value,"RCGPastDue")

Open in new window

For this to work the Dataset RCGPastDue must be added to the main report first.

I've attached a small sample where I do something similar with one subreport and the max function instead of the sum. But the technique is exactly the same. Enter 1, 2 or 3 as the value for the parameter on the main report.
Main.rdlMainSub.rdl
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now