Current and Prior values based on YTD, Month or Qtr prompts

[system]

Hi BOB,

My customer asked a report with single prompt, where user enters YTD or Q1,Q2,Q3,Q4 or numbers from 1 to 12 (for months). Based on the prompt value, the report should get data for current and prior periods as shown below.

a) If it’s YTD, they expect it to be a Fiscal Year level YTD performance (From Apr 1st to Mar 31)
b) If it’s QTR, then both prior year and current year performance by Q1, Q2, Q3, or Q4
c) If it’s by MONTH, they would like to see current year’s and previous year’s comparison (ex: 2018 April vs 2019 April)

I don’t know where to start . Any ideas? Can anyone guide me in the right direction?

---

anil.ganga1 (BOB member since 2007-07-04)

[system]

Maybe build the filters in the universe in order to get the correct date ranges for each.
I don’t think you can make the prompts Optional in the universe, as the SQL wouldn’t generate correctly. I saw this in several posts on BOB.

I did find this link that might help:

https://blogs.sap.com/2015/11/26/custom-prompt-at-universe-level/

---

Rboggs1 (BOB member since 2019-04-08)

[system]

It appears I may have given you bad info,
Check this post about Business Filters in the IDT.

120×120 10.4 KB

In Information Design Tool 4.2 (IDT) I am developing a universe that connects to a single relational database. I want to add a new filter-object (or a ‘predefined condition’ as it is called in Universe Design Tool/Designer). While creating the filter-object, IDT gives me the option to make it filter-type ‘Native filter’ or ‘Business filter’. I assume a ‘Native filter’ is stored in the Data Foundation layer and ‘Business Filter’ in the Business Foundation layer but are there any more difference…

---

Rboggs1 (BOB member since 2019-04-08)

[MarkP]

I’d consider some sort of derived table or view in the database to handle this and use it in conjunction with a calendar table.

Do you have a calendar table in your database and does it support fiscal years? If not, that would be a good place to start.

[system]

Thanks for your suggestions Mark and Rboggs1–The article you provided gave some idea. I came up with below code which I feel it is working for now have to test it thoroughly.

1. First, I have a db column Fiscal Year which always have current fiscal year.
2. We have fiscal month (stores 1,2,3…12) and fiscal quarter (stores 1,2,3,4).

I created two filters in universe.

Current Period Filter

“Invoice Date”.FSCL_YR_NO=to_number(@Prompt(‘Enter Year No/Year and Quarter No/Year and Month No:’,‘A’,Mono,Free))
or
{fn concat(“Invoice Date”.FSCL_YR_NO,“Invoice Date”.FSCL_QTR_NO)}=({fn substring(to_number(@Prompt(‘Enter Year No/Year and Quarter No/Year and Month No:’,‘A’,Mono,Free)),1,4)})||
{fn substring(to_number(@Prompt(‘Enter Year No/Year and Quarter No/Year and Month No:’,‘A’,Mono,Free)),5,1)}
or
{fn concat({fn concat(“Invoice Date”.FSCL_YR_NO,‘0’)},“Invoice Date”.FSCL_MO_NO)}=
({fn substring(to_number(@Prompt(‘Enter Year No/Year and Quarter No/Year and Month No:’,‘A’,Mono,Free)),1,4)})||
{fn substring(to_number(@Prompt(‘Enter Year No/Year and Quarter No/Year and Month No:’,‘A’,Mono,Free)),5,3)}

Prior Period Filter

“Invoice Date”.FSCL_YR_NO=to_number(@Prompt(‘Enter Year No/Year and Quarter No/Year and Month No:’,‘A’,Mono,Free))-1
or
{fn concat(“Invoice Date”.FSCL_YR_NO,“Invoice Date”.FSCL_QTR_NO)}=({fn substring(to_number(@Prompt(‘Enter Year No/Year and Quarter No/Year and Month No:’,‘A’,Mono,Free)),1,4)}-1)||
{fn substring(to_number(@Prompt(‘Enter Year No/Year and Quarter No/Year and Month No:’,‘A’,Mono,Free)),5,1)}
or
{fn concat({fn concat(“Invoice Date”.FSCL_YR_NO,‘0’)},“Invoice Date”.FSCL_MO_NO)}=
({fn substring(to_number(@Prompt(‘Enter Year No/Year and Quarter No/Year and Month No:’,‘A’,Mono,Free)),1,4)}-1)||
{fn substring(to_number(@Prompt(‘Enter Year No/Year and Quarter No/Year and Month No:’,‘A’,Mono,Free)),5,3)}

This code works for only 6 or more digits input values.

For Year: 002020
For Quarters: 020201, 020202, 020203, 020204 (concat of year and quarter)
For Months: 202001,202002…2020012 (concat of year+zero+month)

To_number function is used to remove leading zeros.

I have added two queries in report. one query with current period filter and one with prior period filter. Report seems to be working now.

Now My next step is to create a custom lov and map to the input values (fingers crossed, have to see how it works ). so my final prompt should give me (YTD, QTR1, QTR2, QTR3, QTR4, APR, MAY, JUN…MAR) Our fiscal year is from APR to MAR.

Hope this could help someone else.

---

anil.ganga1 (BOB member since 2007-07-04)