I am building a dimensional report from a Transformer cube.
Scenario – I have 2 dimensions with 'State' at the level 1 on both and 'Territory_Code' as level 2 on 1st dim and 'County' as the level 2 on the 2nd dim. The users only want one prompt on the 'state' , but display 'Territory Code' level detail on one page and 'County' level detail on another page for the selected 'State'.
I created a visible value prompt (Prompt1) with 'State' values from the 1st dimension and a hidden value prompt (Prompt2) with 'State' values from the 2nd dimension. When user selects a state, say, 'Alabama' from Prompt1, the same selection should be dynamically passed to Prompt2.
I have used the following code of javascript -
<script>
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)
{
fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );
}
fW._oLstChoicesPrompt2.onChange = new Function ("fW._oLstChoicesPrompt1.selectedIndex = fW._oLstChoicesPrompt2.selectedIndex");
</script>
But this is still filtering Prompt2 on all the states instead of just 'Alabama'. Could anyone please tell me how to achieve this?
Thanks much!
Hi,
Same requirement we have done for the month and i am placing that js and convert as per your requirement.
take 2 value prompts, in your 1st prompt turn auto submit property to:yes and give Miscellanious Preperty name for your prompt name and the same name you can use in your javascript
in your 2nd prompt, it is actually should hidden if so make visible to:No and give Miscellanious Preperty name for your prompt name and the same name you can use in your javascript and Required, autosubmit set it as:No and the below the is js which we are using in report and you can convert as per your report and this js will run automatically as well as it will clear the defaults so please use whereever you required the js
<script type="text/javascript">
var form = getFormWarpRequest();
var listMonth = form._oLstChoicesMonth_Prompt;
var flag1;
var Months=new Array(12);
Months[0]="Jan";
Months[1]="Feb";
Months[2]="Mar";
Months[3]="Apr";
Months[4]="May";
Months[5]="Jun";
Months[6]="Jul";
Months[7]="Aug";
Months[8]="Sep";
Months[9]="Oct";
Months[10]="Nov";
Months[11]="Dec";
var currentMonth = new Date().getMonth();
//-------Increasing Index value for month by 1 to display previous month------
if (currentMonth==0)
{
currentMonth=11;
}
else
{
currentMonth=currentMonth-1;
}
//----Hidden Month Prompt-----
var listHiddenMonth = form._oLstChoicesMonth_Hidden;
function defaultMonthSelection()
{
for (i = 0; i < listMonth.length; i++)
{
if( listMonth.options.text == Months[currentMonth])
{
listMonth.options.selected =true;
flag1=i;
break;
}
}
}
function userMonthSelection()
{
for (i = 0; i < listMonth.length; i++)
{
if (listMonth.options.selected)
{
listMonth.options.selected =true;
break;
}
}
}
if (flag1 == undefined)
{
if (getFormWarpRequest().elements["cv.id"].value == "RS")
{
defaultMonthSelection();
listHiddenMonth.options[0].selected =true;
window.setTimeout('oCVRS.promptAction(\'finish\')',4000);
}
else
{
defaultMonthSelection();
listHiddenMonth.options[0].selected =true;
window.setTimeout('oCV_NS_.promptAction(\'finish\')', 3600);
}
}
else
{
userYearSelection();
userMonthSelection();
listHiddenMonth.options[0].selected =true;
}
</script>
Thanks,
Hi cognosbeginner!
I'm not sure you need JavaScript in such a scenario. If your members are constructed properly and have the same caption/key on both dims, you could probably solve this easily.
First let's give everything names so that we're clear. You have two dimensions, one is "State-territory" and the other is "State-County".
Now, a member unique name is usually built like this:
[cube].[dimension].[hierarchy].[level]->[dimension].[member]
Check out what a MUN looks like for you (Right click on any state member from one of the dims and select property, you'll see the MUN). If the MUN uses the same caption or key the same regions in both dims, you're good to go.
Let's assume a state MUN looks like this:
[cube].[state-territory].[state].[states]->[state-territory].[Alabama].
1. Create a query. Call it "StatePrompt". In it create a data item called "State Caption", set it up as caption(cube].[state-territory].[state].[states]) (that's a caption of state level).
2. Create a query for your sales territory details. Create the sales territory data item like this:
children(#'[cube].[state-territory].[state].[states]->[state-territory].['+prompt('pState','token')+']'#)
3. Create a query for your county details. Create the county data item like this:
children(#'[cube].[state-territory].[state].[states]->[state-county].['+prompt('pState','token')+']'#)
Now, create a value prompt using query StatePrompt, use value is the data item you created, parameter name pToken. Your selection in the prompt will feed the calculations, which will return the children of the member built with the selected state.
Good luck!
@srinu_anu -
I'm trying to modify the javascript but, no luck yet.
@ Nimrod -
you mentioned - "If the MUN uses the same caption or key the same regions in both dims, you're good to go."
If i understand what you said, i do not think that will work because that is where the problem lies. The 'State' does not use the same caption in both dims, it is different. I already tried to force the display dataitem on the 'Territory' detail page to use the prompt value of 'State' from 'State-County' dim. In that case, i see 'County' data in both pages.
So, i must have 2 state prompts, one from each dimension and then force the 'State' selection to the hidden prompt.
beginner,
Can you post a few samples of your MUN's (maybe the 'Ontario' member from each dimension). Are they identical except for the Dimension and Hierarchy names?
Here is the exmaple of mun's -
For state-Territory dim, the mun for state (level1) is - [Cube Name].[Rat Nam].[Rat Nam].[State]->:[PC].[@MEMBER].[Ontario]
For state-county dim, the mun for state (level1) is - [Cube Name].[Map Nam].[Map Nam].[StateCd]->:[PC].[@MEMBER].[01]
where, for example - '01' refers to the stateCd of Ontario.
Well, they don't match, so my 'simple' approach may not be easy.
What is the caption for the state-country dim' Ontario member? Is it Ontario?
Is one dimension political boundaries, and the other business regions? i.e. Country/State/Province for political, Sales Territories for business?