Broke my forehead on this wall already, must be missing something fairly obvious.
I have following requirement:
- user selects month from the prompt
- depending on user's selection, version prompt contents are refreshed (display values stay the same, use values change). Basically, I'm picking correct "Latest Forecast for month" version based on month. Potential caveat is that I'm picking version use values from TM1.
Base set up of version prompt cascaded from month one poses 2 problems:
- version selection is dropped to default every time you change month (that is logical, since "use value" changes when months change)
- previous "use values" are used for filtering, so until you reselect version or month 2 times, you can't be 100% sure what version you're seeing
I thought about obvious variant of static version prompt with display values (they don't change) and making another "use value" version prompt, that will leave only selected version from static prompt and using javascript to automatically submit this single version.
Question:
This works up to the point of "submitting" version, I see that correct single version is selected in "use values" version prompt, but paramValue and paramDisplayValue are empty for this prompt. If I either run the report (play button) or press Reprompt button, they show up. I can't find a way to achieve this via Javascript ( promptAction( 'reprompt' ) / /promptAction('finish'); don't work).
Any help / ideas would be appreciated.
I'm attaching a couple of screens to show what's happening:
1) I'm selecting static version of Latest Forecast and use value version prompt picks up selection properly, but promptValue and promptDisplayValue are empty
2) From the 1), I either press run or Reprompt button — promptValue and promptDisplayValue show up correctly
Javascript I'm using:
<script type="text/javascript">
function init()
{
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined) { fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );}
if (fW)
var list = fW._oLstChoicesPrompt_Scenario;
list.options[2].selected=true;
canSubmitPrompt();
}
init();
</script>
Which version of Cognos?
Can you describe the structure of the hierarchy? Maybe you can get away with doing a significantly easier and JS free way.
It sounds like Version is below month in the same hierarchy. It also sounds like each month has the same number of versions (otherwise you wouldn't be able to use static choices). Are the order of the versions the same? If so, try setting up static choices, but instead of MUNs use ascending numbers starting with 1. In the report you could do something like item(children(#prompt('Month','mun')#),#prompt('version','number')#)
Hi Paul,
Thanks for coming back so quickly.
Cognos is 10.1.1 FP1.
Unfortunately, Version is not a part of months so I can't use the MUN construction with prompts.
User always sees 3 versions (Budget, Prior Year Actuals and Latest Forecast), but this Latest Forecast version changes from month to month (Starts with annual budget, then q1 estimate, then q2 and etc). So there's a cube in TM1 that has months as a dimension and stores corresponding Latest Forecast Version as string.
I'm attaching report specification (it's just the prompts, nothing big), maybe it'll help a bit.
I would love a JS-free way, but I'm not sure it's possible.
PS: By the way, I really love your blog, thank you )
Okay, I think I'm getting it.
workflow is:
1. report loads
2. user selects Budget and refreshes the report.
3. user selects Latest Forecast Version and refreshes the report.
4. user selects another month and refreshed the report. Report is incorrect because Latest Forecast Version shows the latest version for a different month.
Version can be either Budget (static mun), Prior Year Actual (static mun), or Latest Forecast Version, which is based on a query and there's no logical way to construct the mun.
How about this:
In the Static Version prompt, set the use values to be "Budget", "Latest Forecast", and "Prior Year Actual". In the report query, drag in 3 data items, name them Budget, Latest Forecast, and Prior Year Actual. In Latest Forecast, put in the tuple expression that you're using for scenarioValue. Drag in one more data item, call that scenario, and use: #sb(prompt('pScenarioStatic','token'))#
Reference that data item as you would any other member or tuple expression.
It means a lot hearing that you like my blog. I've been following yours for years now.
Paul, you understand logic perfectly, that's exactly what I'm trying to achieve.
I can't reference tuple expression to get latest forecast in report queries themselves due to:
a) this tuple expression comes from another cube and you can't reference 2 cubes in one query subject
b) If if I add this latest forecast into every cube as string measure, this error (http://www-01.ibm.com/support/docview.wss?uid=swg1PM54643) basically means that I can't do any additional processing in report queries or it'll lead to unexpected results. Additional processing would be any caption comparison or filtering (learned it the hard way)
This is why I'm trying to use Javascript (that I generally avoid at all costs), but it just doesn't seem to work properly (or, more likely, I just don't understand what's happening, which may also be the reason I'm avoiding it).
Thanks a lot for your attention.
Difficult. So the tuple expression from cube B returns a value/string/something that the report query (cube A) translates into the correct version. Can you share how that's being done?
Okay, another idea.
Set the query with the tuple expression onto the page, include the items in the page properties.
In the report query, in the Latest Forecast Version data item, use the method you're using to find the forecast. guessing it's something like:
filter(versions,versions.caption = #prompt('Version','string',sq('Default text is needed here, even if it will never be used'))#)
On the datacontainer, use master detail to populate the parameter value with the tuple expression (not the data item, that would just cause craziness).
Yuck, avoid local functions at all costs when working against OLAP. Substrings can usually be done on the report level if needed.
QuoteDifficult. So the tuple expression from cube B returns a value/string/something that the report query (cube A) translates into the correct version. Can you share how that's being done?
I'm storing this string in pVersion prompt and then constructing MUNs, using [version_dim].[hierarchy]->[well_you_know_the_drill].@MEMBER.#sb(prompt('pVersion','string','Budget'))#
I'll try the master-detail approach, but it's not that good idea in my situation, since I have this prompt report sitting on top of MultiPage-Dashboard and passing values to all underlying tabs. Rewriting them all for local prompts will make "remembering selected prompts on previous tab" another hassle.
Keep ideas coming, you definitely have a totally different pair of eyes )
I can give you access to test server with report, if you want a look yourself (it's on the open internet, IE is all you need).
okay, next idea is a bit hacky, and it's not something I would be able to do online. I'm also not 100% sure it will work, but it's worth a shot.
In Framework Manager, create a new query. In that query, put your expression. Create a new query calculation that has a static 1. The model will not validate, since Framework can't look inside the cube. Unless this is SAP B/W, then all bets are off.
Now create a parameter map that calls those items.
Publish and test the param map in a new query:
#sq($versionLookup{'1'})#
EDIT: if this doesn't work, then lets connect.
Wow, that's something totally of the books, trying it )
Curious whether my other parameter selections (months) will be passed to this parameter mapped function dynamically.
It should work. With a few exasperating exceptions, Cognos generally executes expressions in framework exactly as if they were written in report studio. So the important thing is to make sure you always use the same parameter name.
Quote from: PaulM on 13 Feb 2013 12:27:29 AM
okay, next idea is a bit hacky, and it's not something I would be able to do online. I'm also not 100% sure it will work, but it's worth a shot.
In Framework Manager, create a new query. In that query, put your expression. Create a new query calculation that has a static 1. The model will not validate, since Framework can't look inside the cube. Unless this is SAP B/W, then all bets are off.
Now create a parameter map that calls those items.
Publish and test the param map in a new query:
#sq($versionLookup{'1'})#
EDIT: if this doesn't work, then lets connect.
Finally there's a pause (not a pause, more of a refocus back to this issue, left it to build everything else required except for this prompt) to try this out.
Using parameter map doesn't work with
"The following OLAPPlanner internal error occurred: 'OPASSERT(this) in OPMetadata::DataSource::GetDatabaseIdType() at OLAPPlannerMetadataInterface.cpp:7715'."
error. The same tuple expression works fine in a query and returns this error when encapsulated in parameter map.
I'd guess TM1 is like BW in this aspect (
Moreover, due to this error (http://www-01.ibm.com/support/docview.wss?uid=swg1PM54643) using parameter maps looks impossible at all. If I use any TM1 string and parameter map in one report query, either parameter map stops receiving key or TM1 string becomes a numeric value.
I'll be digging in this direction tomorrow, will post updates.
Cognos is being stubborn today.
So to recap:
1. Param map won't work due to funky OLAPPlanner error
2. Calculation is coming from a different database, so can't use calculation inside query
3. Master detail won't work because you're using the report in a multipage.
4. Using the cascade prompt won't work as the user would need to refresh the report twice.
How many different combinations are there for Last Version? If it's limited, maybe you can generate every single combination, stick those into a JS array, and pull the correct value when the user selects Latest Forecast Version.
I'm sure you've already looked into this, but is there a way to embed the logic into the cube itself?
Let's try to find a time to connect. NSW is nine hours ahead of me, best time would probably be early morning for me.
Hi,
I've the same issue as you for
list.options[2].selected=true;
canSubmitPrompt();
Do you find a tip to generate an Auto-submit in javascript ?
Hi RVLF,
I ended up creating a dummy prompt and pulling that one on the first page run.