How to Calculate WTD , MTD and YTD using SSRS Reporting

Question

0.00/5 (No votes)

See more:

SSRS-2008

I have the below SQL Query and I want to Calculate the WTD, MTD and YTD sales figure using this and only group by the store codes -

SQL

declare @DateFrom datetime , @dateTo datetime

set @datefrom = getdate()-6
set @dateto= getdate() 

select
st.store_code as Store,
p.product_code as Product,
PN.short_name as [Product Description],
c.description as Waist,
s.description as [Size(Length)],
rt.TRANSDATE as [Transaction Date],
rt.TRANSNUM as [Transaction number],
rt.operator_id as [Sales Associate ID#],
sta.name as [Sales Associate name],
dtp.QTY,
((dtp.qty*dtp.Price_Sold)+isnull(dtp.Tax_Amount1,0)+ISNULL(dtp.Tax_Amount2,0)) as [Price_Sold],
service.description as [Service Applied],
dts.PRICE_NET as [Service Amount],
@Datefrom as From_Date,
@DateTo as to_Date,

"YTD" = ?,
"MTD"=?,
"WTD"=? 

from RETAIL_TRANSACTION (nolock) rt  
inner join dt_product (nolock) dtp      on rt.store_code_id = dtp.store_code_id
                                           and rt.transtype = dtp.transtype
                                           and rt.transnum = dtp.transnum
                                           and rt.cais = dtp.cais




inner join retail_line (nolock) rl      on rl.store_code_id=dtp.store_code_id
                                        and rl.transnum=dtp.transnum
                                        and rl.transtype=dtp.transtype
                                        and rl.cais=dtp.cais
                                        and rl.line_id=dtp.line_id



Left outer join dt_service (nolock) dts      on dts.store_code_id=rl.store_code_id
                                           and dts.transtype=rl.transtype
                                           and dts.transnum=rl.transnum
                                           and dts.cais=rl.cais  


 inner join service (nolock)            on service.service_id=dts.service_id                           
 inner join store (nolock) st              on rt.store_code_id = st.store_code_id




 inner join staff (nolock) sta            on rt.operator_ID = sta.staff_id

inner join sku_conversion (nolock) sc    on  dtp.product_id = sc.product_id
                                           and dtp.size_id = sc.size_id
                                           and dtp.color_id = sc.color_id



inner join Product(nolock) P            on P.product_id=dtp.product_id
inner join Product_name(nolock) PN      on PN.product_id=P.product_id
inner join color(nolock) C              on C.color_id=dtp.color_id
inner join size(nolock) s               on s.size_id=dtp.size_id 

where dts.service_id in ('1000045','1000044')

and rt.TS_ID between @Datefrom and @Dateto

I don't want to Group by all the fields in the select statement , only group by Transdate and store.

How to achieve this using SSRS Reporting . Please give me some idea..

Posted 19-Apr-15 9:23am

DEbopm

Updated 19-Apr-15 10:47am

Maciej Los

v2

Add a Solution

Comments

Homero Rivera 19-Apr-15 21:02pm

What do you mean by "How to achieve this using SSRS Reporting . Please give me some idea." that's too broad. Do you know Visual Studio? Do you have a SSRS project there already? I can't answer that question but I can sure answer the YTD specification, please read answer.

Homero Rivera 19-Apr-15 21:34pm

These SSRS tutorial videos are great, btw http://pcteach.me/Series/sql-reporting-services-basics/

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Homero Rivera · Answer 1 · 2015-04-19T15:14:00

Since YTD is the longest span, you must use YTD span in your WHERE clause.

So your variables at top should be:

SQL

declare @DateFrom datetime , @dateTo datetime
 
set @datefrom = dateadd(mm, -month({fn curdate()})+1, {fn curdate()}) /*down to January*/
set @datefrom = dateadd(dd, -Day(@datefrom ) + 1,  @datefrom) /*down to Jan 1st*/

set @dateto= {fn curdate()}

So using this in your WHERE clause brings YTD Data. Now you need to make a SUM with a CASE statement for YTD, MTD and WTD.
I don't know very well your field names, but if you follow this model you'll be fine:

SQL

SUM(YourTotalSales) AS YTD,

SUM(CASE WHEN SaleDate BETWEEN DateAdd(dd, -Day(@dateTo)+1, @dateTo) AND @dateTo 
THEN YourTotalSales ELSE 0 END) AS MTD,

SUM(CASE WHEN SaleDate BETWEEN DateAdd(dd, -DATEPART(dw, @dateTo)+1, @dateTo) AND @dateTo THEN YourTotalSales ELSE 0 END) AS WTD

For the GROUP BY: by rule, if there is a field in the SELECT clause that is not part of an aggregate function, then it must go into the GROUP BY.
Just don't select the fields you don't want to group by and omit them from the GROUP BY clause and you'll do fine.