Custom LOV dummy value to use as a prompt

Hi foreros!

I am in 3.1 with this client.
I am trying to get a parameter to work (numbers 1 to 12), which should work as a fiscal year start.

This works perfectly in a crosstab, if I used a hard-coded number, such as 6.
[CY] is a calendar year
[CY Month Number] is a calendar month number.
=If [CY Month Number] >= 6 Then ([CY] + 1) Else [CY]

If I use a separate query (DP) and try to use in the report - either as a measure, with max() function, or detail object (of [CY Month Number]) I always get #MULTIVALUE error.
This is what it SHOULD look like:


The requirement is to make a custom fiscal year start, which should reset both month numbers and the actual year (!). The months are working fine. I can’t get the year to work.

Things I have tried:
=Max([Month Prompt].[CY Month Number]) + [CY]

Where [Month Prompt].[CY Month Number] is a single numeric value from a different query (DP) from a prompt.

Can someone suggest a good way to do it?

If you have access to the underlying database you could create a view and add it to your Universe.

We had the special need for ISO dates (for example sometimes January 1 is part of last year or December 31 is part of next year) so our view had several columns that were just computations on what was simply a list of dates. Each year we’d have a task that wiped the table and then inserted new rows from Dec 1 of year-1 to Jan 31 of year+1 (so about 427 days) that was done at the end of the final year end processing.

Then we’d just pull those date objects into the reports.

1 Like

Thank you but the requirement is the FY start month is dynamic. It has to come from a user prompt.

I may have found a solution, will post an update if I get it to work.

Try using the UserResponse() function to get the user’s FY Start Month selection and use that in your formula instead of the value returned by the query.

1 Like