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

Comparing data between 2 datasets into a table - SQL reporting services Expand / Collapse
Author
Message
Posted Monday, February 20, 2012 9:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 21, 2012 4:23 AM
Points: 1, Visits: 1
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
Post #1254816
Posted Monday, April 16, 2012 6:37 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 5:47 AM
Points: 476, Visits: 879
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.
Post #1284620
Posted Wednesday, June 20, 2012 5:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, November 3, 2012 12:12 AM
Points: 1, Visits: 12
p
Post #1318586
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse