Default Values with Value Prompts / Filters

[neilu]

I need to use a parameter and a value prompt to filter data in a report.  Now this part is straight forward, however, I need to assign the parameter a default value, this being the current month, so that it automatically shows data for the current month when the report is loaded.   The report then needs to be able to show data for previous months if the user chooses to change the parameter via the value prompt.

This seems relatively simple but I can't see a way of doing it using the options offered with the filters and value prompts.  Can anybody help?   

[rjcressw]

I have run into a similar problem and have resorted to using a map view with a column added that tells whether each row is in the current month or not.  Hope you can do something similar!

[bdybldr]

Try defining your parameter like this...

?parmFiscalMonth? = extract(month, {sysdate}) OR [FiscalMonth] = ?parmFiscalMonth?
AND
?parmFiscalYear? = extract(year, {sysdate}) OR [FiscalYear] = ?parmFiscalYear?

[neilu]

My parameter uses a look up table to find the period based on the first day of the current month.  I've tried a few different variations of the above solution but haven't succeeded yet.

[bdybldr]

Neil,
I misspoke.  I meant to write, define your FILTER...not parameter.  I'm sure you caught that though.

Anyway, all this filter does is set a default value for your parameter or allow the users to select a value.  Please explain how your parameter is used to get the current month and any other relevant information.  I'm sure we can make it work.

Thanks.

[neilu]

Ok I'll see if I can explain it! 

The report I'm creating is specific to a month within a year.  This is identified by a period value taken from a look up table within my model which identifies the month.  The look up table contains a description of the period i.e. 'May 2006' and the first day of that month etc.

When I create a prompt page to set the period, this works fine and the selected period identifies the first day of the month value to pass to the report, where it is then used by the filter.

Ideally I want to remove this step and where my filter is defined as

[FirstofMonth] = ?Period?

I want it to be something like

[FirstofMonth] = ?Period? OR [FirstofMonth] = _first_of_month(current_date)

with the default taking precedence where the parameter isn't set.  I've tried quite a few different methods (this one, case statements, etc) with no luck so far.

[BIsrik]

The requirement I understood is that the prompt should show the current month whenever u run the report. If the user wants previous month he enters by removing the defualt value.  Please let me know my understanding is r8. I feel javascript needs to be written to set the default value of the prompt since the default value should be dynamic.
 In crn u don't have any option( ie. built in the software) to set the dynamic default values.
  Alternatively if u r not happy with javascripts u can maintain the report regularly. Thats update the static value prompt value every month.

Srik

[bdybldr]

Try this...

?Period? = _first_of_month(current_date) OR [FirstofMonth] = ?Period?

This sets the default value for ?Period? to the first day of the current month, unless the user selects a value for [FirstofMonth].  In which case, the user selected value will be used.

HTH.  Keep us informed.

[neilu]

Try this...

?Period? = _first_of_month(current_date) OR [FirstofMonth] = ?Period?

This sets the default value for ?Period? to the first day of the current month, unless the user selects a value for [FirstofMonth].  In which case, the user selected value will be used.

HTH.  Keep us informed.

I got that to validate when I used the current_timestamp because of the [FirstofMonth] value being of the datetime data type.  When I ran it with the filter set to 'required' it brought up a prompt page.  With the filter set to 'optional' all the data is returned so in both cases no default has taken effect.

[neilu]

The requirement I understood is that the prompt should show the current month whenever u run the report. If the user wants previous month he enters by removing the defualt value.  Please let me know my understanding is r8. I feel javascript needs to be written to set the default value of the prompt since the default value should be dynamic.
 In crn u don't have any option( ie. built in the software) to set the dynamic default values.
  Alternatively if u r not happy with javascripts u can maintain the report regularly. Thats update the static value prompt value every month.

Srik

You just about got the jist:

Basically when the report is opened, I want it to show data for the current month.

There is then a drop down at the top (which is automatically populated with the periods from the last 12 months) which can be used to update the parameter controlling the filter on the query, thereby allowing the user to view any data from the last 12 months, one period at a time.

[mrcool]

As srik said even we used javascript fr getting cur month as def n there by user can change the months in value prompts...


Thanks,
Mrkwel