So I'm trying to write up something for cognostechie to help him out with a default selection for a prompt, but I think maybe there's something fundamental I might be missing (PaulM, hay-ulp!).
http://nicbertino.com/?p=48
This works perfectly fine.. on a prompt page. On the report page, I have everything setup to do what I need except one thing - to set the prompt to current year and THEN filter by that year.
Here's the code I have so far (in jQuery, but it's pretty easy to read if you know JS):
<script type="text/javascript">
jQuery.noConflict();
jQuery(document).ready(function() {
jQuery("#selectbox select").change(function() {
promptAction('finish');
});
jQuery("#selectbox select").val((new Date).getFullYear());
}); // End Ready
</script>
So it's pretty simply - the jQuery("#selectbox select").val((new Date).getFullYear()); line sets the value prompt to the current year, and whenever that prompt is changed to another value, the report is refreshed using this:
jQuery("#selectbox select").change(function() {
promptAction('finish');
});
.. also working beautifully. My issue is that when the report is run, the option is changed to the current year in the prompt, but all of the data is showing. Any ideas on how to draw the current year and filter on that by default until the user changes the value?
You forgot one thing: the jQuery will alter the prompt only after the report has rendered. That's why it works perfectly in the prompt page.
To get around that in the report you'll have to use the default value in the macro prompt.
Your filter should look something like:
SQL Server
[Year] = #prompt('Year','integer','year(getDate())')#
Oracle
[Year] = #prompt('Year','integer','to_char(sysdate, 'YYYY')')#
I think cognostechie is using a cube (Oy vey on him for using detail filters!) so the previous two examples won't work.
For this I'll also refer to a blog post:
http://cognospaul.wordpress.com/2010/09/26/macros-part-2-of-timestamp-of-a-dimensional-twist/ (http://cognospaul.wordpress.com/2010/09/26/macros-part-2-of-timestamp-of-a-dimensional-twist/)
The slicer will need to look something like:
[Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].#sb(prompt('Year','integer',timestampMask($current_timestamp,'yyyy'))+'0101-'+prompt('Year','integer',timestampMask($current_timestamp,'yyyy')+'1231'))#
Which will resolve to:
[Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20110101-20111231]
Hope that made sense.
Paul - Thanks a ton for this !
However, my reauirement is to have a filter that would filter the report for the current year when the report is run first time and also filter based on what the user selects from the prompt from 2nd timke onwards.
melee - You are almost there and thank you so much for making this effort !!
I have prompts only on the prompt page and I actually made it work when I run the report first time by this:
getFormWarpRequest()._oLstChoicesyear.selectedIndex = 1;
promptbuttonfinish();
I have a filter in the report query that says:
[Dimension Name],[Year] = ?PromptName?
Yes, it is a detail filter and Paul is correct, OUCH ! But it worked. The report opened up showing only the current year's data.
Now here's the problem - When I select another year from the Prompt (on the Report Page), it throws an error. I think because I used the promptbuttonfinish(); function in the HTML item so now it's automatic and does not follow the Prompt button !!
I think your code is almost done. The only thing you need is the filter in the report and another
command to submit the prompt. After
jQuery("#selectbox select").val((new Date).getFullYear());
can you put promptAction('finish')
Create a Layout calculation in your report and use paramDisplayValue(?PromptName?). That will show whether or not the current year is submitted or just selected. If it doesn't show anything then the filter will not receiving any value so that is the problem. That might get done by promptAction('finish')
Again, I really appreciate your efforts for this !
Quote from: cognostechie on 02 Feb 2011 12:11:32 AM
I think your code is almost done. The only thing you need is the filter in the report and another
command to submit the prompt. After
jQuery("#selectbox select").val((new Date).getFullYear());
can you put promptAction('finish')
You can't do that, unfortunately. I tried, but it threw an error. I'll give it a shot again tomorrow so you can know exactly what it was. That's why I posted the thread :)
That's exactly what the default value of the macro prompt does. If it doesn't receive a value, it uses that.
I think the following should work for you.
- Create your prompt with the year level from your cube.
- Place the HTML item after it, and put a render variable on the HTML item with the expression: paramValue('Year') is null
- Put the following macro into your slicer: #prompt('Year','mun','[CurrentYearMember]')#
When the report runs the reporting engine will begin with checking all prompts and parameters. It will see the prompt object, but since it doesn't have a default value nothing will be returned to the parameter. Since the parameter is empty the prompt macro will use the default value, slicing the query by [CurrentYearMember].
After the query has been processed it will begin to render the page. The variable on the HTML item will be processed, and since paramValue('Year') is null, the HTML item will be rendered, highlighting the correct row in the prompt object.
Now the report has been rendered the user can select another year in the prompt. That year will be passed to the prompt macro, correctly slicing the query. When the page begins to generate the variable on the HTML item will be checked again, and this time it will not be rendered, so the row in the prompt object won't be changed.
The MDX when you use a detail filter is usually correct, but it leads to poor habits. There have been a number of times where just moving the detail filters to the slicers substantially improved the speed of the report.
Excellent !!
It worked ! I did not use the render variable because I need it to select the Current Year again after the user selects a different year from the Prompt and the report runs with the selected year.
By the way, I mistyped in my earlier reply that I am using a Prompt Page. I do NOT have a Prompt Page and that is why I have to use optional filters because they do not want to see a Prompt Page.
So this is what I did in the HTML item:
<script>
var f = getFormWarpRequest();
var prompt = f._oLstChoicesyear;
prompt.remove(1);
prompt.remove(0);
prompt.removeAttribute("hasLabel");
document.formWarpRequest._oLstChoicesyear.options[0].selected = true;
</script>
Interestingly when I tried to put the default value in the HTML item, it did not accept that as the default.
document.formWarpRequest._oLstChoicesyear.options[0].defaultSelected = true;
This is supposed to work but it didn't. Your idea of creating a Prompt Macro and passing the Current Year as the default worked.
Thanks again Paul and melee !
and by the way, I cannot use a Slicer because it opens up the Prompt even if I put the default there. It opens up the prompt and lets you hit enter without providing a value because the default is given but it still opens up the prompt.
So I have to use detaliled filters.
Ok. Got it to work with Slicer as well.
One more post to this thread -
Just so people don't get confused by why it wasn't working (slicers etc) earlier and worked later -
Cognos does caching of the report specs so even after changing things, it was working the earlier way many times. Log out and log back in and the changes took effect.