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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics