OBIEE: Display the Fiscal Period Months of the Fiscal Quarter based on the Fiscal Period selected in the Prompt

Dear All,

In one of my project, I faced a task where I had to display the current fiscal months and its previous months in a fiscal quarter in the report based on the fiscal period selected.


Example:

If I select Fiscal Period as 'P11-2013' in the prompt, it means:

• Financial Year: 2013
• Financial Quarter: 4
• Financial Month: February

So as per the requirement, we have to display the data for Jan-2013 and Feb-2013 only.

Likewise, if we select 'P10-2013' (in a request variable), we have to display data for Jan-2013.

Similarly, if selected 'P12-2013', then we have to display data for Jan-2013,Feb-2013 and March-2013.

After the good analysis, I was able to prepare to the solution.

Please find below the sample code.

case
(CASE
WHEN (CASE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS double)+3,12)
WHEN 0 THEN 12 ELSE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS Double)+3,12) END) >=1
AND (CASE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS double)+3,12)
WHEN 0 THEN 12 ELSE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS Double)+3,12) END) <=3 THEN 4
WHEN (CASE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS double)+3,12)
WHEN 0 THEN 12 ELSE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS Double)+3,12) END) >=4
AND (CASE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS double)+3,12)
WHEN 0 THEN 12 ELSE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS Double)+3,12) END) <=6 THEN 1
WHEN (CASE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS double)+3,12)
WHEN 0 THEN 12 ELSE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS Double)+3,12) END) >=7
AND (CASE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS double)+3,12)
WHEN 0 THEN 12 ELSE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS Double)+3,12) END) <=9 THEN 2
WHEN (CASE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS double)+3,12)
WHEN 0 THEN 12 ELSE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS Double)+3,12) END) >=10
AND (CASE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS double)+3,12)
WHEN 0 THEN 12 ELSE MOD(CAST(SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) AS Double)+3,12) END) <=12 THEN 3
END)
when 1 then
( case when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) in ('01','1')) then Table.column=@{Period_name}{'P11-2013'}
when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) in ('02','2')) then Table.column in (@{Period_name}{'P11-2013'},
('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -1,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)))
 when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) in ('03','3')) then Table.column in (@{Period_name}{'P11-2013'},('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -1,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)),('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -2,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)))

 end)

 when 2 then
 (case when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) in ('04','4')) then Table.column=@{Period_name}{'P11-2013'}
when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) in ('05','5')) then Table.column in (@{Period_name}{'P11-2013'},
('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -1,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)))
 when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) in ('06','6')) then Table.column in (@{Period_name}{'P11-2013'},('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -1,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)),('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -2,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)))

end)

when 3 then
 (case when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) in ('07','7')) then Table.column=@{Period_name}{'P11-2013'}
when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) in ('08','8')) then Table.column in (@{Period_name}{'P11-2013'},
('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -1,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)))
 when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) in ('09','9')) then Table.column in (@{Period_name}{'P11-2013'},('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -1,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)),('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -2,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)))

end)
when 4 then
 (case when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)=('10')) then Table.column=@{Period_name}{'P11-2013'}
when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) = ('11')) then Table.column in (@{Period_name}{'P11-2013'},
('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -1,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)))
 when (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2) = ('11')) then Table.column in (@{Period_name}{'P11-2013'},('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -1,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)),('P'||cast(TIMESTAMPADD(SQL_TSI_MONTH, -2,
evaluate('TO_DATE(%1,%2)' as date,
 (SUBSTRING(@{Period_name}{'P11-2013'} FROM 2 FOR POSITION('-' IN @{Period_name}{'P11-2013'})-2)||'-'||SUBSTRING(@{Period_name}{'P11-2013'} FROM LOCATE('-',@{Period_name}{'P11-2013'})+1 FOR LENGTH(@{Period_name}{'P11-2013'}))),
 'MM-YYYY')) as char)))

end)
end

N.B: Please change the code, as per your requirement.

Please provide your comment for any clarification.

Regards,
Angad