CALL US NOW:

1-401-727-2400 U.S.
1-905-532-0440 Canada

How to Implement Custom Search and Select Prompt

By Robert Edis, Senior BI Consultant

Business Problem:

Users require a special multi-select prompt control using a filtered query. They need to add/remove choices, and reorder choices in the control. This is useful when the possible choices are very many. They need:

  1. A multi-select list box to display a filtered list of values. This is populated from a query that is filter by user choices in prior prompt controls.
  2. From the list they need to add choices to a final list of choices to display in the report.
  3. They can add choices to or remove them from the final list.
  4. They can reorder the choices in the final list.
  5. They can change the original filter prompts on the same page to get a different list from which they can append to the final list.

Solution:

This solution uses a prompt page with all prompt controls on the same page. There is significant use of JavaScript to manipulate the prompt controls. A count of the choices in the final list is displayed. This technique has been tested with Internet Explorer 9 only. It uses the Go Sales (Query) package.

 

searchprompt_fig01
Figure 1 – Prompt Page at run time
searchprompt_fig02
Figure 2 – Filtered report results

 

 

  1. Create a new list report. In the list add: Region, Retailer Country, Retailer Site (from Retailers), Product Line, Product Type, Product (from Products), Revenue, Gross Profit, and Quantity measures.
  2. Add a prompt page. Open it for editing.
  3. Add a table (3 rows, 3 cols).
    1. Row 1, col 1, add a text item to be a label for the first prompt control. Text is “Product Line”.
    2. Row 1, col 2, add a text item to be a label for the second prompt control. Text is “Product Type”.
    3. Row 1, col 3 is a spacer.
    4. Row 2, col 1, add a value prompt. Parameter and name are both “pProdLine”. Don’t create a parameterized filter. Create a new query named “qProdLine”. Use Product Line Code, Display Product Line. Set Multi-select property to “Yes” and List Box. Set width to fit options.
    5. Row 2, col 2, add a value prompt. Parameter and name are both “pProdTyp”. Don’t create a parameterized filter. Create a new query named “qProdTyp”. Use Product Type Code, Display Product Type. Set Multi-select property to “Yes” and List Box. Set width to fit options.
    6. Row 2, col3 is a spacer.
    7. Row 3, cols 1 and 2 are spacers.
    8. Row 3, col 3, add an HTML item. Description “Refresh filters”. Note that I am using an image instead of a button label. Left justify the cell. Code <button onclick=”promptAction(‘reprompt’)” style=”padding: 3px; font-weight: bold; font-size: 10pt; font-family: Arial, Serif; background-color:silver; color:blue; border: 1px solid silver;”><img src=”../samples/images/blue_arrow_right.gif” /></button>
  4. Add another table (2 rows, 4 cols) below the first and name it “tblSrchSlctPrmt”.
    1. Row 1, col 1, add a text item to be a label for the filter list prompt control. Text “Available Products”.
    2. Row 1, col 3, add a text item to be a label for the final list prompt control. Text “Selected Products”.
    3. Row 1, col 4, add a text box prompt (displays counter). Parameter is “pSlctCnt”. Control name is the same. Click Finish to bypass parameterized filter and query dialog. Required: No, Multi-select: No, Hide Adornments: Yes. Name: “pSlctCnt”.
    4. Row 2, col 1, add a value prompt. Parameter and name are both “pAvailable_Products”. Don’t create a parameterized filter. Create new query name “pProducts”. Use Product Number, Display Product. Set Multi-select to “Yes” and List Box. Sort on Product. Set width to fit options.
    5. Right click on the prompt control and Go To Query. Add an optional Detail Filter as ([Sales (query)].[Products].[Product line code] IN (?pProdLine?)) OR ([Sales (query)].[Products].[Product type code] IN (?pProdTyp?)). Return to edit the prompt page.
    6. Row 2, col 2, add a table (2 rows, 1 col) named “tblBtnAddRmv”. Set cell properties to horizontal align as Middle, vertical align as Center.
      1. Row 1, add an HTML item. Description is “Add fields”. Code <button onclick=”iScripts.selectColumns.selectField(‘_Products’)” style=”background-color:white”> <img src=”../ps/portal/images/move_right.gif” /></button>
      2. Row 2, add an HTML item. Description is “Remove fields”. Code
    7. Row 2, col 3, add a value prompt. Parameter and name are both “pSelected_Products”. Click Finish to bypass both the parameterized filter and query dialogs. Set as optional and multi-select list box. Set width to fit options.
    8. Row 2, col 4, add a table (4 rows, 1 col) named “tblBtnMv”. Set cell properties to horizontal align as left, vertical align as middle. Add an HTML item to each row.
      1. Description “Top”. Code <button onclick=”iScripts.selectColumns.moveToTop(‘_Products’)” style=”background-color:white”><img src=”../skins/modern/viewer/images/top.gif”></button>.
      2. Description “Up”. Code <button onclick=”iScripts.selectColumns.moveUp(‘_Products’)” style=”background-color:white”><img src=”../skins/modern/viewer/images/pageup.gif”></button>.
  • Description “Down”. Code <button onclick=”iScripts.selectColumns.moveDown(‘_Products’)” style=”background-color:white”><img src=”../skins/modern/viewer/images/pagedown.gif”></button>.
  1. Description “Bottom”. Code <button onclick=”iScripts.selectColumns.moveToBottom(‘_Products’)” style=”background-color:white” ><img src=”../skins/modern/viewer/images/bottom.gif”></button>.
  1. Add a margin of 20 to the top of the tblSrchSlctPrmt table to provide a separation space from the first table.
  1. Add a text box prompt control after the table tblSrchSlctPrmt. Parameter and name are both “pMergeSelected_Products”. Required: No, Multi-select: No, Visible: No. This is used to manipulate the selected values in the final list. This is needed if the values in the Selected Products control are used to hide/show columns.
  2. Add an HTML item. Description: “Reselect”. Code <script>iScripts.selectColumns.reselectFields(‘_Products’)</script>.
  3. Add a final HTML item to hold the JavaScript logic. See text box. Note box is smaller than content. Select all in box, copy and paste into an editor. Note that the page has to finish loading before the functions here can be referenced.
<script language="javascript">// <![CDATA[
/***** Create and initialise global variables */
var fW = (typeof getFormWarpRequest == "function" ? getFormWarpRequest() : document.forms["formWarpRequest"]);
if (!fW || fW == undefined) { fW = (formWarpRequest_THIS_ ? formWarpRequest_THIS_:formWarpRequest_NS_); }
var preFix = "";
if (fW.elements["cv.id"]) { preFix = fW.elements["cv.id"].value;}
var nameSpace = "oCV" + preFix;
var displayStyle = "";
var noDisplayStyle = "none";
var iScripts = {}; // Create a namespace for the functions

iScripts.isInArray = function(value, array) { for(var j = 0; j < array.length; ++j) { if(array[j] === value) {return true;} } return false; }

iScripts.selectColumns = {}; // Fill the Available<_name> prompt with fields from the package.

iScripts.selectColumns.selectField = function(name)
{
 var available = fW['_oLstChoicesAvailable' + name];
 var selected = fW['_oLstChoicesSelected'+name];
 var selectedCount = 0;
 var maxCount = 100;
 var totalCount = selected.length;
 for(var i = 0; i < available.length; ++i) { if(available.options[i].selected) ++selectedCount; }
 totalCount += selectedCount; // Add the current measure selection count to the total
 for(var i=0; i<selectedCount; ++i)
 {
  if (totalCount <= maxCount) { selected.appendChild(available.options[available.options.selectedIndex]); }
  else
  {
   alert('*W* Maximum number of items exceeded - Please reduce your selection of ' + totalCount  + ' to 100 or less.');
   totalCount -= selectedCount; // Subtract the current measure selection count from the total
   break;
  }
 }
 /***** Update the selected measure total count displayed on prompt page */
 var mCnt = fW._textEditBoxpSlctCnt;
 mCnt.value = totalCount;
}

iScripts.selectColumns.removeField = function(name)
{
 var available = fW['_oLstChoicesAvailable' + name];
 var selected = fW['_oLstChoicesSelected' + name];
 var selectedCount = 0;
 for(var i = 0; i < selected.length; ++i) { if(selected.options[i].selected) ++selectedCount; }
 for(var i = 0; i < selectedCount; ++i) { available.appendChild(selected.options[selected.options.selectedIndex]); }
 // Update the selected measure total count displayed on prompt page
 var mCnt = fW._textEditBoxpSlctCnt;
 mCnt.value = selected.length;
}

/***** Before modifying the use values, join the values into the merged textbox. This allows us to reselect those fields, in that order, on page load. */
iScripts.selectColumns.mergeFields = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 var merged = fW['_textEditBoxMerged' + name];
 var mergedValue = []; // Set up an array to hold values
 for(var i = 0; i < selected.length; ++i) { mergedValue.push(selected.options[i].value); }
 merged.value = mergedValue.join('||');
}

/***** On page reload, select the fields that appear in the merged textbox */
iScripts.selectColumns.reselectFields = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 var merged = fW['_textEditBoxMerged' + name];
 var available = fW['_oLstChoicesAvailable' + name];
 var mergedArr = merged.value.split('||');
 for(var i = 0; i < mergedArr.length; ++i) { for(var j = 0; j < available.length; ++j) { if(available.options[j].value === mergedArr[i]){selected.appendChild(available.options[j])} } }
}

iScripts.selectColumns.moveToTop = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 var j = 0;
 for(var i = 0; i < selected.length; ++i) { if(selected.options[i].selected) {selected.insertBefore(selected.options[i],selected.options[0+j]); ++j} }
 selected.style.display = noDisplayStyle;
 selected.style.display = displayStyle;
}

iScripts.selectColumns.moveUp = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 for(var i = 1; i < selected.length; ++i) { if(selected.options[i].selected &#038;! selected.options[i-1].selected) { selected.insertBefore(selected.options[i],selected.options[i-1]); } }  selected.style.display = noDisplayStyle;  selected.style.display = displayStyle; } iScripts.selectColumns.moveDown = function(name) {  var selected = fW['_oLstChoicesSelected' + name];  for(var i = selected.length-2; i >= 0; --i) { if(selected.options[i].selected &#038;! selected.options[i+1].selected) { selected.insertBefore(selected.options[i],selected.options[i+2]); } }
 selected.style.display = noDisplayStyle;
 selected.style.display = displayStyle;
}

iScripts.selectColumns.moveToBottom = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 var j = 0;
 for(var i = 0; i < selected.length; ++i) { if(selected.options[i-j].selected) { selected.appendChild(selected.options[i-j]); ++j; } }
 selected.style.display = noDisplayStyle;
 selected.style.display = displayStyle;
}

/* After the fields have been merged to the text box, it is time to prepare them. The
 * report will search for the fields by looking for a number surrounded by pipes: |1| or |2|
 * This function will loop through and modify the use value to incorporate the pipes.
 */
iScripts.selectColumns.prepUseValues = function(name)
{
 var selected = fW['_oLstChoicesSelected' + name];
 for(var i = 0; i < selected.length; ++i)   
 {   selected.options[i].value += '|'+i+'|';   selected.options[i].selected = true;  } 
} // ]]>

 

  1. Edit the query Query1. Add a filter as [Sales (query)].[Products].[Product number] IN (?Selected_Products?).
  2. Test the results by running the report.

At this point your report should look something like the below.

searchprompt_fig03     searchprompt_fig04
Figure 3 – Prompt page layout     Figure 4 – List of queries

 

searchprompt_fig05

Figure 5 – Query1 definition with filter

 

searchprompt_fig06

Figure 6 – qProducts definition with optional filter

   
Figure 7 – qProdLine      Figure 8 – qProdTyp

 

 

 

 

 

The example above is designed to filter report rows (products). This technique can also be used to filter columns. This is described in “How to Include Dynamic Measure Columns Using a Custom S&S Prompt in Report Studio”.

 

 

Click here for Report Studio report specifications for this report

 
 
 
 
 

Contact Us

E-mail: cci.info@lighthousecs.com

USA
Phone: (401) 727-2400
Fax: (401) 727-4998

Canada
Phone: (905) 532-0440
Fax: (905) 532-0683