CODEWITHSUNDEEP
reporting-serviceschartsdatasetssrs-2008-r2
wodz
wodz

Reputation: 67

SSRS 2008, Two Datasets, One Chart

I have two datasets. One dataset1 has the fields MonthNumber, MonthData. The other dataset2 has the fields MonthNumber, MonthBase. I want to use both the MonthData field and MonthBase fields in one dataset to create a chart. Please help me if anyone has an idea how I can combine both datasets to one or use one field of one dataset into another?

Upvotes: 1

Views: 1747

Answers (1)

Jonnus
Jonnus

Reputation: 3038

To combine the fields from two datasets without combining the datasets you need to use the LOOKUP function.

To replicate this I created the datasets...

DataSet1
--------
MonthNumber  MonthData
------------ ----------
1            12
2            23
3            11
4            8

DataSet 2
---------
MonthNumber  MonthData
------------ ----------
1            10
2            15
3            20
4            25

  1. Create a chart as you would normally for DataSet1.

  2. Set the values to MonthData, and the Category Groups to MonthNumber Then add a new Series in the Values area, and set the expression to 

     =Lookup(Fields!MonthNumber.Value, Fields!MonthNumber.Value, 
     Fields!MonthBase.Value, "DataSet2")

This effectively states join The ID fields DataSet1.MonthNumber, to DataSet2.MonthNumber, returning me DataSet2.MonthBase

The design will look like this

enter image description here

And the output like this

enter image description here

See also the reference here from which I derived this answer

Upvotes: 2

Related Questions