Two Datasets

Question

In my SSRS report, I have two different datasets.  Both queries are pulling from the same databases. However two datasets are connected with different tables.

Dataset #1:  I built a matrix report to show

Location:  Total Cost

Dataset #2:  I built a matrix report to show

Location:  Total Cost

I would like to sum up the total cost in both dataset # 1 and dataset # 2.  How can I do that?

Thanks,

Josephine

---

JT

Answer 1

Hi,

Go to this link

http://stackoverflow.com/questions/1624546/two-datasets-sql-reporting-services-2008

---

Hope this will help you !!!
Sanjeewan

Answer 2

Where I can put this expression?  Should I add a new matrix table?Please advise. Thanks!

=IIf(Fields!Line.Value="B1", Sum(Fields!Result.Value, "DataSet2"), IIF(Fields!LineName.Value = "B3", sum(Fields!Result.Value, "DataSet2"),""))

---

JT

Answer 3

Hi Josephine - I assume you are wanting to display the total from both datasets in a textbox on your report, correct? If so, you just need to put the expression in the textbox where you would normally specify a dataset column name or some other value.

Note that I don't think you need the complexity of the "IIF" function, it could just look like this instead:

=Sum(Fields!Cost.Value, "DataSet1") + Sum(Fields!Cost.Value, "DataSet2")

I hope this helps - Thanks, Dean

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 4

Thanks, Dean

I have additional question:

In my data result in Dataset 1:

Location   Dollars

A              $100

B              $200

C              $300

In my data result in Dataset 2:

Location   Dollars

A              $350

B              $210

C              $350

I want to sum up the location A in both dataset 1 and dataset 2.  For example: Location A = $450, Location B =$410, and Location C = $ 650.

Thanks,

Josephine

---

JT

Answer 5

You can use a lookup function in your expression to look up in other data set. For details on lookup please refer to below link

http://msdn.microsoft.com/en-us/library/ee210531.aspx

Answer 6

Hi Josephine, I understand now. Since both data sets come from the same server/database, I'd solve this in the SQL queries rather than in report expressions.

If you have a reasonably small, fixed list of Locations, (ie A,B,C only) you could use the IIF/SUM expression technique for each Location. Ie, for Location A:

=Sum(IIF(Location="A",Fields!Cost.Value,0), "DataSet1") + Sum(IIF(Location="A",Fields!Cost.Value,0), "DataSet2")

But I don't consider this a very practical or maintainable solution.

As Sorna mentions, RS does support lookup functions, but I rarely use them because, compared to the power of SQL Server JOIN semantics, they are very simple and limited in what they can do. Also, if your data set is large and you have a lot of complex expressions like this, you're asking RS to do a lot of processing once it receives the data set, and this really affects the perf of your report. It can and will handle millions of rows of data (ultimately limited by memory), but SQL Server is much more specialized and efficient at doing that kind of processing. My reports commonly use one dataset for complex aggregations, and another for detailed data, its just how I go about these types of problems.

I hope this helps, thanks, Dean

This posting is provided "AS IS" with no warranties, and confers no rights.