Combine two datasets in SSRS report?

Question

Combine two datasets in SSRS report?

TAMMYAUDETTE

Ten Centuries

Points: 1259
More actions
April 14, 2010 at 1:39 pm

#386706

I reallly feel like this is something that is simple, but I'm completely missing the logic.
I have a report I'm building, a report of SKU numbers with sold and returned qtys by date. The info comes from 2 views, so I have two datasets. They look like this:
SOLD:
ENTRY_DT
STK_NBR
STK_DESC
QTY_SOLD
RETURNS:
ENTRY_DT
STK_NBR
STK_DESC
QTY_RET
I want the user to be able to run a report with start date and end date, and get the the total sold and returned, for each item. So in this case the ENTRY_DT and the STK_NBR have to match, and then sum the QTY fields of each.
Maybe I've been at this too long, I just can't get it.
Tammy

Viewing 5 posts - 1 through 5 (of 5 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

TAMMYAUDETTE (4/14/2010)
I reallly feel like this is something that is simple, but I'm completely missing the logic.
I have a report I'm building, a report of SKU numbers with sold and returned qtys by date. The info comes from 2 views, so I have two datasets. They look like this:
SOLD:
ENTRY_DT
STK_NBR
STK_DESC
QTY_SOLD
RETURNS:
ENTRY_DT
STK_NBR
STK_DESC
QTY_RET
I want the user to be able to run a report with start date and end date, and get the the total sold and returned, for each item. So in this case the ENTRY_DT and the STK_NBR have to match, and then sum the QTY fields of each.
Maybe I've been at this too long, I just can't get it.
Tammy

You might want to look into Join Fundamentals in BOL.

Basically,you would join the two tables together.

If the RETURNS will always have an entry for every day that there is one in SOLD, then the below query is fine. If you can have entries in SOLD without entries in RETURNS, change the "JOIN" to "LEFT JOIN".

SELECT SOLD.Entry_Dt,

SOLD.STK_NBR,

SOLD.QTY_SOLD,

[RETURNS].QTY_RTN

FROM SOLD

JOIN [RETURNS]

ON SOLD.Entry_Dt = [RETURNS].Entry_Dt

AND SOLD.STK_NBR = [RETURNS].STK_NBR

WHERE SOLD.Entry_Dt >= @StartDt

AND SOLD.Entry_Dt < @EndDt

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TAMMYAUDETTE Ten Centuries Points: 1259 More actions · Answer 2

Thanks for the quick reply!

Makes total sense, appreciate the link too.

Tammy

WayneS SSC Guru Points: 95461 More actions · Answer 3

Tammy,

Glad to be able to help you out. We know you're an "accidental DBA", and most people here will try to help you out.

One reason some people won't help out is if you don't try to help us help you... by that I mean posting table definitions/sample data in a format where they can just cut-and-paste to start helping you out. Please see the first link in my signature for how to do this. In addition, please enclude what you expect to see for results based on the sample data provided.

Back to your issue...

If you can have data in the SOLD table without data in the RETURNS table for that date/stk #, then you need to change the join to a left join (already mentioned). If you can ALSO have data in the RETURNS table without data in the SOLD table for that date/stk #, then come back and ask... it gets a bit more complicated.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TAMMYAUDETTE Ten Centuries Points: 1259 More actions · Answer 4

Thanks for the tips! You're right, I can see why that would make it easier to assist me. I'll most definitely keep that link and info handy!

Thanks Wayne 🙂

Tammy