CODEWITHSUNDEEP
sql-server-2008reporting-servicesssrs-2008-r2linegraph
sql2015
sql2015

Reputation: 611

SSRS - Hide Results for future months

I'm producing a line graph within SSRS using SQL Queries. I have a line graph with 4 datasets (3 using lookup function).

My queries calculate values for each month of current year. As we are only in June, values of months from July to December are 0. I don't want that they were displayed on graph and on axis.

Can anyone point me in the right direction? I've tried to add a filter to the category group for the axis but this hasn't worked

Expression: Month_field
Operator: <
Value: =Month(Today())

my sql query is a build up of months for this year so I can get a result for all months ( I was having Issues returning 0 as a value for a month) eg..

(SELECT * FROM (VALUES(DATENAME(month,'2015-01-01'),1)
                      ,(DATENAME(month,'2015-02-01'),2)
                      ,(DATENAME(month,'2015-03-01'),3)
                      ,(DATENAME(month,'2015-04-01'),4)
                      ,(DATENAME(month,'2015-05-01'),5)
                      ,(DATENAME(month,'2015-06-01'),6)
                      ,(DATENAME(month,'2015-07-01'),7)
                      ,(DATENAME(month,'2015-08-01'),8)
                      ,(DATENAME(month,'2015-09-01'),9)
                      ,(DATENAME(month,'2015-10-01'),10)
                      ,(DATENAME(month,'2015-11-01'),11)
                      ,(DATENAME(month,'2015-12-01'),12)) AS Mnth("  Month  ",MnthSort)) AS M

followed by the below to returnvalues greater than 1st fay of current year and less than 1st day of current month

 WHERE     Received1Date >=dateadd(mm,datediff(mm,0,getdate())-12,0)AND Received1Date < dateadd(mm,datediff(mm,0,getdate()),0)

My issue is when using my query in SSRS on a line graph all months display on Axis and all future results are 0 - I know this is expected because of my query but just wondering if there is something I can do to the graph within SSRS to exclude all future months?

Upvotes: 0

Views: 440

Answers (1)

NewGuy
NewGuy

Reputation: 1030

Why don't you filter the data in your query?

   select values 
   from table 
   where transaction_date >= Getdate()

this way those future months are not options to be displayed.

Upvotes: 1

Related Questions