Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSRS 2008 Combine Two Data Sources in One Report

Posted on 2010-08-26
3
Medium Priority
?
952 Views
Last Modified: 2012-06-27
Experts, I am trying to combine two data sets in one report that share a common ID called "Order". I have both data sets setup and the tablix in the report. One data source hold Date info about Projects and the other holds cost. The cost dataset is returning empty when I run it. Here is the code I'm using for it. I dont think I'm using the WHERE IN clause correctly. I'm not an SQL programmer so please dumb it down for me
SELECT     [Project ID], [Project Description], [Planning Order], [Order], [Shared/Specifc], AuthAmt, [Actuals to Date], FAC, [Rmng Fcst], [EOY Budget/Plan], 
                      [2010 YTD Actuals], [EOY Fcst]
FROM         EPMSAPExcel
WHERE     ([Order] IN ('" + Join(Parameters!OrderNumbers.Value, "', '")+"'))

Open in new window

0
Comment
Question by:hunew
3 Comments
 
LVL 16

Accepted Solution

by:
vdr1620 earned 2000 total points
ID: 33538621
I created a sample code for you below..Also, Suggest use to check the below links on SSRS - SQL Query with parameters and Joins in SQL ..which will help you re write your query correctly
 
http://www.sql-server-performance.com/articles/reporting/report_parameters_p2.aspx

http://www.codeproject.com/KB/database/SqlServerJoins.aspx
SELECT     [Project ID], [Project Description], [Planning Order], [Order], [Shared/Specifc], AuthAmt, [Actuals to Date], FAC, [Rmng Fcst], [EOY Budget/Plan], 
                      [2010 YTD Actuals], [EOY Fcst]
FROM         EPMSAPExcel E 
INNER JOIN TAble B        -----( Replace it with your tableName) 
ON E.order = B.Order      -- (Joining on matching columns)
WHERE     (E.[Order] IN (@Parameter)

Open in new window

0
 

Author Comment

by:hunew
ID: 33546646
Thanks but the Table B you mention above is a different dataset in my case. how I can refer to it?
0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 33546886
I am afraid you cannot join the data from two separate datasets inside of RS, you will have to do that at the database level via your original query or stored procedure.  You can nest objects in RS or create subreports where the inner object uses a value from the outer object as a parameter to gather data to display, but it is nested something like this not Joined.

Customer 1    Name     Address
Some other customer 1 summary data
       Customer 1 Order Data
       Date        Quantity           Price
       1/1/10     100               $500.00
       2/2/10     300             $1250.00
Customer 2    Name     Address
Some other customer 2 summary data
       Customer 2 Order Data
       Date        Quantity           Price
       2/1/10     50                $500.00
       2/2/10     100             $1250.00
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

916 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