Home > Home, MSDN, SSRS > SSRS : Hide Data Point on SSRS Chart

SSRS : Hide Data Point on SSRS Chart

This Question is originally posted on MSDN, link click here.

In Brief:-

I am using SSRS 2008 R2. I have a chart where the values are gallons of fuel, category groups are Months, and Series Groups are Years. I want to hide the current month of the current year only when the chart is generated. So when I generate the chart in May 2016, I want the data point hidden, not the category group since there will be data points for May 2013, 2014, and 2015. Then when June 2016 roles around, same thing, etc. Below is a screen shot of my chart. If someone needs to see the SQL Query, I can provide that as well.

Lets create sample report using sample data , Sample data query as given below:-

 

select ‘Jan’ Monthsname, 1 MonthNumber, 2014 year, 2000 gallons
union
select ‘Feb’ Monthsname, 2 MonthNumber, 2014 year, 3000 gallons
union
select ‘Mar’ Monthsname, 3 MonthNumber, 2014 year, 5000 gallons
union
select ‘Apr’ Monthsname, 4 MonthNumber, 2014 year, 7000 gallons
union
select ‘May’ Monthsname, 5 MonthNumber, 2014 year, 8000 gallons
union
select ‘Jun’ Monthsname, 6 MonthNumber, 2014 year, 9000 gallons
union
select ‘July’ Monthsname, 7 MonthNumber, 2014 year, 10000 gallons
union
select ‘Jan’ Monthsname, 1 MonthNumber, 2015 year, 3000 gallons
union
select ‘Feb’ Monthsname, 2 MonthNumber, 2015 year, 5000 gallons
union
select ‘Mar’ Monthsname, 3 MonthNumber, 2015 year, 8000 gallons
union
select ‘Apr’ Monthsname, 4 MonthNumber, 2015 year, 2000 gallons
union
select ‘May’ Monthsname, 5 MonthNumber, 2015 year, 6000 gallons
union
select ‘Jun’ Monthsname, 6 MonthNumber, 2015 year, 1000 gallons
union
select ‘July’ Monthsname, 7 MonthNumber, 2015 year, 7000 gallons
UNION
select ‘Jan’ Monthsname, 1 MonthNumber, 2016 year, 9000 gallons
union
select ‘Feb’ Monthsname, 2 MonthNumber, 2016 year, 8000 gallons
union
select ‘Mar’ Monthsname, 3 MonthNumber, 2016 year, 7000 gallons
union
select ‘Apr’ Monthsname, 4 MonthNumber, 2016 year, 3000 gallons
union
select ‘May’ Monthsname, 5 MonthNumber, 2016 year, 2000 gallons
union
select ‘Jun’ Monthsname, 6 MonthNumber, 2016 year, 10000 gallons
union
select ‘July’ Monthsname, 7 MonthNumber, 2016 year, 3000 gallons

order by year,MonthNumber

Design the report dataset with above query and drag the  line char control on to the report body and set the Values, Category Groups and series groups as shown below:-

1

Run the report to check what it is showing then we modify what needed .

1

As already shown above that , what we needed we need to hide the current month current year data point.

First we show data points with circle as shown below:-

1

Lets Filter the data from dataset or chart as per your need. Better we filter at chart level , I mean filter current Month and current year data.

Go to Chart Properties , Filters Pane and set the expression as shown below:-

1

Expression :-

=cstr(Fields!year.Value) + cstr(Fields!MonthNumber.Value)

Value:-

=CSTR(year(now()))+cstr(month(now()))

Run the report, report will not have the data point but line was breaked as shown below:-

1

Now We need to set the color of the current year series, Why we need to set the color of the series will show in later stage of development.

1

=iif(Fields!year.Value=year(now()),”RED”,”Automatic”)

We will create a line for empty data values so we set color as RED as we set the same series with RED color in above screen

Follow above click on line and go to the properties and then go to EmptyPoint and set Color RED.

1

 

Now run the report

1

let say i didn’t set the series color and for empty rows  I set color green then how it look like:-

1

So better set the color for filtered series and same color used for empty data line

 

Thanks

Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : Know BI Tools

Advertisements
Categories: Home, MSDN, 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: