Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Flexible Fiscal Data Sorting

  • 2 August 2013
  • Author: okeofs
  • Number of views: 12081
  • 0 Comments

A few months ago, I faced the challenge of extracting data via SQL Queries (for our enterprise), whose fiscal year runs from July through June.

Further, the parameters fed through from SQL Server Reporting Services to our stored procedures, had to cater for multiple years, as the business folks would probably want to look back to prior years, for planning purposes. HOWEVER only one year at a time may be passed.

 

Reporting is ALWAYS viewed from July 1st  through June 30th  , either as year to date (for current year) OR in the case of past years, the whole year at one time. The important part is that when the monthly figures are shown, they must start with July and end with June.

In Short

Fiscal Year SSRS label

Parameter passed to SP

(or chosen fiscal year)

@YearIncoming

2012-2013

2013

2013-2014

2014

 

First off, I created a piece of code that resides at the top of each stored procedure (SP). This script creates the start and end dates for the chosen fiscal year. The code to achieve this may be seen below:

Note that piece of code is based upon the input year (in the parameter @YearIncoming) passed to the stored procedure.

 

set @beginfiscal= (select Min(datee) from [ANC-DW].dbo.Dim_Date

where convert(int,datepart(year,datee)) + 1 = @yearincoming)

set @beginfiscal =

(case

When @yearIncoming<> '2013'** then dateadd(month,6,@beginFiscal) else @beginfiscal

end)

 

** The first date in my reference table is 2012-07-01

 

 

set @endfiscal= (select Min(datee) from [ANC-DW].dbo.Dim_Date

where convert(int,datepart(year,datee)) = @yearincoming)

set @endfiscal =dateadd(month,6,@endFiscal) -- 6 months past the first of

--January

 

 

 

The layout of table dim_Date is a shown below and runs from 2012-07-01 through 2016-06-30

 

 

Table [ANC-DW].dbo.Dim_Date

 

Datee                 DateKey     WeekNumber  Month   Quarter

2012-07-01         20120701            1             1             1

2012-07-02         20120702            1             1             1

2012-07-03         20120703            1             1             1

2012-07-04         20120704            1             1             1

2012-07-05         20120705            1             1             1

………………

Passing through ‘2013’ to the script will yield:

Start date                                          End date

2012-07-01 00:00:00.000              2013-07-01 00:00:00.000 (further explanation below)

 

Now the plot thickens. We need some code to create month sort fields otherwise when sorted we will start with April and end with September.

Here is the code to achieve just this.

Note that for July through December that @beginFiscal is used to calculate the month. For January through June, I use the end date or @endFiscal.

 

set @month01  =

  convert(varchar(4),datepart(Year,@beginFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,@beginFiscal))) = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,@beginFiscal)))  else

   convert(varchar(2),datepart(Month,@beginFiscal))

  end

 

set @month02  =

  convert(varchar(4),datepart(Year,@beginFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,1,@beginFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,1,@beginFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,1,@beginFiscal)))

  end  

 

set @month03  =

  convert(varchar(4),datepart(Year,@beginFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,2,@beginFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,2,@beginFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,2,@beginFiscal)))

  end  

 

set @month04  =

  convert(varchar(4),datepart(Year,@beginFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,3,@beginFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,3,@beginFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,3,@beginFiscal)))

  end  

 

  set @month05  =

  convert(varchar(4),datepart(Year,@beginFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,4,@beginFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,4,@beginFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,4,@beginFiscal)))

  end  

  set @month06  =

  convert(varchar(4),datepart(Year,@beginFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,5,@beginFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,5,@beginFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,5,@beginFiscal)))

  end  

 

set @month07  =

  convert(varchar(4),datepart(Year,@endFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,-6,@endFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-6,@endFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,-6,@endFiscal)))

  end

 

set @month08  =

  convert(varchar(4),datepart(Year,@endFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,-5,@endFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-5,@endFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,-5,@endFiscal)))

  end

set @month09  =

  convert(varchar(4),datepart(Year,@endFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,-4,@endFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-4,@endFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,-4,@endFiscal)))

  end

set @month10  =

  convert(varchar(4),datepart(Year,@endFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,-3,@endFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-3,@endFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,-3,@endFiscal)))

  end

 

set @month11  =

  convert(varchar(4),datepart(Year,@endFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,-2,@endFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-2,@endFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,-2,@endFiscal)))

  end

set @month12  =

  convert(varchar(4),datepart(Year,@endFiscal)) +

  case

  when len(convert(varchar(2),datepart(Month,dateadd(mm,-1,@endFiscal))))  = 1 then

   convert(varchar(2),'0' + convert(varchar(2),datepart(Month,dateadd(mm,-1,@endFiscal))))  else

   convert(varchar(2),datepart(Month,dateadd(mm,-1,@endFiscal)))

  end

 

 

The end result may be seen in the table below:

 

yearmth

201207

201208

201209

201210

201211

201212

201301

201302

201303

201304

201305

201306

 

This year month combination is used as a ‘join key’ to join with records from the fact table. Each fact record has a date attached (as would any transaction).

 

The astute reader will note that the end date held in @endfiscal, at this point contains July 1 of the next fiscal year due to the calculations we did above. I now need to force that date to the previous day to ensure that the end date is June 30th 23hr 59 minutes and 59 seconds. This is achieved as follows:

 

--Take off 5 millisec to ensure that records with no times for July 1 are taken as a part of the year end

 set @endFiscal = DATEADD(ms,-5,@endFiscal)

 

Finally

 The trick is how to get the correct month names connected to each ‘year month’ combination.

This can be achieved using a simple case statement.

 

select yearmth,

Case

when substring(YearMth,5,6) = '01' then 'January'

when substring(YearMth,5,6) = '02' then 'February'

when substring(YearMth,5,6) = '03' then 'March'

when substring(YearMth,5,6) = '04' then 'April'

when substring(YearMth,5,6) = '05' then 'May'

when substring(YearMth,5,6) = '06' then 'June'

when substring(YearMth,5,6) = '07' then 'July'

when substring(YearMth,5,6) = '08' then 'August'

when substring(YearMth,5,6) = '09' then 'September'

when substring(YearMth,5,6) = '10' then 'October'

when substring(YearMth,5,6) = '11' then 'November'

when substring(YearMth,5,6) = '12' then 'December'

end as [Month] .. and fact data.

 

Thus when I create my report within SSRS I use the ‘year month’ as the sort field and the TRUE MONTH name as a secondary sort field. The ‘year month’ is hidden and I only show the true month.

 

As always should you have any questions or concerns,  please feel free to contact me at

Steve.simon@sqlpass.org

Print
Tags:
Rate this article:
No rating

okeofsokeofs

Other posts by okeofs

Please login or register to post comments.