Get difference of rows from tables referencing to different databasets

  • 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!!

  • 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.

  • Worked like charm!!! Thanks a lot, I really appreciate that.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply