Hello,
I am starting to work with multidimensional reports.
My report which has a prompt that is linked to boolean style variable [Query2].[Day_Calc]<>ParamDisplayValue('prompt') that is changing box type to none if condition is met.
Is there a way of adding default value to current_date?
here is the report.
I also have tried to use variable but the highlited part seems not to be working:
( ParamDisplayValue('prompt') is null
and [Query2].[Current_Date]<>[Query2].[Day_Calc] ) OR
(ParamDisplayValue('prompt') is not null
and[Query2].[Day_Calc]<>ParamDisplayValue('prompt'))
Right, if I understand correctly what you have done here, it's no wonder it takes longer.
You're bringing in all records, and then have Cognos run over the HTML and hide the records you don't wish to see.
When you're using a filter function or a slicer, you limit the number of records pulled, which is faster. The more records you pull, the longer the query will take.
Yes you are right this is exactly what my report does.
The problem with filter or slicer or style variable I can't figure out how to add current_date as a default as in the prompt the default value is static field, and current date is dynamic.
Well, that's a whole new topic.
There are several ways you can go about adding current_date as your default prompt selection. I see you're using a value prompt, so you can add a static value and set you slicer so that if the static value is selected it would slice by current_date. There are several ways to achieve this, and they depend on your cube structure and/or your cube vendor.
Another, cooler option is to use JavaScript to select the date that matches today's date.
I'm attaching a 10.2 (I believe that's the version you're using) sample for JS solution (It's based on samples and therefore addresses month, but you should be able to find your way through changing it to match your case. Also, I did not use the Prompts API in this case, although it's possible, b/c calculating the MUN for use value is a pain).
Good luck!
This will be very usefull in other projects but for this project the cube will be rebuilt each week so the current day value will never show today() so JS would not work here.
In my date dimension in cube I have current day.
Could you show example of filter/slicer that would filter the date, but default it to current_date?
I have uploaded my cube Date dimension diagram
Cool, you have a Current Day hierarchy. That simplifies things.
Try the following:
Create your prompt as you would usually, with all dates.
Then, create your date member like this:
#prompt('monthPrm','memberUniqueName','firstChild([MUN].[of].[current day].[root])')#.
This will insert the value of the prompt to the data item, however, the default value will be the first child of the root member in the current day hierarchy, which is basically current day.
Attached a sample using months.
Oh this is briliant. Worked perfectly! Thank You!
Happy to help.