Basing a Query Filter on a calculation / variable

building reports webintelligence
1

Hi folk,

I have no doubt this has been asked before, but I cannot see anything similar.

I need to create a query filter that is something like:
where Fiscal year between (select fiscal year - 2) and (select fiscal year).
Select Fiscal Year needs to be a prompt and then select fiscal year - 2 is just a calculation of the prompt value - 2.

Can this be done, or do I need to present the user with 2 prompts instead?

Thanks

debbi.frost (BOB member since 2015-12-08)

2

Do you have access to the universe to create a pre-defined prompt ? Or do you need this @report without any universe changes ?

Shobhit_Acharya (BOB member since 2005-08-11)

3

If I could do it without universe changes that would be good. Is this possible?

debbi.frost (BOB member since 2015-12-08)

4

These types of parameters are best added on to the universe. In a pinch if you have to do this at a report level, I don’t see any other option except a custom sql. You’ll have to edit and customize the where clause in the custom sql.

where Fiscal_year between @Prompt('Fiscal Year','A','Class\Fiscal_Year',mono,constrained) -2 and @Prompt('Fiscal Year','A','Class\Fiscal_Year',mono,constrained)

I’d add a second data provider DP2 which is “custom” instead of the original DP, and source the results of DP2 in the first but that is not absolutely necessary though.

Shobhit_Acharya (BOB member since 2005-08-11)

5

Only at the Universe level possible. No edit at Query prompt to get dynamic dates possible. Go to Uni. level @prompt option(best practice) or Custom SQL(maintenance overhead and migration issues) !

kch :us: (BOB member since 2005-04-08)

6

Thanks folk.

debbi.frost (BOB member since 2015-12-08)

7

To be honest, I could never see the simple creation of a prompt vs. custom SQL, for custom SQL to be an option.

Create the prompt :wink:

Mak 1 :uk: (BOB member since 2005-01-06)

8

Thanks Mac. I would do if I had access to the universe. It is one that is due to be deprecated, and we have a dev freeze on it. I am specifying that in the new one that we have proper parameter prompts. Thanks for the advise.

debbi.frost (BOB member since 2015-12-08)

9

I’ve just seen so many problems later on.

Custom SQL is not a good option, IMO.

Mak 1 :uk: (BOB member since 2005-01-06)