June 18, 2012 at 2:25 am
Hi Forumer's
I have a report in SSRS with from and to date parameter. the records pullout based on the Date. So, i have where Condition
that determine the date range. i have also a column header in SSRS that contains the Month.
I would like to hide the month based on the where date condition.
EX. Where between Trandate From "2012/04/01" and "2012/06/18"--meaning i will pullout records based on this date range.
Based on the date only Apr up to May that have a records and the rest are zero records and these should be hide.
Can you please help me on how to do this. Thanks..
Jan--Feb--March--Apr--May up to Dec
------------------------------------
0 -- 0 --98 --55 --60---------0
0 -- 0 --10 --0 --75---------0
I place this in column visibility but im not successful. it's not working.
=IIF(Sum(Fields!Jan.Value) > 0,"True","False")
Thank you in advance..
JOV
June 18, 2012 at 6:38 pm
Kaniguan_SQL (6/18/2012)
Hi Forumer'sI have a report in SSRS with from and to date parameter. the records pullout based on the Date. So, i have where Condition
that determine the date range. i have also a column header in SSRS that contains the Month.
I would like to hide the month based on the where date condition.
EX. Where between Trandate From "2012/04/01" and "2012/06/18"--meaning i will pullout records based on this date range.
Based on the date only Apr up to May that have a records and the rest are zero records and these should be hide.
Can you please help me on how to do this. Thanks..
Jan--Feb--March--Apr--May up to Dec
------------------------------------
0 -- 0 --98 --55 --60---------0
0 -- 0 --10 --0 --75---------0
I place this in column visibility but im not successful. it's not working.
=IIF(Sum(Fields!Jan.Value) > 0,"True","False")
Thank you in advance..
JOV
If you are doing the sum in the query and passing the 0 value to the report, you might use the Tablix filter rather than the column visibilty property to control the display. If you are not actually passing a 0 value for the date, try changing the column visiblity expression to:
=iif(isNothing(Sum(Fields!Jan.Value))=True, True, False)
or without the sum
=iif(isNothing(Fields!Jan.Value)=True, True, False)
June 20, 2012 at 12:31 am
THanks for the reply.
You mean like this..
Tablix properties ----Visibility-----then Show or Hide based on expression then i place this
scripts inside. and i try also the other one but still the Jan column did not hide even there is no trnasaction of mostly the column has zero values.
=iif(isNothing(Sum(Fields!Jan.Value))=True, True, False)
My Scripts
I have one scripts that pull out records and insert to temp table #data1
and also this is the last part of my CTE scripts and i insert the derived result to temp table #Data2, So, i have 2 temp table and then i combined these two.
Select
Partno
,SUM(Case When TransMOnth='January'Then Usage else 0 End) As Jan
,SUM(Case When TransMOnth='February'Then Usage else 0 End) As Feb
,SUM(Case When TransMOnth='March'Then Usage else 0 End) As Mar
,SUM(Case When TransMOnth='April'Then Usage else 0 End) As Apr
,SUM(Case When TransMOnth='May'Then Usage else 0 End) As May
,SUM(Case When TransMOnth='June'Then Usage else 0 End) As Jun
,SUM(Case When TransMOnth='July'Then Usage else 0 End) As Jul
,SUM(Case When TransMOnth='August'Then Usage else 0 End) As Aug
,SUM(Case When TransMOnth='September' Then Usage else 0 End) As Sep
,SUM(Case When TransMOnth='October' Then Usage else 0 End) As Oct
,SUM(Case When TransMOnth='November' Then Usage else 0 End) As Nov
,SUM(Case When TransMOnth='December' Then Usage else 0 End) As Dec
,avg(usage) as overall_avg
Into #Data2
From CTE2
Group by Partno
Order by Partno
Combine:
The result of these combined data i place it into SSRS report template.
Select
d1.Itemid
,d1.OEM_OnHand
,d1.S_OnHand
,d1.IR_OnHand
,d1.R_OnHand
,d1.RR_OnHand
,d1.RO_OnHand
,d1.TotalOnHand
,Round(Jan,0) as Jan
,Round(Feb,0) as Feb
,Round(Mar,0) as Mar
,Round(Apr,0) as Apr
,Round(May,0) as May
,Round(Jun,0) as Jun
,Round(Jul,0) as Jul
,Round(Aug,0) as Aug
,Round(Sep,0) as Sep
,Round(Oct,0) as Oct
,Round(Nov,0) as Nov
,Round(Dec,0) as Dec
,Round(overall_avg,0) as Overall_Avg
From #Data d1
Inner Join #Data2 d2
On d2.partno = d1.itemid
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply