Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pass Parameter for two different Datasets Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 10:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 229, Visits: 693
I have two seperate datasets :
Dataset1 and Dataset 2 and want to display results from both of them for the same values.
Here is what I have done :

Created Dataset3 and written a query where I pass the parameters to get two combo boxes on the report to fetch values.
On dataset 1 , I added Filter with the expression :
=First(Fields!ProdNbr.Value, "Dataset3")

But it gives me an error : Aggregate functions cannot be used in dataset filters .
Any ideas???
Post #1495183
Posted Tuesday, September 17, 2013 3:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 25, 2014 10:53 AM
Points: 3,151, Visits: 1,371
If you want to produce a join like structure between the two datasets then either:

- create a join within a single dataset if the sources are compatible (i.e. both from same SQL server etc)
- use one of the SSRS lookup expressions to access the columns returned from dataset2 for the equivalent ProdNbr is dataset1.

Example:

DataSet1 : ProductNbr, Name, Color, ListPrice
DataSet2 : ProductNbr, QuantitySold, SalesAmount

1. Create a table and add the columns from DataSet1
2. Add a additional column to the table and add the expression as shown below. The function is used as :

= LOOKUP( FieldInCurrentDataSet, FieldInTargetDataSet, FieldToReturnFromTargetDataSet, "NameOfTargetDataSet")

= LOOKUP( Fields!ProdNbr.Value, Fields!ProdNbr.Value, Fields!QuantitySold.Value, "DataSet2")

3. If you want to return multiple values evaluate the use of LOOKUPSET and MULTILOOKUP as well.

If this is not what you were looking for the please reply with more details and I will try to help.

Fitz

Post #1495415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse