Dynamic Previous month filter

building semantic layer
1

Has anyone created a dynamic filter to get the previous month data? I want to do this to handle year end and for use in scheduled reports. I am struggling through the logic with the options available in the Designer tool.

We are running against a Oracle db.

Thanks for any help.
Kevin

MaxVonM :us: (BOB member since 2013-02-14)

2

Hi Kevin,

We have an exact requirement and here is the code that will get you what you want…


<TimeDimTable>.MonthName = CASE WHEN @Prompt('Select Report Month','A','TimeDim\Month Name',Mono,Constrained,Not_Persistent,{'Last Month'},User:1) = 'Last Month'
THEN
(CONVERT(VARCHAR(3), DATENAME(MM, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())),GETDATE()),100)), 10)+ '-'+ SUBSTRING(cast(DATEPART(yyyy, DATEADD (mm,-1,getdate())) as char(4)),3,4))
ELSE
@Prompt('Select Report Month','A','TimeDim\Month Name',Mono,Constrained,Not_Persistent,{'Mon-YY'},User:1) END

Our report is scheduled to run on the first of every month for the previous month’s data. I have just selected ‘Last Month’ as the value for my Month prompt while setting up the schedule.

“TimeDim\MonthName” object give you the LOV for Month selection. The format that we wanted was Mon-YY and the SQL below does it for me. (Ours is a SQL Server DB backend…but you can get an Oracle equivalent quite easily)


SELECT
  DerivedTimeDim.MonthName
FROM
  ( 
  select distinct
ARDS.BO.v_Dim_Date.MonthName as MonthName,
ARDS.BO.v_Dim_Date.Date as Date
from ARDS.BO.v_Dim_Date
WHERE
CAST(ARDS.BO.v_Dim_Date.Date AS DATE) >= CAST(DATEADD(YEAR, DATEDIFF(YEAR, 0,DATEADD(YEAR, -1, GETDATE())), 0) AS DATE)
AND
CAST(ARDS.BO.v_Dim_Date.Date AS DATE) <= CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS DATE)
AND
ARDS.BO.v_Dim_Date.LastDayOfMonthIndicator = 1
  )  DerivedTimeDim
  order by Date desc

Let me know. Good luck :slight_smile:

Regards
CV Bhat

Supernova :uk: (BOB member since 2013-08-30)

3

Depends on exactly what you mean by ‘last month’s data’ but here are some structures that may help you:


SELECT TRUNC(SYSDATE,'MONTH')
         ,LAST_DAY(SYSDATE)
         ,ADD_MONTHS(SYSDATE,3)
         ,NEXT_DAY(SYSDATE,'Monday')
FROM DUAL;

SlimBob (BOB member since 2013-09-06)

4

This code is interesting to have set up in Designer(if doable).

We are a DB2 shop so I am sure there are issues with dates. But Month name is something not commonly used. Is there a way to modify this for say going back 1,2,52 weeks ?

I am not too good with playing around with complex stuff like this.

di652 (BOB member since 2005-02-24)

5

Hi Kevin,

This Syntax works well for us in Oracle and may be used in recurring scheduled reports without prompting for the previous month’s date range;

  Trunc(DATE_TABLE.My_Date_of Interest)
   between  last_day(add_months(trunc(sysdate), -2)) + 1
   and  last_day(add_months(trunc(sysdate), -1))

[Moderator Edit: Added code formatting - Andreas]

Joe Szabo :us: (BOB member since 2002-08-19)

6

Have a look at the date sticky at the top of this forum. Lots of date code snippets for different databases. Add the ones you want as objects to your universe and use them in your filters.

Debbie

Debbie :uk: (BOB member since 2005-03-01)