Prompt value with Scheduler

[Esperanza]

Hello cognos experts!
I have a report with a prompt for the fiscal Month, I have a value 'Current' in the list which is the current fiscal Month (We have a flag in our Time Calendar).
If I run manually the report, It works fine.
I mean if we are in may, the 1st of June the value Current switch for June and the values for the report are June's values. 

The problem is with Cognos scheduler, even if I picked the value 'Current', the 1st of July it stayed in June and on.
I had to reselect the 'Current' value in the scheduler to have the July values.

Now I would like to have the 'Current' value selected by default in the prompt.
(Properties defaults values -> Current)
It's auto-submit.
but It does not works, the Current value in the list is not selected.
Any Idea?

[Esperanza]

Ok,
I realized that in the properties, for the default value, I have to put the use value (not the display value) which is the Fiscal Month Id in my case example : 201506.
That value changes every month.
So there is no way to have the current month selected by default??

[gpollock]

When you schedule it, you use the default value, right?  If "Current" is always going to be an option, what happens if you go into the report schedule, and set the option to override the default value, but manually select the "Current" option?

It doesn't make sense that what you're trying isn't working.

[Esperanza]

I know it does not make sense,  but in the scheduler, the override the default value, does not seems to work well.

Via the scheduler I selected 'Override the default value' I want it to always run 'Current' for Fiscal Month  (see documentation).
So right now the report runs every day and the values are updated correctly.
When the month switch to the next one it still show the value of the last month.
I have to re-open the scheduler -> Override the default values and select 'Current' and then I have the good current month.
I thought that auto-submit would fix my problem, but no.

That is why, now I want the default value 'Current' selected by default

[gpollock]

How is the "Current" Fiscal month set?  Is it a setting on the prompt, or a flag on your date dimension?

[Esperanza]

It is a flag from our time Dimension.
It is a relational data package.

[gpollock]

Okay, that sounds good.  What about the prompt?  It's a static value you added?  What does the filter look like in your query when you have this selected?  Do you have a filter with a case statement, where if "Current" is selected, it filters on that flag; otherwise, it filters the month by the value of the parameter?

[Esperanza]

relational data
The prompt is base on a query.
And the fiscal Month desc show 'Current' if it is the current fiscal mth.

I realized that I don't have to Override the default values in the scheduler because I do not have a default value in the prompt.
The thing is, In the scheduler, it is the only way to pre-select 'Current'.
If I don't override the default value in the sheduler,  the report is in error when run via the scheduler because no prompt were selected.
I want to find a way to pre-select 'Current' in the prompt but the default selection is based on the dataItem value which changes every month.

Thank you in advance.

[Esperanza]

Here is the filter:

[GM].[Dim Time Fiscal].[Fiscal Month Id]=[GM].[Dim Time Fiscal].[CURR_FisMonthId]

This is a big report with many queries (union all and joins etc) all the filter have the ?pFiscalMth?

[gpollock]

Here's what I would do.  Say you want to filter on DimTime.Month if the month is selected; otherwise set it to the current month.  Set ?Month? to whatever prompt and make the prompt optional.  Create a filter in your query like below:

Case when ?Month? is not null --(or 0 or whatever)
    then DimTime.Month = ?Month?
    else DimTime.CurrFiscalMonth=1
end

I made it more generic for your situation.  Please adjust the filters for your syntax or model.  You should be able to test this easily in your sample reports; and then scheduling should be a breeze.

[Esperanza]

Thank you, I will try that.
Also, I found something with the use of JavaSript.
https://www.ironsidegroup.com/2013/04/02/report-studio-cookbook-dynamic-default-prompts-done-right/

Again, thank you a lot

[Esperanza]

The following Java script works fine.
My current value is selected and the report run (if done manually) with the selected value and the data is good.
Again, the problem is with the scheduler.  I got the following error :

QFS-ERR-0139 The request has multiple errors. RQP-DEF-0354 The query contains one or more unresolved prompts. QE-DEF-0387 Invalid format for numeric prompt 'pDate'.
How can it works manually but not via Cognos Scheduler ?

I have only one prompt.

-----------------------------------
<script language="javascript">

function setDefaultValue()
{
var form = getFormWarpRequest();
form._oLstChoicesmylist.options[0].selected = true;

}

setDefaultValue()

</script>
---------------------------------------------

[gpollock]

The problem with Javascript is it's client-side, and only runs when the user both runs from a browser that can handle javascript, and also has javascript enabled.  In this case, running from a scheduler is running on the server, and the server isn't running a browser.  Any report options that rely on javascript should only be conveniences to a person running the report--never something you need to get it working properly.

Sorry Javascript isn't working out.  What about the filter with case statement?

[Esperanza]

Well, I am very sorry to learn that today.
I am disappointed that Cognos has no function for that.
I must not be the only one who has to run the current fiscal month via scheduler.
For some report the value is a sequentialIdDate which I cannot extract from a date.
For the fiscalMonth,

I could do something like that :
if (?pFiscalMonthId? is null) then
((cast(
substring(cast(current_date,varchar(10)),1,4)||substring(cast(_add_days (current_date,-1),varchar(10)),6,2),integer)+100)-1)
else (?pFiscalMonthId?)

Thank you for everything I learned a lot this week about prompt and JavaScript!