Division by data from second dataset
Hi everybody,
I am currently developing reports with Rebort Builder 2.0 and got stuck with the following:
The report (based on dataset 1) should look like
Year
Month Pieces
YTD Average per working day
2010
1
50
50 ABC
2
80
130 BCD
… …
… …
Total 1500
1500 XYZ
The working days are included in another dataset (dataset 2), which looks like
Year
Month Days
2010
1
20
2
20
…
Total 240
So far, my calculations are:
XYZ: Sum(Fields!Amount_local.Value)/Sum(Fields!Amount_local.Value, "DataSet2")
How do I calculate ABC, BCD etc? When I choose values from dataset 2, I only get the possibilities to work with the “Sum”
and “First” functions.
Any ideas are much appreciated. Thanks!
Andreas
October 14th, 2010 4:22pm
Hi Andreas,
Are you using SQL Server Reporting Services(SSRS) 2008 R2?
If so, we can use the Lookup function to get the working day from the second dataset, and the calculation should be:
=Sum(Fields!Amount_local.Value)/Lookup(Fields!Year.Value & Fields!Month.Value, Fields!Year.Value & Fields!Month.Value , Fields!Days.Value, "DataSet2")
If you are using other versions of SSRS, please merge the two datasets into one using T-SQL. In this case, we can use JOIN to merge the two datasets. Then in the calculation, we can use the following expression:
=Sum(Fields!Amount_local.Value)/First(Fields!Days.Value, "GroupMonth")
For more information about Lookup function, please see:
http://msdn.microsoft.com/en-us/library/ee210531.aspx
If you have any more questions, please feel free to ask.
Thanks,
Jin Chen
Jin Chen - MSFT
Free Windows Admin Tool Kit Click here and download it now
October 18th, 2010 12:39pm
Hi Jin Chen,
thanks for your feedback.
Proposal one does not work, as I am working with SSRS 2008 and not SSRS 2008 R2. Software says, "Lookup" function is not declared.
Regarding proposal two, I am a beginner with the software and never did any T-SQL before. Could you give me some more information, how to do merge the datasets properly?
Furthermore, the datasets above were presented simplified. In reality, dataset 1 is bigger than presented, as I am filtering for various parameters. Example: I would like to show the activities of sales people. Therefore, I use filters
for the activities "Visits", "Catalogs distributed" and " Samples". For each parameter, the above mentioned columns occur (1. Pieces, 2. YTD, 3. Av. per working day). Does this somehow change the merging procedure?
Thanks and kind regards
Andreas
October 21st, 2010 2:02pm