[SOLVED] Filter cascading prompts

[TheFrenchGuy]

Hi,

I want to use the filter function in order to "filter" my use value prompts.
I have 3 multi select prompts with 2 of them are cascading :

Code Select Expand

Country                   Region                     Service centre
           =>cascading           =>cascading

My issue is :
if the country 'UK' is selected I don't want 'Blank' Region appear. But only for 'UK' !

My 3 data items prompts are in only one query.
I tryed for use value data item region :

Code Select Expand

case
when caption(children([Daily Cube].[Location].[Location (by country)].[Europe]->:[PC].[@MEMBER].[EU])) =('UK')
then (filter(descendants([Daily Cube].[Location].[Location (by country)].[Europe]->:[PC].[@MEMBER].[EU];2),caption(descendants([Daily Cube].[Location].[Location (by country)].[Europe]->:[PC].[@MEMBER].[EU];2)) not in ('( Blank )')))
else (descendants([Daily Cube].[Location].[Location (by country)].[Europe]->:[PC].[@MEMBER].[EU];2))
end

but this function hide '( Blank )' region for all the countries.

Can you help me pls ?

[TheFrenchGuy]

Hi folks,

I've also tried to filter only for the UK, but I got same results :

Code Select Expand

case
when caption(children([Daily Cube].[Location].[Location (by country)].[Europe]->:[PC].[@MEMBER].[EU])) =('UK')
then (filter(descendants([Daily Cube].[Location].[Location (by country)].[Europe]->:[PC].[@MEMBER].[EU];2),caption(children([Daily Cube].[Location].[Location (by country)].[Country]->:[PC].[@MEMBER].[UK])) not in ('( Blank )')))
else (descendants([Daily Cube].[Location].[Location (by country)].[Europe]->:[PC].[@MEMBER].[EU];2))
end

All I want is to exclude the 'Blank' named region in my prompt region but only for the UK ...

[TheFrenchGuy]

Hey,

I decided to make something less automatic : I've create 12 queries for my 12 countries.
In each ones I've put :
_Country
_Regions
_Service Centres

Then I can filter directly a query depending what I'm waiting for. In my case, I've filtered the query 'UK' with, in the region data item, the function :

Code Select Expand

filter(children([Country]),caption(children([Country])) not in ('( Blank )'))
in order to exclude '( Blank )' region from UK's regions.

After, I've unioned countries (11 unions ....) but at the end I get 1 query with all I want.

I've successfully implemented this and when I run the report and select the last level (service centre), I get this error :

RSV-SRV-0040

      The report server encountered an internal error. Check additional information associated with this error message. If cause of problem cannot be ascertained, increase the logging level in the IBM Cognos administration tool and reproduce the conditions that caused the error. If the problem persists, see the problem determination information on the IBM Cognos Support Portal page at http://www-947.ibm.com/support/entry/portal/Overview/Software/Information_Management/Cognos_Business_Intelligence.

Any help appreciated

[navissar]

I wouldn't use unions like that over dimensional, it's a strictly relational thing. I assume you're over dimensional since you're using the filter function.
This is a classic case for a wee bit of javascripting. I could give you the script, but where's the learning in that? If you're using 10.2 and up, you have the prompt API available. Here is the documentation and it includes code samples https://www-304.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.2.doc/c_prmpt_api_overview.html%23prompt_api_overview.  What you want to do is add a validator for you country prompt that removes blank from your region prompt if current selected value is UK. Try to do that, and if you get stuck, ping us back for help.

[TheFrenchGuy]

Hello Nimrod,

ty for reply.
For the union tool, I've just try my best to reach my purpose.
Btw I have no skill in javascript ... but I'm sure it's not complicated to implement and to update.

You get the point Nimrod :

What you want to do is add a validator for you country prompt that removes blank from your region prompt if current selected value is UK

[navissar]

Right, listen, forget everything I wrote. I'm an idiot, I forgot my own solution.
Use this: http://www.ibm.com/developerworks/library/ba-pp-reporting-scripting_techniques-page673/
When you set up the list query, make sure to filter out "blank" for the UK and you should be good to go, since the script takes the values from the list. Cascade will work really fast, and you wouldn't have to write a single line of code.

[TheFrenchGuy]

Hi

Thx Nimrod for your reply.
Btw, I'm lost trying this solution : I'm unable to do that in dimensional.
When I try the stuff, I get 3 prompts but not cascading.
I'm not using the "display value" tool cause I've no code in data item, I've just :
_Countries
_Regions
_Service centres

and not
_Countries
_Country codes
_Regions
_Region codes
_Service centres
_Service centre codes

So I have difficulties to implement your solution Nimrod.

[navissar]

just use each column twice.

[TheFrenchGuy]

Hello Nimrod and thx for your support.

I'm always in trouble trying your cascading prompts to work.
Certainly something I'm doing bad :
_I've create 6 data items and according to you, I've duplicated them twice and I've renamed them following you example (Countries line, Countries line code => same data item)
_I've set the prompts up


with Display value, Parameter and Name with same value and Use value with the twin data item.

Pls let me know if you see anything wrong

[navissar]

use select lists rather than checkboxes.

[barrysaab]

How did you go,TheFrenchGuy. :)

[TheFrenchGuy]

Hi all,

@Nimrod : I've changed checkbox to list, without any improvement in the cascading

@barrysaab : I did wrong, so it's the purpose : How to do ^^

I thought there was easier solutions to limit use value prompts based on Cube ... What a mistake

[navissar]

Upload your report's specs, I'll take a look.

[TheFrenchGuy]

Pls, find enclosed,

tx

[navissar]

1. Who told you to hide the list?! Unhide the list.
2. Replace the script with this here below. IBM broke one of the lines on their site.

Code Select Expand

<script>
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() :
document.forms["formWarpRequest"]);
if ( !fW || fW == undefined)   
{
fW = ( formWarpRequest_THIS_ ? formWarpRequest_THIS_ : formWarpRequest_NS_ );
}
var isDisableOn=true
//change this to false if you don't wish grandchild or younger prompts to be disabled
/*Section 1: initialize cascade:
this section handles grabbing the block with the list(s) in it, verifying the lists were
constructed as necesary, and adding an onchange functionality calling the cascade
function in the second section to each relevant prompt directing it at the correct list.
*/
/*get the block:
To simplify matters and make the whole thing operate from a single HTML Item, the block
is captured by a Cognos generated LID.
*/
var allDivs=document.getElementsByTagName("div");
var theDiv;
for(var allDivsIndex=0;allDivsIndex<allDivs.length;allDivsIndex++){
if(allDivs[allDivsIndex].LID&&allDivs[allDivsIndex].LID.indexOf("cascadeLists")==0){
theDiv=allDivs[allDivsIndex];
theDiv.style.display="none";//hide it from the world
break;
}

}

/*get the list(s)
The solution supports multiple cascade instances, so there can be more than one list.
Here is where we grab them
*/
var allLists=theDiv.getElementsByTagName("table");
var theLists=new Array();
for(var allListsIndex=0;allListsIndex<allLists.length;allListsIndex++)
{
if(allLists[allListsIndex].className&&allLists[allListsIndex].className=='ls')
{
theLists.push(allLists[allListsIndex]);
}

}
var numberOfCascades=theLists.length;

/*verify the lists -
there are three verifications made:
1. The list has an even number of columns (use, display)
2. The header of every pair is the same
3. There are prompts on page with the same name as said header for all three.
If a verification step fails, an alert is thrown to alert the user/developer
*/
function verifyTheLists(){
for(var theListsIndex=0;theListsIndex<numberOfCascades;theListsIndex++){
var theList=theLists[theListsIndex];
var isListValid=true;
var allRows=theList.getElementsByTagName("tr");
var titleRow=allRows[0];
var allHeaders=titleRow.getElementsByTagName("td");
//verification number 1
if(allHeaders.length%2!=0){
alert("Each prompt should have a list column with use and display value");
isListValid=false;
}
//verification number 2+3
for(var allheadersIndex=0;allheadersIndex<allHeaders.length-1;allheadersIndex++){
if(allheadersIndex%2==0){//even columns
if(allHeaders[allheadersIndex].innerText!=
allHeaders[allheadersIndex+1].innerText){
isListValid=false;
alert("each pair needs an identical label");
break;
}
else{

if(!fW["_oLstChoices"+allHeaders[allheadersIndex].innerText]){

alert("There is no corresponding prompt for"+allHeaders[allheadersIndex].innerText);
isListValid=false;
break;
}
}

}
else{//odd column
if(allHeaders[allheadersIndex].innerText==
allHeaders[allheadersIndex+1].innerText){
isListValid=false;
alert("each pair needs an identical label in the list. ");
break;
}
}
}

if(!isListValid){break;}
}
return isListValid;
}
/*the Initialize sequence is quite simple:
We go over each list, verify it, and then add the corresponding prompt from each header
an onchange function
*/
function initPromptsCascade(){
if(verifyTheLists()){
for(var theListsIndex=0;theListsIndex<numberOfCascades;theListsIndex++){
var theList=theLists[theListsIndex];
var theID=theList.LID;
var allRows=theList.getElementsByTagName("tr");
var titleRow=allRows[0];
var allHeaders=titleRow.getElementsByTagName("td");
for(var titleIndex=0;titleIndex<allHeaders.length;titleIndex+=2){
//remove select/deselect all links because they don't work with the cascade
var thePrLinks=fW["_oLstChoices"+allHeaders[titleIndex].innerText].
parentNode.parentNode.getElementsByTagName("a");
for(var linksI=0;linksI<thePrLinks.length;linksI++){
thePrLinks[linksI].style.display="none";
}

if(titleIndex<allHeaders.length-2){
  fW["_oLstChoices"+allHeaders[titleIndex].innerText].ListID=theID;
  fW["_oLstChoices"+allHeaders[titleIndex].innerText].isDisableOn=isDisableOn;
//do we want to disable grandchildren?
  fW["_oLstChoices"+allHeaders[titleIndex].innerText].location=titleIndex;
  fW["_oLstChoices"+allHeaders[titleIndex].innerText].onchange=function()
{cascadePrompts(this.ListID,this.location,this.isDisableOn)};
}
//disable 3rd generation onwards
if(titleIndex>0&&isDisableOn){
fW["_oLstChoices"+allHeaders[titleIndex].innerText].disabled=true;

}
}

}
}
}
/*section 2: perform cascade:
This is where the magic happens. When a user selected a value in a prompt, we:
1. create an array of the selected values.
2. clear all descendant prompts
3. go to the list at the prompt's location
4. search through the list for the selected values
5. if the value on the list row matches the selected value, we add the values of the
descendants to the descendants prompts.
6. if the flag is on, disable 3rd generation descendants or younger
*/
function getSelectedValues(promptName){
//This could be made MUCH simpler if using the new 10.2+ prompts API
var pr=fW["_oLstChoices"+promptName];
var retArr=new Array();
for(var prI=0;prI<pr.length;prI++){
if(pr[prI].selected==true){
retArr.push(pr[prI].dv)
//we compare based on display value, just in case we didn't use the correct code
}

}
return(retArr);

}
function clearDescendants(header,location){
for(var childrenI=location+2;childrenI<header.length;childrenI+=2){
fW["_oLstChoices"+header[childrenI].innerText].length=0;

}

}
function checkUnique(pr,display){
//we have to do it this way in case the same child is in two different parents
var isUnique=true;
for(var prI=0;prI<pr.length;prI++){
if(pr[prI].dv==display){
isUnique=false;
break;
}

}
return isUnique;
}
function addValueToPrompt(promptName,display,use){
var pr=fW["_oLstChoices"+promptName];
var isUnique=true;
//check for uniqueness
if(pr.length>0){//not empty

isUnique=checkUnique(pr,display);
}

if(isUnique){
var opt=document.createElement('option');
opt.text=opt.dv=display;
opt.value=use;
pr.add(opt);

}
}
function cascadePrompts(list,location,isDisable){
for(var listsI=0;listsI<theLists.length;listsI++){
if(theLists[listsI].LID==list){
var listEl=theLists[listsI];
break;
}
}
var rows=listEl.getElementsByTagName("tr");
var headerRow=rows[0];
var headerRowCells=headerRow.getElementsByTagName("td");
var currentPrompt=headerRowCells[location].innerText;
//clear children prompts
clearDescendants(headerRowCells,location);
var theSelections=getSelectedValues(currentPrompt);
var isEmpty=theSelections.length==0;

//run through the list
for(var rowsI=1;rowsI<rows.length;rowsI++){
var cells=rows[rowsI].getElementsByTagName("td");
for(var selectionsI=isEmpty?-1:0;selectionsI<theSelections.length;selectionsI++){
//run through selected Values
if(cells[location].innerText==theSelections[selectionsI]||isEmpty){

//the value on the cascading prompt matches!
for(var cellsI=location+2;cellsI<cells.length;cellsI+=2)
{
var thePr=headerRowCells[cellsI].innerText;
var dispVal=cells[cellsI].innerText;
var useVal=cells[cellsI+1].innerText;
addValueToPrompt(thePr,dispVal,useVal);
}

}
}
}
//disable 3rd generation onwards
if(isDisable){
if(!isEmpty){//enable son
fW["_oLstChoices"+headerRowCells[location+2].innerText].disabled=false;
//disable grandchild and onwards
for(var disableI=location+4;disableI<headerRowCells.length;disableI+=2){
fW["_oLstChoices"+headerRowCells[disableI].innerText].disabled=true;

}

}
else{
fW["_oLstChoices"+headerRowCells[location].innerText].disabled=false;
//disable grandchild and onwards
for(var disableI=location+2;disableI<headerRowCells.length;disableI+=2){
fW["_oLstChoices"+headerRowCells[disableI].innerText].disabled=true;

}



}
}

}

initPromptsCascade();

</script>

[TheFrenchGuy]

Nimrod,

I hide the table cause the prompt run too slowly with the table unhide : service centres are about 5 000, so 5 000 to show .... :s

ps : Ok, the cascading prompt running now. Thank you Nimrod. Now trying to modify data item in order to filter, for the UK, Blanck regions. Ta

[navissar]

yeah, the script takes care of hiding the list, it cannot access it if you hide it.

[TheFrenchGuy]

Hi all,

I passed this issue by use "except" function and "filter" one.

Thank you for your help