Filter report based on Quarter or in my Case Fiscal Quarter

Question

Filter report based on Quarter or in my Case Fiscal Quarter

mightycaptain

SSC Veteran

Points: 224
More actions
April 12, 2011 at 1:57 pm

#238365

I've used these forums on many occasions to solve the problems that have vexed me and decided to post a solution I came up with today that I was having trouble finding and had to invent.
I have a report with several parameters. Fiscal Year, Department,Fiscal quarter, just to name a couple, several are Multi-Select. I like to set all my reports with default parameters, and with dates, I love for them to be dynamic, for instances so I don't have to republish reports every year, when I supply default parameters for the Fiscal Year I use this expression =iif(month(now)>=7,year(now)+1,Year(Now)). My company's Fiscal Year Starts in July.
Now for Quarter, it was a little more interesting. It is also a Multi-Select, but I wanted to return the current Fiscal Quarter by Default instead of all. Also in the Stored Procedure, @fquarter is varchar, so the data type for the parameter is String. My Fiscal Year starts in July, so conversion was a little easier since the Fiscal Quarters lined up with the Calendar Quarters. So Instead of selecting all quarters, this returns the Current Fiscal Quarter as a default value.
=iif(Cstr(Datepart("q",Today())>=3),CStr(Datepart("q",Today())-2),Cstr(Datepart("q",Today())+2))
If you wanted to follow the Calendar Year, you could drop the iif and use the first part of the expression. =Cstr(Datepart("q",Today())
I hope this saves someone some head banging. If anyone has alternate ways of handling this I'd love to know, doesn't seem to be a lot out there I can find.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 1

A common technique in the data warehousing world is to create a dates or calendar table that list all the possible permutations of a date. ie.:

date

calendar year

fiscal year

calendar quarter

fiscal quarter

calendar month

fiscal month

day of calendar month

day of fiscal month

day of calendar year

day of fiscal year

etc. Then query the table with a where = Getdate() and it returns all permutations of a date. This approach also makes date range queries easy, instead of WHERE DATE BETWEEN 1/1/11 AND 3/31/11

you could use WHERE FiscalQuarter = 201101.

mhoward_00 57858 Valued Member Points: 69 More actions · Answer 2

Daniel,

I found your post about dealing with a calendar table and I have done that exact thing and I have run into a lil issue and was wondering if you had a solution to it. Below is my query for my calendar table...

CREATE TABLE dbo.State_Calendar

(

dt SMALLDATETIME NOT NULL

PRIMARY KEY CLUSTERED,

isWeekday BIT,

isHoliday BIT,

Y SMALLINT,

FY SMALLINT,

Q TINYINT,

M TINYINT,

D TINYINT,

DW TINYINT,

monthname VARCHAR(9),

dayname VARCHAR(9),

W TINYINT

)

GO

---populate the table with data from jan 2000 to dec 31,2029

SET NOCOUNT ON

DECLARE @dt SMALLDATETIME

SET @dt = '20000101'

WHILE @dt < '20300101'

BEGIN

INSERT dbo.State_Calendar(dt) SELECT @dt

SET @dt = @dt + 1

END

--now populate all the other columns in the table

UPDATE dbo.State_Calendar SET

isWeekday = CASE

WHEN DATEPART(DW, dt) IN (1,7)

THEN 0

ELSE 1 END,

isHoliday = 0,

Y = YEAR(dt),

/*

-- if our fiscal year

-- starts on Sept 1st:

*/

FY = CASE

WHEN MONTH(dt) < 9

THEN YEAR(dt)-1

ELSE YEAR(dt) END,

--STATE Quarters are different

/*•1st quarter: 1 October 2012 – 31 December 2012

•2nd quarter: 1 January 2013 – 31 March 2013

•3rd quarter: 1 April 2013 – 30 June 2013

•4th quarter: 1 July 2013 – 30 September 2013

*/

Q = CASE

WHEN MONTH(dt) >=10 THEN 1

WHEN MONTH(dt) <=3 THEN 2

WHEN MONTH(dt) BETWEEN 4 AND 6 THEN 3

ELSE 4 END,

M = MONTH(dt),

D = DAY(dt),

DW = DATEPART(DW, dt),

monthname = DATENAME(MONTH, dt),

dayname = DATENAME(DW, dt),

W = DATEPART(WK, dt)

The issue I am having is that I want to pull the quarter from the current date but want it do follow my fiscal year. See below for example..

select *

from State_calendar

where

[highlight=#ffff11]Q=DATENAME(quarter,getdate()) and[/highlight]

Y=YEAR(GETDATE())

Problem is the quarter in sql says 9-17-2013 is in the 3 quarter but in my fiscal year it is in the 4th quarter, So I am getting date range

04-01-2013 to 06-30-2013