0

I have the following situation (in SQL Server 2008 R2):

enter image description here

. Two datasets (Dataset 1 / Dataset 2) 
. Rows (A - H ) <br>
. Columns (2011 - 2012 - 2013 - P12 M)

I recieve the first three columns from dataset 1, the last column (Past 12 Months) with an lookup on 

Column A=Lookup(Fields!A.Value, Fields!A.Value, Fields!Total.Value, "Dataset 2")

So far so good..

The challenging part: 

Row B/A = ROW B divided by ROW A

I use this statement: 

=SUM(IIF( Fields!A.Value = "B", Fields!Total.Value, 0)) / SUM(IIF( Fields!A.Value = "A", Fields!Total.Value, 0))

But how to get there with an lookup? How to get to the first question mark (Answer: 2,23)?

I tried to combine the statement with an lookup, so far no result.
Searched the internet and found/tried some URL:
SSRS nested iif expression in lookup

Community
  • 1
  • 1
Mizzle
  • 13
  • 1
  • 4

1 Answers1

0

Method 1:

Handling it the way the report is designed currently.

You would need to explicitly give the value in source expression of Lookup.

=Lookup("A", Fields!A.Value, Fields!Total.Value, "Dataset 2")
 /Lookup("B", Fields!A.Value, Fields!Total.Value, "Dataset 2")

Method 2:

I would probably redesign the datasets and combine the dataset 1 and dataset 2. It will help remove doing funky logic in the report. Use new dataset to populate the matrix.

New Dataset:

SELECT CAST(Year as varchar(10)), A, Total
FROM DataSet1_Table
UNION ALL 
SELECT 'L12M', A, Total
FROM DataSet2_Table
Anup Agrawal
  • 6,551
  • 1
  • 26
  • 32