Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
jackn 40579
jackn 40579
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
davoscollective
davoscollective
SSChasing Mays
SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)SSChasing Mays (601 reputation)

Group: General Forum Members
Points: 601 Visits: 1000
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.
mdazizulhoque.bd
mdazizulhoque.bd
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 12
p
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search