December 23, 2014 at 9:41 am
Hey all,
I've been trying to figure out how to setup a report such that the column names are the months of the year, and the row to display results based on a query.
Currently i setup an SSRS report with 2 sub reports. It's taking the results from 1 table called Paydetails and taking the date from an orderheader table. I'm grabbing the SUM value for a unique ID for each month, and the total(I figured out how to get the Total with the Report Builder by using the Matrix Wizard).
We have 4 unique IDs for payouts, and 2 for discounts; Let's just call them A,B,C,D for the payouts, E & F for the discounts.
In the main report containing the subreports, i have 1 dataset and the script is :
SELECT
p.pyt_itemcode,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 01 THEN pyd_amount ELSE 0 END) AS Jan,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 02 THEN pyd_amount ELSE 0 END) AS Feb,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 03 THEN pyd_amount ELSE 0 END) AS Mar,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 04 THEN pyd_amount ELSE 0 END) AS Apr,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 05 THEN pyd_amount ELSE 0 END) AS May,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 06 THEN pyd_amount ELSE 0 END) AS Jun,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 07 THEN pyd_amount ELSE 0 END) AS Jul,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 08 THEN pyd_amount ELSE 0 END) AS Aug,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 09 THEN pyd_amount ELSE 0 END) AS Sep,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 10 THEN pyd_amount ELSE 0 END) AS Oct,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 11 THEN pyd_amount ELSE 0 END) AS Nov,
SUM(CASE WHEN MONTH(o.ord_completiondate) = 12 THEN pyd_amount ELSE 0 END) AS Dec
from paydetail p
join orderheader o on p.mov_number=o.mov_number
where p.pyt_itemcode in ('A','B','C','D')
AND o.ord_completiondate >=@StartDate
AND o.ord_completiondate <= @EndDate
GROUP BY P.PYT_ITEMCODE
The second subreport is the same except that it look for the itemcodes E & F.
The last subreport calculates the total count for E & F. I used the same script but just added SELECT COUNT(p.pyt_itemcode) and i'm getting the total for the entire year in 1 column, and then the monthly amounts. I want to get the monthly total count of E & F and the average cost per month.
I'd like to have 1 row show "Total Count" and a second for "$/Itemcode Average"...I think that the ROLLUP function or PVIOT, if someone could give me an example using my script that would be awesome...
December 29, 2014 at 12:10 pm
Without any actual data to play with, I can't test this, but you should end up with something like this:
WITH MONTHS AS (
SELECT 1 AS MN, 'Jan' AS MTH_NAME UNION ALL
SELECT 2, 'Feb' UNION ALL
SELECT 3, 'Mar' UNION ALL
SELECT 4, 'Apr' UNION ALL
SELECT 5, 'May' UNION ALL
SELECT 6, 'Jun' UNION ALL
SELECT 7, 'Jul' UNION ALL
SELECT 8, 'Aug' UNION ALL
SELECT 9, 'Sep' UNION ALL
SELECT 10, 'Oct' UNION ALL
SELECT 11, 'Nov' UNION ALL
SELECT 12, 'Dec'
),
RAW_DATA AS (
SELECT p.pyt_itemcode, pyd_amount, M.MTH_NAME
FROM paydetail AS p
JOIN orderheader AS o
ON p.mov_number = o.mov_number
JOIN MONTHS AS M
ON MONTH(o.ord_completiondate) = M.MN
WHERE p.pyt_itemcode in ('A','B','C','D', 'E', 'F')
AND o.ord_completiondate BETWEEN @StartDate AND @EndDate
)
SELECT R.pyt_itemcode, [Jan], [Feb], [Mar], [Apr], [May], [Jun], [Jul], [Aug], [Sep], [Oct], [Nov], [Dec],
([Jan] + [Feb] + [Mar] + [Apr] + [May] + [Jun] + [Jul] + [Aug] + [Sep] + [Oct] + [Nov] + [Dec]) AS YEAR_TOTAL,
([Jan] + [Feb] + [Mar] + [Apr] + [May] + [Jun] + [Jul] + [Aug] + [Sep] + [Oct] + [Nov] + [Dec]) / 12 AS AVG_MONTH
FROM RAW_DATA AS R
PIVOT (SUM(R.pyd_amount) FOR R.MTH_NAME IN
([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]) AS P
You'll need to be sure your time period, from @StartDate to @EndDate doesn't go beyond a given 1 year time frame, as otherwise, you run the risk of getting potentially undesirable results from having, say, January of 2013 added into January of 2014 and displayed as [Jan]. I'm not sure why you are using sub-reports, as I'm not sure you need them. If you need to aggregate A thru D separately from E and F, I can modify the above query to provide such grouping. Let me know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 29, 2014 at 12:21 pm
I'm using sub-reports because the report in question is getting the summary for 6 different item codes which are all PK from the same table; 4 of which are payables ( Money we send out) and the 2 others are discounts (Money we make). Additionally, the report needs the total count and average for the discount item codes for each month as well.
The end result would have a 8 rows, not including the Totals for the the payables and discount, so 10 rows total split in 3 sections; Payables sum by month for each payable itemcode & Total sum ( 4 payable itemcodes), total discount sum by month & total ( 2 discount item codes), total count for discount itemcodes and the average price for each month. sum
They also want to add a line graph to compare years, but i'll tackle that when i get the main part done.
December 29, 2014 at 1:14 pm
I'm still pretty sure you don't need a sub-report at all. You can derive the averages and any column totals if needed, within SSRS. Did you run the query I supplied? If so, did you get the results you sought?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 30, 2014 at 11:44 am
I got the following error message when i tried using your script:
Msg 325, Level 15, State 1, Line 31
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.
Our database compatibility is set to SQL SERVER 2000(80) and i don't want to make any changes on prod obviously :S
Thanks for your help though! I'll dig around and see what other convention our database might let us use 😛
December 30, 2014 at 10:02 pm
Unfortunately, I didn't know about the compatibility level because you didn't mention it, and you posted the question in the SQL 2008 forum, so I naturally assumed I could develop a query using SQL 2008 capabilities. With the database in SQL2000 compatibility mode, you can't use PIVOT, as it's not valid until SQL 2005, and the compatibility level determines what query elements you can use. You might as well go back to the case statement query you had before, or alternatively, you might still be able to get it all in one query, by adapting the CASE statement to a query that includes all the pay codes.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 31, 2014 at 7:48 am
Thank you again for your input, very much appreciated 🙂
Sorry for not providing more complete description, i'm still pretty much brand new to SQL Server and learning as much as i can as i go along. Your example introduced me to CTE which helped me better understand Temp tables; I'll try playing around with it.
Happy new year!!
December 31, 2014 at 8:22 am
I'm not sure that CTE's are available in SQL 2000 either. I can't remember. You can check Books Online, or maybe just Google it. In any case, let me know how this turns out.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
January 7, 2015 at 11:36 pm
sgmunson (12/31/2014)
I'm not sure that CTE's are available in SQL 2000 either. I can't remember. You can check Books Online, or maybe just Google it. In any case, let me know how this turns out.
CTEs were introduced in SQL 2005.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply