CODEWITHSUNDEEP
sql-serverreporting-services
Matt P
Matt P

Reputation: 147

Showing Full Calendar Year SSRS

I have the requirement of showing actual data versus a trend in SSRS. I have it working for the current running fiscal year (July to now), but the end-user wants to show the full fiscal year (until next July). Is there a way to insert dummy variables to show the rest of the year without data?

The trend line is a variable that is $30k for each given month.

Upvotes: 2

Views: 811

Answers (1)

Nathan Griffiths
Nathan Griffiths

Reputation: 12756

I find the best way of doing this is to artificially generate the future months in the dataset query(obviously with actuals = 0). Exactly how you do this depends on what your data and existing query looks like, but here is an example that will generate a temp table of dates for the current FY - you could then join this to your original data to get results for all dates in the year:

DECLARE 
      @StartDate datetime = '2012-07-01'
    , @EndDate datetime = '2013-06-30';

WITH DateRange(Date) AS
 (
     SELECT
         @StartDate Date
     UNION ALL
     SELECT
         DATEADD(day, 1, Date) Date
     FROM
         DateRange
     WHERE
         Date < @EndDate
 )

SELECT 
    [Date]
INTO #CurrentFYDates    
FROM 
    DateRange
OPTION (MaxRecursion 10000);

(from: http://www.ridgway.co.za/archive/2007/11/23/using-a-common-table-expression-cte-to-generate-a-date.aspx)

Upvotes: 1

Related Questions