Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get difference of rows from tables referencing to different databasets Expand / Collapse
Author
Message
Posted Thursday, May 23, 2013 9:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:30 AM
Points: 8, Visits: 161
I am trying to achieve something different, and hope if someone can help me with this. In my report, I have 2 datasets that has MDX queries that references to 2 different cubes. I created 2 tables to display the result of 2 datasets. Well my actual requirement is 3rd table, that will display the difference between values in 2 tables cell by cell. In my report, I don't even need to display 1 and 2 table, its just to used to get the records for 3rd table. Check the file attached, where I have my two tables. The 3rd table should have difference of cells values for every fiscal week.

I tried creating 3rd table without any dataset assigned to it, but it didn't work. I tried to use expression in text field to get the difference between to cells, but it works only for the first record.

I am really stuck at this point, please help!!






  Post Attachments 
SSRS_example.jpg (6 views, 57.61 KB)
Post #1456067
Posted Thursday, May 23, 2013 10:26 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 25, 2014 4:20 PM
Points: 152, Visits: 448
I would base the tablix on the BIPM dataset and then use an expression similar to this one for the value of each detail textbox in the tablix:

= Fields!ItemSales.Value - Lookup(Fields!FiscalWeekOfYear.Value, Fields!FiscalWeekOfYear.Value, Fields!ItemSales.Value, "BIPM Audit")

This expression returns the difference between the ItemSales value in the BIPM dataset and the ItemSales value in the BIPM Audit dataset for corresponding fiscal weeks.
Post #1456084
Posted Thursday, May 23, 2013 11:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 8:30 AM
Points: 8, Visits: 161
Worked like charm!!! Thanks a lot, I really appreciate that.
Post #1456122
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse