the requirement is to display prompt and parameterized data item on same page, so I have a data item with the following expression [Product].[Brand].[By Brand]->?Brand? in the report layout, and a value prompt with a separate query to pass the parameter value. (parameter in value prompt is Brand) no matter what I do, a separate page comes up for the data item from report layout asking for a value first. Note this works just fine with a prompt and report page, but trying to set up as a slick dashboard.. Thoughts ?
It's a bit of a chicken-and-egg story here. Your data item has an embedded parameter, which needs to be resolved before the page can be rendered. You are also placing the prompt to resolve this on the same page, which cannot be rendered until the parameter is resolved...
This explains your current observed behaviour. Question - in terms of your design intent for the report, how should the item be resolved if there is no parameter value provided - would you expect to see an unfiltered result, or an empty result, or... ?
One possibility is to change the report to use a detail filter instead of the calculated item expression, then set the filter to be optional. This will allow the page to render with an unfiltered report initially.
Regards,
MF.
I disagree, detail filters should be used sparingly (if ever) in dimensional reports. Instead of [Product].[Brand].[By Brand]->?Brand? try using #prompt('Brand','memberuniquename','[Product].[Brand].[By Brand].[Brand]','','[Product].[Brand].[By Brand].[Brand]')# This will default to the Brand level if no value is returned or the selected value.
Depends whether is is a DMR or pure OLAP source. I do agree though, detail filters should be used with extreme caution.
Thanks for the idea of the prompt macro - top tip! :)
MF.
That's fair. DMR is strange and unknown territory to me. Since it's really relational pretending to be OLAP a detail filter would probably be more efficient.
Paul,
Thank you for the macro prompt idea. It worked flawlessly with a value prompt on the same page as crosstab, I used the follow code:
#prompt('Month_Yr','memberuniquename','[PBrand].[Time].[Calendar Years by Month].[Month]->:[PC].[@MEMBER].[20030301-20030331]','','[PBrand].[Time].[Calendar Years by Month].[Month]')#
The nice thing is the macro prompt only has to be implemented once and it picks up all the YTD, LYTD parameterized data items just like the olap filter thing
Also I noticed that a tree prompt behaves different then value prompt when trying to implement on same page.
Settled on a Cognos Viewer Portlet to Portlet communication via channel as a final solution for dashboard.
Hi Paul,
Can you please let me know if I can use DMR function for the default expression which you have mentioned i.e #prompt('Brand','memberuniquename','[Product].[Brand].[By Brand].[Brand]','','[Product].[Brand].[By Brand].[Brand]')#
e.g I am having a data item as A1 as lastPeriods(2,[Fuel Savings (Analysis)].[Time (Arrival)].[TIME (ARRIVAL)].[MONTH]->?p_month?)
Even I am receiving a separate page asking value for month. Can I replace the second parameter with the prompt macro expression syntax as mentioned above for default value.
If not what alternative can be used so that I can apply the DMR function as well as avoid an extra prompt.
Thanks!
Aarti
The prompt macro will return a member, so you can use that anywhere that accepts members as a parameter.
lastPeriods(2,#prompt('p_month','mun','[DefaultMonthMember])#)
If the user selects a value for p_month, then it will be returned, otherwise it will use the value in the DefaultMonthMember data item.
Thanks for your response Paul...It works... :) :)