1

I have the following expression that works beautifully to add two values, regular hours and overtime hours.

Sum(IIF(Fields!BillStatus.Value = "F", (CDec(Fields!RegHrs.Value) + CDec(Fields!OvtHrs.Value)),CDec(0)),"ReportDataset")

What I need to do is add a third value to that, but this third value will come from a different dataset, and I cannot seem to find the proper syntax for that.

Somehow, immediately after the OvtHrs.Value, I need to say something to the effect of...

+ (Fields!HoursWorked.Value, "RBaseJobBaseline")

How do I inject that value as part of the "true" clause of the IIF, since it needs to come from a different dataset.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
DJGray
  • 504
  • 8
  • 26
  • SSRS will not allow you to directly combine values in two different datasets on a row-by-row basis - see the answer to this question for further details and alternative solutions: http://stackoverflow.com/questions/10342896/using-fields-in-multiple-datasets-in-ssrs –  Feb 11 '13 at 18:21
  • Yikes! That's a pretty serious limitation. I'll have to find a different workaround. Thanks for the feedback and the link to the other question, Mark. – DJGray Feb 11 '13 at 18:58

2 Answers2

2

The only way to combine sets in SSRS it's

  1. Lookup [msdn][1]
  2. LookupSet msdn
  3. MultiLookup msdn

But these functions comes to SSRS from MS SQL 2008R2 version, unfortunately in SSRS 2005 you can't do this and you should combine datasets only in data base level.

[1]: http://technet.microsoft.com/en-us/library/ee210531.aspx ????

Community
  • 1
  • 1
Roman Badiornyi
  • 1,509
  • 14
  • 28
0

Combine your datasets (or SQL queries) into one query.

Use the DATASETS option in SSRS2005 (via Properties of your control) to capture the "root" or main dataset by using the report aggregate functions such as FIRST(), SUM(), etc.

To report the detail of your single dataset, use the FIELDS option.

SSRS2005 can only work on ONE dataset at a time, unless you use multiple tables (or lookup parameters, etc) with different datasets, but they are still totally independant of each other.

Fandango68
  • 4,461
  • 4
  • 39
  • 74