The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Course Of The Month
5 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
SSRS SQL PIVOT Multiple Rows Required for Result
Posted on 2014-12-20
This query gets the result, below
I need help with how to modify the query to also be able to show quantities below each revenue line in my report. For example:
Doing that within a pivot is throwing me off. How to do?
SELECT *
FROM
(
SELECT
SalesLineItems.[Item Class Code],
SalesLineItems.[Item Number]
,SalesLineItems.[Customer Name]
,(SELECT CASE ISNULL ([Tax Exempt 1 from Customer Master], '')
WHEN ''
THEN NULL
ELSE CAST ([Tax Exempt 1 from Customer Master] AS DECIMAL (10,2))
END) as [Dispenser Cost]
,SalesLineItems.[GL Posting Date]
,SalesLineItems.[SOP Number]
,CASE WHEN LEFT (SalesLineItems.[SOP Number],2)='CR' THEN sum(SalesLineItems.[Extended Price]*-1) ELSE sum(SalesLineItems.[Extended Price])END as [Extended Price]
,datename(month,SalesLineItems.[GL Posting Date]) as FMonth
,datename(year,SalesLineItems.[GL Posting Date]) as FYear
,CASE WHEN LEFT (SalesLineItems.[SOP Number],2)='CR' THEN sum(SalesLineItems.[QTY]*-1) ELSE sum(SalesLineItems.[QTY])END as [QTY]
FROM
SalesLineItems
WHERE
SalesLineItems.[SOP Type] IN ('Invoice','Return')
AND SalesLineItems.[Void Status] <> 'Voided'
AND SalesLineItems.[Document Status] = N'Posted'
AND SalesLineItems.[Extended Price] != 0
AND SalesLineItems.[Item Class Code] IN (@ItemClassID)
AND datename(year,SalesLineItems.[GL Posting Date]) = @FYear
GROUP BY
SalesLineItems.[Item Class Code]
,SalesLineItems.[Item Number]
,SalesLineItems.[Customer Name]
,SalesLineItems.[Tax Exempt 1 from Customer Master]
,SalesLineItems.[GL Posting Date]
,SalesLineItems.[SOP Number]
) as DerivedTable
PIVOT (sum([Extended Price]) for FMonth in ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]))
AS NEWMONTH
I need help with how to modify the query to also be able to show quantities below each revenue line in my report. For example:
Doing that within a pivot is throwing me off. How to do?
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
3 Comments
Either that, or don't do it as a PIVOT, and do it as a Matrix instead - no need for a pivot at all in that case.
Active 7 days ago
I was unable to get the query to parse correctly for the Union All because of the derived tables. So, I chucked the idea of using that query (which was from another report I created for the same customer).
I used the core query (sans derived tables and pivot) and added the QTY field. Then, in SRS, I created a new tablix and added column groups as adjacent for each month, filtered by month, and sorted by Item Number. I added QTY as another row within the row group.
Results are as desired.
I rated the solution as "C" because the solution offered was "do it as a Matrix instead" without any further explanation. It's a good thing I know my way around SRS. Expert-to-"expert" (but I'm improving with every snafu!) that advice was sufficient to warrant full points and a sincere "Thank you!" for the clear-eyed suggestion.
Happy holidays!
I used the core query (sans derived tables and pivot) and added the QTY field. Then, in SRS, I created a new tablix and added column groups as adjacent for each month, filtered by month, and sorted by Item Number. I added QTY as another row within the row group.
Results are as desired.
I rated the solution as "C" because the solution offered was "do it as a Matrix instead" without any further explanation. It's a good thing I know my way around SRS. Expert-to-"expert" (but I'm improving with every snafu!) that advice was sufficient to warrant full points and a sincere "Thank you!" for the clear-eyed suggestion.
Happy holidays!
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| Convert results from the query output to insert into sql table | 3 | 29 | |
| CASE Statement using different fields | 8 | 32 | |
| rolling count by date, hour query | 7 | 31 | |
| SQLCMD question to have fields created as fixed length | 2 | 36 |
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses
Course of the Month5 days, 5 hours left to enroll
738 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.