September 16, 2013 at 10:29 am
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???
September 17, 2013 at 3:29 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply