Solved

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

Posted on 2014-02-21
4
2,350 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

760 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

22 Experts available now in Live!

Get 1:1 Help Now