Home > SSRS > SSRS 2008 – hide chart lines based on parameter selected

SSRS 2008 – hide chart lines based on parameter selected

From MSDN I got this question SSRS 2008 – hide chart lines based on parameter selected.

Based on the parameter selection we will hide the series lines.

Just to hide series line we just need to filter the data.

lets in our example : we have Series as year, Months we are showing in X-axis and Data in Y-axis.

Parameters

TwoYrs? T/F

ThreeYrs? T/F

Both are False then we will show current Year Data, If Two Years True and Three  Years False then only two years data.

Create two Parameters in SSRS report.

Source Data we will modify based on the parameters as shown in below query:-

select * from
(
select 1 monthnumber , ‘Jan’ MonthName, 2013 year,100 amount
union
select 1 monthnumber , ‘Jan’ MonthName, 2014 year ,200 amount
union

select 1 monthnumber , ‘Jan’ MonthName, 2015 year , 300 amount
union

select 2 monthnumber , ‘Feb’ MonthName, 2013 year, 300 amount
union

select 2 monthnumber , ‘Feb’ MonthName, 2014 year, 350 amount
union

select 2 monthnumber , ‘Feb’ MonthName, 2015 year,200 amount
union
select 3 monthnumber , ‘Mar’ MonthName, 2013 year, 380 amount
union
select 3 monthnumber , ‘Mar’ MonthName, 2014 year, 100 amount
union
select 3 monthnumber , ‘Mar’ MonthName, 2015 year, 500 amount

)t

where year in (
select distinct FilterYear from
(
select case when @TwoYrs=1  then  year(getdate())-1 else year(getdate()) end  FilterYear
union
select case when @TwoYrs=1 and @ThreeYrs =1 then year(getdate())-2 else  year(getdate()) end FilterYear
union
select year(getdate()) FilterYear
) t
)

then Above Dataset query is core logic to hide and show the series in chart.

Lets run the report

Case 1: @Twoyrs= True and @ThreeYrs=True

AllTrue

Case 2: @Twoyrs= True and @ThreeYrs=False

OneTrueOneFalse

Case 2: @Twoyrs= False and @ThreeYrs=False

AllFalse

Thanks for visiting my blog

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: SSRS Tags:
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: