Value prompt drop down - hide values

[cognosun]

Hi Guru's,

I've got 4 types of measures ( columns), in a value prompt drop down....in which one column ( C1) gets populate on 5th of every month...and remaining columns get populate on 10th of every month.

So if a user is running report before 10th, C1 should be hidden or disabled from value prompt drop down....can anyone suggest a solution on this

Many thanks in advance,
Joys

[Lynn]

Is your prompt control currently set up to run using static choices? If so I'd change it to be query driven and include some logic to look at the current date and return only the appropriate choices.

If you have a date dimension, for example, you could have the filter return current date plus 2 or 3 additional dates based on whether it is before, on, or after the 10th. Then in your query item expression use a case statement to resolve to a value for each of the measure choices.

[cognosun]

Can you post a a rough draft query....

[Lynn]

I have no idea what query subjects you have available. You will need a query subject that you can reliably extract either three or four records from consistently. You will use these rows to fabricate the data for your prompt control. A date dimension is a good candidate for this.

So let's pretend you have a date dimension. You need to create a filter that returns 3 rows if the current date is before the 10th or else 4 rows if the current date is on or after the 10th. The filter expression for that might look something like this:

Code Select Expand


where
(
  day([cal_date]) < 10
  and [cal_date] between _add_days(current_date, -3)
                         and  _add_days(current_date, -1)
)
or
(
  day([cal_date]) >= 10
  and [cal_date] between _add_days(current_date, -3)
                         and current_date
)


Now you need a query item with a case statement that will look at each [cal_date] value that is returned by the query and transform it to C1, C2, C3 or C4. These are the prompt choices you want to present to your user. If you need both a use and display value then you'll need to query items.

That case statement would look something like this:

Code Select Expand


case
when [cal_date] = current_date then 'C1'
when [cal_date] = _add_days(current_date, -1) then 'C2'
when [cal_date] = _add_days(current_date, -2) then 'C3'
when [cal_date] = _add_days(current_date, -3) then 'C4'
end


Associate the query to your prompt control and it should only present C1 as a choice when the system date is on or after the 10th day of the month.

If you do not have a date dimension then just find some other query subject from which you can reliably extract three or four rows and adjust the where clause accordingly.