Post reply

Comparing data between 2 datasets into a table - SQL reporting services

  • February 20, 2012 at 9:51 am

    #391600

    Hi there,

    This is my first post so I'm not sure if I'm putting it in the right place. I'm working with a reporting server that I only have read access to, so I cannot create tables or procedures etc. What I'm trying to do is compare values returned from 2 datasets and then display it appropriately in a table. The 1st dataset grabs a list of dates that match if certain criteria are matched, and the second one does the same but with different criteria. I then want to be able to display a list of these dates in a table, excluding any that match between the 2 datasets. Can anyone advise me please?

    Regards,

    Jack

  • April 16, 2012 at 6:37 pm

    #1474482

    This is a bit old but no-one has replied so I'll let you know my various ways of doing this.

    Sounds like you could use EXCEPT in this case.

    Instead of having two datasets, you can combine them into one.

    e.g.

    Select Date1 from Table1

    EXCEPT

    Select Date2 from Table2

    If there are other fields you need in the table you can use the EXCEPT as a subquery e.g.

    Select Date1, field2, field3 from Table1 where Date1 in

    (

    Select Date1 from Table1

    EXCEPT

    Select Date2 from Table2)

    There are some other tricks to working with multiple datasets in SSRS. First you can reference cells directly. It helps to name the cells so they aren't just called e.g. Textbox37.If you named it SaleDate for example:

    You can use ReportItems!SaleDate.Value in expressions. This option is hidden unfortunately, you just need to know about it apparently. It's probably not ideal to use references to named cells, but if you change the name of the cell the error message is pretty explicit.

    You can also reference other Datasets in expressions but it seems to be fairly limited to overall aggregates. In the expression editor you can choose "Datasets" under "Category" and then pick an item and aggregate value e.g. sum / first / average etc

    I was actually looking to see if there was a way to compare same groups between datasets. e.g. dataset2 Region Sales as a percentage of dataset1 Region Sales where they are both the same region. I'm trying to help a power user who has visually created this report with 3 MDX datasets in it so was trying to avoid the stored proc option so that this user can still see the queries and avoid my future involvement.

  • June 20, 2012 at 5:24 am

    #1503330

    p

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

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