SSRS SQL PIVOT Multiple Rows Required for Result

This query gets the result, below

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

Open in new window

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?

LVL 24

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented: 2014-12-22

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.

You need to do it as two separate pivots, and then UNION ALL between them.

CPA_MCSEAuthor Commented: 2014-12-24

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!

SSRS SQL PIVOT Multiple Rows Required for Result

Who is Participating?

Already a member? Login.