Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
running SQL 2012 SSIS packages in recent versions? 3 103
SSRS Dynamic Date Parameter 25 459
SSRS Report 2 45
SSRS - Date Report Options 2 28
Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

839 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