December 2, 2011 at 2:10 pm
I have ssrs reprots and I want to display all month in Rows column
I am passsing Two parameter
@year =2011
@month =6.
And Reports display Only uptp 4month.
But i want to display all 12 months.
This Is my Reports look Like
Year Jan Feb Mar April
2011 4 5 4 5
But I want
Year Jan Feb Mar April May June July August Sept October Nov Dec
2011 4 5 4 5 0 0 0 0 0 0 0 0
How can I display All Months.
Thanks
Bhavesh
December 2, 2011 at 2:55 pm
you have to join a Calendar table of some sort in order to generate your missing values.
Search here on SSC for Calendar Table or Tally Calendar.
If you can show us the query that you are using to get your data, we could offer some better suggestions, but i'd jsut be guessing at this point as to what your data looks like.
Lowell
December 3, 2011 at 4:00 pm
The following article will help you do exactly what you need to do...
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 5, 2011 at 1:46 am
U vil try to pivot concept & Unpivot concept..
December 5, 2011 at 1:46 am
U vil try to pivot concept & Unpivot concept..
December 5, 2011 at 4:53 am
satheyaraaj.tamilmani (12/5/2011)
U vil try to pivot concept & Unpivot concept..
Uh huh... you might want to explain how that's going to work if you have data missing from an entire time period. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
December 5, 2011 at 5:04 am
Jeff Moden (12/5/2011)
satheyaraaj.tamilmani (12/5/2011)
U vil try to pivot concept & Unpivot concept..Uh huh... you might want to explain how that's going to work if you have data missing from an entire time period. 😉
Sure (and without an outer join too!) =>
This report ALWAYS runs on a 12 full months timespan. Hence the weird coding. :hehe:
SELECT
S.NoArticle
, S.Description
, S.ParametreReportProduit
, SUM(CASE WHEN dtYM.ixPivot = 01 THEN S.Quantity
ELSE 0
END) AS Qty_Month01
, SUM(CASE WHEN dtYM.ixPivot = 02 THEN S.Quantity
ELSE 0
END) AS Qty_Month02
, SUM(CASE WHEN dtYM.ixPivot = 03 THEN S.Quantity
ELSE 0
END) AS Qty_Month03
, SUM(CASE WHEN dtYM.ixPivot = 04 THEN S.Quantity
ELSE 0
END) AS Qty_Month04
, SUM(CASE WHEN dtYM.ixPivot = 05 THEN S.Quantity
ELSE 0
END) AS Qty_Month05
, SUM(CASE WHEN dtYM.ixPivot = 06 THEN S.Quantity
ELSE 0
END) AS Qty_Month06
, SUM(CASE WHEN dtYM.ixPivot = 07 THEN S.Quantity
ELSE 0
END) AS Qty_Month07
, SUM(CASE WHEN dtYM.ixPivot = 08 THEN S.Quantity
ELSE 0
END) AS Qty_Month08
, SUM(CASE WHEN dtYM.ixPivot = 09 THEN S.Quantity
ELSE 0
END) AS Qty_Month09
, SUM(CASE WHEN dtYM.ixPivot = 10 THEN S.Quantity
ELSE 0
END) AS Qty_Month10
, SUM(CASE WHEN dtYM.ixPivot = 11 THEN S.Quantity
ELSE 0
END) AS Qty_Month11
, SUM(CASE WHEN dtYM.ixPivot = 12 THEN S.Quantity
ELSE 0
END) AS Qty_Month12
, SUM(S.Quantity) AS Qty_Total
, S.[Unit Cost]
, dbo.FnNavMoneyRounding(SUM(S.Quantity * S.[Unit Cost])) AS Total_Cost
, SUM(S.Quantity) / 12 AS Qty_AVG
, S.DaysLeadTime
INTO
#pivot
FROM
#Sales S
INNER JOIN (
SELECT
Y
, M
, ROW_NUMBER() OVER ( ORDER BY Y, M ) AS ixPivot
FROM
(
SELECT DISTINCT
Y
, M
FROM
dbo.Calendar C
WHERE
C.dt BETWEEN @BeginDate AND @EndDate
) dtYearMonths
) dtYM
ON S.Annee = dtYM.Y
AND S.Mois = dtYM.M
GROUP BY
S.NoArticle
, S.Description
, S.ParametreReportProduit
, S.[Unit Cost]
, S.DaysLeadTime
December 5, 2011 at 5:11 am
U can do it by Matric Report. and Convert Month Numeric value to month Name for showing column Header as Jan,feb, march
December 5, 2011 at 5:13 am
Bipinkumar Yadav (12/5/2011)
U can do it by Matric Report. and Convert Month Numeric value to month Name for showing column Header as Jan,feb, march
It will still fail if there's absolutely no data for 1 of the months.
December 5, 2011 at 5:22 am
Ninja's_RGR'us (12/5/2011)
Jeff Moden (12/5/2011)
satheyaraaj.tamilmani (12/5/2011)
U vil try to pivot concept & Unpivot concept..Uh huh... you might want to explain how that's going to work if you have data missing from an entire time period. 😉
Sure (and without an outer join too!) =>
This report ALWAYS runs on a 12 full months timespan. Hence the weird coding. :hehe:
You missed the point I was trying to maketo Satheyaraaj, Remi... 😉 Satheyaraaj dropped a "bomb" and walked away without much understanding offered. No link, no nothing. It was almost as effective as someone asking how to do a running total and someone saying only "Use T-SQL".
Haven't tried it but nice job on the code you posted.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 5, 2011 at 5:28 am
Jeff Moden (12/5/2011)
Ninja's_RGR'us (12/5/2011)
Jeff Moden (12/5/2011)
satheyaraaj.tamilmani (12/5/2011)
U vil try to pivot concept & Unpivot concept..Uh huh... you might want to explain how that's going to work if you have data missing from an entire time period. 😉
Sure (and without an outer join too!) =>
This report ALWAYS runs on a 12 full months timespan. Hence the weird coding. :hehe:
You missed the point I was trying to maketo Satheyaraaj, Remi... 😉 Satheyaraaj dropped a "bomb" and walked away without much understanding offered. No link, no nothing. It was almost as effective as someone asking how to do a running total and someone saying only "Use T-SQL".
Haven't tried it but nice job on the code you posted.
That's why I posted another bomb. 😀
My code works fine but only if the timespan is invariant. I used to do this with left join but I had some fun on that one :-P.
If you want to use a matrix to pivot the data you have to use a temp table to save the results, there insert 1 row per missing timespan.
(Insert where not exists). That's my more "standard way" to do this. You could also ignore the not exists part and just add 1 row per period regardless of wether or not it's needed. It might save enough iops to be a good idea...
December 6, 2011 at 8:15 am
Thanks For all Reply.
Bhavesh
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply