Prompt Default Selections

[Tracy]

I've got something like this in my query:
#prompt('p_promptname','type','defaultcalculation')#

If I don't select anything at the optional prompts, then my report will filter on the defaultcalculation.  This is what I want and appears to work.

I also put a Value Prompt on my report page (not a prompt page).  It by default just shows "p_promptname" and we have to select a value.   But I want the prompt to also default to defaultcalculation when the report first comes up so that the value prompt matches what's actually being used at that time (less confusing for users).

I know that I can enter the "Default Selections", but it looks like I have to hardcode it with either a string or a MUN.  But I hate hardcoding... and it's actually not even feasible since the data is constantly changing.  Can I enter calculations in the Default Selections?  I can't get it to work, but am hoping it's just a syntax thing.

[CognosPaul]

There may be a couple of things to try. The first thing would be to create a static value with a generic display and the defaultcalculation as the use, defaulting to that. If you're using a token instead of a MUN prompt the use/default can be a formula.

The second idea is to use a javascript formula to automatically select the desired item in the list. The following will change the default p_promptname title to something more friendly and automatically select the first value:

Modify the header of the value prompt:

   1. Insert HTML item to the left of the prompt. (This item has to appear prior to the prompt in page generation.) The span id creates the identification of the object.

                <span id = "Type">

   2. Insert HTML item to the right of the prompt .  You may want to get rid of the canSubmitPrompt(); if you get an annoying popup.

                 </span>
                <script type="text/javascript">               
                                var theSpan = document.getElementById("Type");
                                var theSelect = theSpan.getElementsByTagName("select");
                                theSelect[0].options[2].selected=true; //Index0, 0=title, 1='----',2=first value
                                theSelect[0].options[0].text = 'Select Injured Type';
                                canSubmitPrompt();
                 </script>

This is useful for selecting the current year, for instance.

[Tracy]

Wow!  Thanks so much for the reply!

I've actually been trying your first suggestion for a couple of days and it's not working.  For instance, suppose I want a prompt of employees and I want it to default to me.  I created a query that not only contains all the Employee ids (use) and Employee names (display), but also a dummy record that includes a calculation which pulls my Employee Id (from our Authentication source) and a generic name like "MY STUFF".  Then I set the Default Selections for the prompt to "MY STUFF". 

When I open the report, it does show "MY STUFF" in the prompt (keep in mind that the prompt is right on the report page, not on a separate prompt page where I have to click Submit).  But it appears to pass NULL or perhaps "MY STUFF" to my query (not the employee id) and my report is blank.  That's the opposite of what I had before where the report pulled correctly but the prompts didn't show up right.

As for Javascript, I was trying to avoid it because I see "this is not supported... who knows how it will upgrade in future releases" all over the place.  But I still gave it a shot.  At the very least, you've shown me how to customize the label within the drop down box (for instance, to "Select Injured Type").  That will definitely come in handy.

I did put in the Javascript and was able to set it to the first value as you have.  But I expected my report to filter by that first value and it didn't.  It didn't pull up anything.  It basically gave me the same results where the prompt looks right, but the report returns nothing.

[CognosPaul]

I should have mentioned that the javascript will only select the value after the report has rendered; you'll still need to use the function in the prompt macro.

I'm surprised though that the first suggestion is not working. If you're referencing the employee id from the authentication source you must be using a macro for that. Can you share the expression?

Instead of waiting for your reply, I'm going to blunder on and assume that your original prompt macro must look something like:

Code Select Expand

#prompt('parameter','memberuniquename','[cube].[dimension].[hierarchy].[leve]->:[YK].[[table]].[field]].&['+$account.personalInfo.employeeID  +']]]')#

If it looks like that then you would not be able to put the '[cube].[dimension].[hierarchy].[leve]->:[YK].[

].[field]].&['+$account.personalInfo.employeeID  +']]]' then in the use value. Prompt macros won't accept prompt macros as parameters Instead you could place Code Select Expand #'[cube].[dimension].[hierarchy].[leve]->:[YK].[[table]].[field]].&['+$account.personalInfo.employeeID  +']]]'# into a new data item (let's call it [Data Item1]). The static choice in your prompt could then be [Data Item1] while the display would be MY STUFF. The default would be [Data Item1], and it would send the parameter to the query before the report is rendered, thus solving the problem you had with the javascript.

[Tracy]

I'm using this to get the id from our authentication source:
#csv(@account.parameters.employeeid)#

You are incredibly helpful.  But I think there's big holes in my understanding of things that's preventing me from getting this to work.

I understand that I can add Static Choices.  So I could enter Employee Id 123 as the Use and "Tracy" as the Display which would make that entry appear in my drop down box.  Or to generic-ize it a bit, I could enter 123 and "My Stuff" so that I could hardcode the Default Value to "My Stuff" while still sending 123 to the query.  When I do that and open my report, I do see "My Stuff" as the chosen value in the prompt.  But if I also add ParamValue('p_associate') to my display, it shows nothing... not until I select another value in the drop down box and then reselect "My Stuff" do I see ParamValue('p_associate') return 123.  So I take it from this that my prompt is just faking it's initial value to look pretty on load... and that seems to be causing my report to return nothing on load.

Assuming the above worked, would continue to try to generic-ize the Id:  I have been able to get the Default Selections to work when I type in a value like 123 or even the MUN.  But these are basically hardcoding it which I don't want to do.  I would rather use #csv(@account.parameters.employeeid)#.  So you suggested adding [Data Item1] using the syntax below (#'MUN'#).  But where am I adding this Data Item?  Is it one of my existing queries?  A new query that just contains [Data Item1]?  Do i have to associate this query with the page or something?

In other words, it sounds like you are saying that I could create a calculation/dataitem that builds the MUN and then I could reference that calculation/dataitem in the Default Selections.  It just doesn't seem to be working.

[CognosPaul]

A question, are you using a real cube or a DMR?

The ParamValue function will only work once a value has been entered into the parameter itself. When you have a default value in the prompt the default value doesn't actually go into the parameter. The way around that is to have a simple boolean variable on the layout calculation. case when paramvalue(param) is null then 'my stuff' else paramdisplayvalue(param) end.

The reason I suggested placing the empid macro into a data item is because you can't pass a macro function to a prompt macro. What you can do is create the empid macro function in the [Data Item1] and reference that in the macro prompt.

I've created a very simple report using the concept. I don't have any of the samples installed, so you'll have to switch to your package and replace the values with something you have.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="he"><!--RS:8.2-->
<modelPath>/content/package[@name='Cube']/model[@name='model']</modelPath>
<layouts>
<layout>
<reportPages>
<page class="pg" name="Page1">
<pageBody class="pb">
<contents><selectValue parameter="Level" refQuery="Query2" required="false" autoSubmit="true"><useItem refDataItem="Finish Level"/></selectValue>
<crosstab class="xt" refQuery="Query1">
<crosstabCorner class="xm"><contents><textItem><dataSource><dataItemLabel refDataItem="Forecast"/></dataSource></textItem></contents></crosstabCorner>
<style>
<CSS value="border-collapse:collapse"/>
</style>
<defaultMeasure refDataItem="Forecast"/><crosstabFactCell class="mv"><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Finish Level" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Lab" class="ml"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows></crosstab>
</contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<queries><query name="Query1"><source><model/></source><selection><dataItem name="Forecast"><expression>[Cube].[Measures].[Outputs].[Forecast]</expression></dataItem><dataItem name="Finish Level" aggregate="none"><expression>#prompt('Level','token','[Default]')#</expression></dataItem><dataItem name="Lab" aggregate="none"><expression>[Cube].[Dim].[Hier].[Level]</expression></dataItem><dataItem name="Default"><expression>#'[Cube].[Dim2].[Hier2].[Level]-&gt;:[PC].[@MEMBER].['+'Replace this with your macrofunction'+']'#</expression></dataItem></selection></query><query name="Query2">
<source>
<model/>
</source>
<selection><dataItem name="Finish Level" aggregate="none"><expression>[Cube].[Dim2].[Hier2].[Level]</expression></dataItem></selection>
</query>
</queries></report>

[Tracy]

I got distracted and never got back to this particular thread.  I do want to thank Paul for all his detailed responses.

I was able to get this to work and wanted to share my solution.  I had been hoping that there was one piece of code that would work to both filter the report and set the default in the prompt.  But I gave up on that and had to code each separately.  The filter part has already been described using something like this in the filter:
columnname = #prompt('p_promptname','type','defaultcalculation')#

This is how I got the prompt to default:

Add a Value Prompt named "o_currentmonthprompt" that uses p_promptname.

Add a Singleton to the report, which pulls the result of defaultcalculation.  In the singleton, surround the defaultcalculation with HTML Items. 
In the HTML Item before defaultcalculation, enter:  <span id="CurrentMonth">
In the HTML Item after defaultcalculation, enter:
</span><script type="text/javascript">
var promptValue;
promptValue=document.getElementById('CurrentMonth').innerText;
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined) { fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );}
var prompt = fW._oLstChoiceso_currentmonthprompt;

// prompt.value.length will be 0 on load when nothing has been chosen
// This is the only time we want to set it or it will always return to our default value
if (prompt.value.length==0)
{
   for (i = 0; i < prompt.options.length; i++)
   {
      if (prompt.options.text == promptValue)
      {
         prompt.options.selected = true;
      }
   }
}
</script>



That's it.  The value of the defaultcalculation will be shown in the singleton and be visible in the report.  I got around that by either (a) putting it someplace where it made sense or (b) setting the font color to match the background color.