February 16, 2012 at 11:32 pm
i have a query which is giving me dynamic data using pivot
In that am using total as both columns and rows like this
name ..type...PART... .hOR..... total
xyz ......a........RT.........4..........4
pqr.......b.........RT........6..........6
TOTAL........................10........10
NOW I WANT add one more column based on this footer total
my formula is 10(which is total in footer)/4
how can i get access to footer total
am using 2 pivots using union all ..if i use the sum it sayz divide by zero !
;with cte as
(
select EM.Employee_Name as Name ,
BN.Block_Name as Block_Name,
.........
)
SELECT Name ,BlockType, Block_Name ,[1],[2],
cast((([1]+[2])*(16))/(8) as decimal(4,0)) As Proportion,,
cast((((([1]+[2])*(16))/(8))*100)/(sum( (([1]+[2])*(16))/(8))) as float) as cs1- change here i want
from CTE PIVOT (MAX(HOURS) FOR Date_Issued IN ( [1],[2] ) ) AS pvt
GROUP BY BlockType, Block_Name, Name,[1],[2]
UNION ALL SELECT Name , BlockType, Block_Name,[1],[2],
cast((([1]+[2])*(16))/(8) as decimal(4,0)) As Proportion,,
cast(((([1]+[2])*(16))/(8))/( sum( (([1]+[2])*(16))/(8))) as float) as cs1 -- its wrking only in footer
FROM (select Name , 'Total' as BlockType, space(1) as Block_Name,
Hours, Date_Issued FROM CTE) P PIVOT (SUM(hours) FOR Date_Issued IN ( [1],[2] ) ) as pvt
GROUP BY BlockType, Block_Name, Name,[1],[2]
February 20, 2012 at 8:57 am
T-SQL is not a reporting tool, yet you are trying to use it like one. What you are trying to do would be relatively easy in a reporting tool, such as SSRS. Consider re-writing this report in SSRS.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply