Single-action reprompt+drill-through

[cogswell]

I have a report with inline prompts (checkboxes) which drills through to another report opening in a new window. Unfortunately, the drill-through action does not pass the prompt parameters of the in-line prompts unless the page is reprompted.

Is there any HTML/JS code which combines the actions of the 'reprompt' button and the drill-through hyperlink? We do not want a reprompt button anywhere on the page, and auto-submit is not possible/desired with our prompts.

We do not use JQuery or any other external library/API. Cognos 8.4.1 here.

[Joe Gilvary]

I'm not sure that I follow what you're trying to do, or what you're seeing, so please forgive me if these seem like stupid questions.

Are you certain that the drill through definition sets "Display prompt pages" to "Only when required parameters values are missing" in the source report?

Have you mapped values for every required parameter in the target report for the drill through definition in the source?

Are there any required filters in the target report for which you need to supply parameter values in the drill through definition (as opposed to having Cognos prompt for them)?

[cogswell]

Thank you for the reply! Please note my answers to your questions (not stupid at all, btw! :)) below:

I'm not sure that I follow what you're trying to do, or what you're seeing, so please forgive me if these seem like stupid questions.

Are you certain that the drill through definition sets "Display prompt pages" to "Only when required parameters values are missing" in the source report?

Yes.

Have you mapped values for every required parameter in the target report for the drill through definition in the source?

Yes.

Are there any required filters in the target report for which you need to supply parameter values in the drill through definition (as opposed to having Cognos prompt for them)?

What I am trying to do is conditionally render columns in a list in the drill-thru report, based on single-static-choice checkbox prompts on the source report. These single checkboxes are present in the list column headers in that source report, and there is a nice fat drill-thru hyperlink right below that list.

[NimrodA]

Never tried single-click repromting+drill down. something tells me this will be tricky, because a link already has an "onclick" event. I don't know if you can make a link do two things at a single click.
However, I have this trick I'm using to auto-submit multiple-choice prompts - I add an HTML item before and after the prompt. In the one posed before the prompt I write

Code Select Expand

<span onmouseout="oCVRS.promptAction('reprompt')">
and the one after the prompt simply closes the span.
Now, after the selection is made, when the mouse is moved away from the prompt, a reprompt occurs. This could be a workaround for your issue if I understood it correctly. You could just add this before and after the list in the source report.

[Joe Gilvary]

I've used render variables to select which table to show in a report, based on the presence or lack of certain parameters. The table contained the crosstab of interest, so when it doesn't render, neither does the crosstab. It was a way to let a report drill through to itself. The parameters were not required, but they drove which table appeared in the target report.

I don't think you can use a render variable for a list column, but another technique that might work is to use conditional styles. Under the Advanced Conditional Styles you can check values of parameters or data items and specify an Advanced Style setting of Box type=None. This effectively removes the list column body. You'd need to apply the same conditional style to the list column head. Sometimes it's easier to work with conditional styles and box type = None when you use the "Page Structure" view of the page.

Hope that helps. I'd be interested to read how you eventually solve this. It sounds like the kind of useful "trick" that any of us could appreciate learning.

Thanks,

Joe

[cogswell]

NimrodA, thanks for the suggestion. Unfortunately, I have 8 checkboxes (for 8 columns) and 4 other value and tree prompts (for filters). I don't want the entire page to reprompt everytime I check/uncheck a column and move my mouse. That suggestion is very handy for what you described though. I learned something new today; thanks! :)

Joe, thanks for the reply. What you described is very close to what we're doing; the drill-through target report crosstab (not list as I stated in my original post, sorry) uses Style-Variables+Box-Type to conditionally render elements.

So based on parameters passed from the source report (one 'Y' coming from each checkbox prompt in the column title of the source crosstab), conditional rendering is being performed. Now if only I didn't have to manually reprompt before clicking the drill-through link!

I had no idea that you could use Conditional Styles for conditional rendering. That is good to know. :)

[CognosPaul]

What you're asking for is possible, but requires javascript. I'll give you the basic idea of what you need to do, then in two days I'll build an example report. (I'd do it now, but it's bed time, and tomorrow I'm working at a client with no internet access).

When you use a normal drillthrough Cognos will only be able to use parameters that have already been saved. You'll need to find a way of creating the drillthrough via URL params.

Click on the properties of the target report. Then click on "View the search path, ID and URL".

The Default Action URL should look something like:

https://CognosServer/cognos8/cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2fpackage%5b%40name%3d%27Reports%27%5d%2freport%5b%40name%3d%27Report%27%5d&ui.name=Link&run.outputFormat=&run.prompt=true

Copy that for later.

Create an HTML item for your scripts.

First you'll need to define a var URL with the URL of your report.

You'll need to create JS functions that loop through your checkboxes and populates another var with a string similar to this:

Code Select Expand

&p_Param1=ABC&p_Param1=BCA&p_Param1=CAB

Then you'll need to use a JS function to pop open a new window that points to URL + ParamString. (Hint: window.open()l)

Finally, create an HTML item that is something like:

Code Select Expand

<a href="#" onclick="yourfunctions(); return false;"/code]

Did any of that make sense? If not don't worry, I'll make a sample report next time I'm at a Cognos installation with access to the internet.

[CognosPaul]

Oh wow, you can tell how tired I was when I wrote that.

As promised, here is my solution.

First. Create a target report. In this example I created a crosstab with months and product lines based on the Go Sales (query).

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/7.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Models&apos;]/package[@name=&apos;GO Sales (query)&apos;]/model[@name=&apos;model&apos;]</modelPath>
<drillBehavior/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1">
<crosstabCorner>
<contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents>
<style>
<defaultStyles>
<defaultStyle refStyle="xm"/>
</defaultStyles>
</style>
</crosstabCorner>


<style>
<CSS value="border-collapse:collapse"/>
<defaultStyles>
<defaultStyle refStyle="xt"/>
</defaultStyles>
</style>
<crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Month" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><sortList><sortItem refDataItem="Month key"/></sortList></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Quantity"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=","/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItem aggregate="none" rollupAggregate="none" name="Month"><expression>[Sales (query)].[Time].[Month (caption)]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="3"/><XMLAttribute output="no" name="RS_dataUsage" value="attribute"/></XMLAttributes></dataItem><dataItem aggregate="none" rollupAggregate="none" name="Product line"><expression>[Sales (query)].[Products].[Product line]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="3"/><XMLAttribute output="no" name="RS_dataUsage" value="attribute"/></XMLAttributes></dataItem><dataItem aggregate="total" name="Quantity"><expression>[Sales (query)].[Sales].[Quantity]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value="fact"/></XMLAttributes></dataItem><dataItem aggregate="none" rollupAggregate="none" name="Month key"><expression>[Sales (query)].[Time].[Month key]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value="identifier"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpression> [Sales (query)].[Time].[Month key] in ?Month?</filterExpression></detailFilter><detailFilter><filterExpression>[Sales (query)].[Products].[Product line code] in ?ProductLine?</filterExpression></detailFilter></detailFilters></query></queries><reportName>target</reportName></report>

Save this report somewhere. In Cognos Connection, find the URL of the report. Click on properties --> View the search path, ID and URL. The default action url is what you need. Copy it into notepad, replace everything before /cgi-bin/ with .. and change run.prompt=true to run.prompt=false.

Code Select Expand

../cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2fpackage%5b%40name%3d%27Reports%27%5d%2freport%5b%40name%3d%27Report%27%5d&ui.name=Link&run.outputFormat=&run.prompt=false

In the report your drilling from wrap each of your checkbox groups in HTML items <div id="Checkbox1"> and </div>

Create an HTML item with the following code:

Code Select Expand

<script>
var URL = '../cgi-bin/cognos.cgi?b_action=cognosViewer&ui.action=run&ui.object=%2fcontent%2fpackage%5b%40name%3d%27Reports%27%5d%2freport%5b%40name%3d%27Report%27%5d&ui.name=Link&run.outputFormat=&run.prompt=false';
var Params ='';

function inlineRadios(child,paramname)
{
   if(child.type == "checkbox" && child.checked)
   {
        Params = Params+'&p_'+paramname +'='+child.value;
   }
}

function promptDescendants (node,paramname)
{
   for (var i = 0; i < node.childNodes.length; i++)
   {
      var child = node.childNodes[i];
      inlineRadios(child,paramname);
      promptDescendants(child,paramname);
   }
}


function drillto()
{
promptDescendants (document.getElementById('Month'),'Month');
promptDescendants (document.getElementById('ProductLine'),'ProductLine');
window.open(URL+Params);

}
</script>


As you can see (besides stealing the base of this code from a previous example I made) I am using Month and Product Line checkboxes.

Finally create another HTML item wherever you want the user to click to drill:
<a href="#" onclick="drillto(); return false;">click me</a>

When the user clicks on the report it will concatenate the params with the url and attempt to open that string in a browser window.

Code Select Expand


<report xmlns="http://developer.cognos.com/schemas/report/7.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Models&apos;]/package[@name=&apos;GO Sales (query)&apos;]/model[@name=&apos;model&apos;]</modelPath>
<drillBehavior/>
<layouts>
<layout>
<reportPages>
<page name="Page1">
<style>
<defaultStyles>
<defaultStyle refStyle="pg"/>
</defaultStyles>
</style>
<pageBody>
<style>
<defaultStyles>
<defaultStyle refStyle="pb"/>
</defaultStyles>
</style>
<contents><HTMLItem description="div id=Month">
<dataSource>
<staticValue>&lt;div id=&quot;Month&quot;&gt;</staticValue>
</dataSource>
</HTMLItem><selectValue parameter="Month" refQuery="Months" multiSelect="true" required="false" selectValueUI="checkboxGroup"><useItem refDataItem="Month key"><displayItem refDataItem="Month"/></useItem><sortList><sortItem refDataItem="Month key"/></sortList></selectValue><HTMLItem description="/div">
<dataSource>
<staticValue>&lt;/div&gt;
</staticValue>
</dataSource>
</HTMLItem><HTMLItem description="div id=pl">
<dataSource>
<staticValue>&lt;div id=&quot;ProductLine&quot;&gt;</staticValue>
</dataSource>
</HTMLItem><selectValue parameter="ProductLine" refQuery="ProductLine" multiSelect="true" selectValueUI="checkboxGroup"><useItem refDataItem="Product line code"><displayItem refDataItem="Product line"/></useItem></selectValue><HTMLItem description="/div">
<dataSource>
<staticValue>&lt;/div&gt;</staticValue>
</dataSource>
</HTMLItem><HTMLItem>
<dataSource>
<staticValue>&lt;script&gt;
var URL = &apos;../cgi-bin/cognos.cgi?b_action=cognosViewer&amp;ui.action=run&amp;ui.object=%2fcontent%2fpackage%5b%40name%3d%27Reports%27%5d%2freport%5b%40name%3d%27Report%27%5d&amp;ui.name=Link&amp;run.outputFormat=&amp;run.prompt=false&apos;;
var Params =&apos;&apos;;

function inlineRadios(child,paramname)
{
   if(child.type == &quot;checkbox&quot; &amp;&amp; child.checked)
   {
        Params = Params+&apos;&amp;p_&apos;+paramname +&apos;=&apos;+child.value;
   }
}

function promptDescendants (node,paramname)
{
   for (var i = 0; i &lt; node.childNodes.length; i++)
   {
      var child = node.childNodes[i];
      inlineRadios(child,paramname);
      promptDescendants(child,paramname);
   }
}


function drillto()
{
promptDescendants (document.getElementById(&apos;Month&apos;),&apos;Month&apos;);
promptDescendants (document.getElementById(&apos;ProductLine&apos;),&apos;ProductLine&apos;);
window.open(URL+Params);

}
&lt;/script&gt;

&lt;a href=&quot;#&quot; onclick=&quot;drillto(); return false;&quot;&gt;click me&lt;/a&gt;</staticValue>
</dataSource>
</HTMLItem></contents>
</pageBody>
</page>
</reportPages>
</layout>
</layouts>
<XMLAttributes><XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=","/></XMLAttributes><queries><query name="Months"><source><model/></source><selection><dataItem aggregate="none" name="Month key"><expression>[Sales (query)].[Time].[Month key]</expression></dataItem><dataItem aggregate="none" sort="ascending" name="Month"><expression>[Sales (query)].[Time].[Month (caption)]</expression></dataItem></selection></query><query name="ProductLine"><source><model/></source><selection><dataItem aggregate="none" name="Product line code"><expression>[Sales (query)].[Products].[Product line code]</expression></dataItem><dataItem aggregate="none" sort="ascending" name="Product line"><expression>[Sales (query)].[Products].[Product line]</expression></dataItem></selection></query></queries><reportName>Drill From</reportName></report>

[dferrara]

HI You have presented an excellent solution to what  I am working on which is using a textbox like a search.  I applied the code <span onmouseout="oCVRS.promptAction('reprompt')"> in an html object before the text box with a closing tag after. and this worked great when I rand it from RS. However when I ran it from the portal it crashed saying that ocvrs was not defined. I did find this article from IBM saying it is not the correct object to reference. http://www-01.ibm.com/support/docview.wss?uid=swg21343290.

Have you already encountered this?

[CognosPaul]

The RS portion of oCVRS.promptAction indicates that it's an output from Report Studio. In the portal it's oCV_NS_ and I think there are a few more permutations depending on where you're running it.

There is a standard way of getting around that.

Fist, add this to the script tag before anything else:

Code Select Expand

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;


Now you can do something like window[nameSpace].promptAction(...)

[zzeuss]

Hi CognosPaul,

Your script to pass checkbox values to a drill through report is exactly what I've been looking..
Would you be able to alter your script so I can use text box prompt instead of check box?
I need to pass string(entered in text box) to drill through report..!!

Thank you!!!

[CognosPaul]

Which version of Cognos are you using? It's a fairly easy adaptation, but the JavaScript to do it has changed recently.

If you're on 10.1 or below, you could do something like:

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;

window.open(URL+fW._txtEditBoxPromptName.value);

[zzeuss]

Thank you CognosPaul.. :)

I'll try and will let you know!!

and yes.. we are on 10.2..

[ksiv6]

Hi Cognospaul,

Im looking for something similar to what you have mentioned in this topic.

We have a promptpage report with few single select list box prompts and a couple of multiselect listbox prompts.
The single selects are autosubmit(no issues here) and each of the multiselect have a reprompt button, so the user has to hit the reprompt everytime they select/deselect anything in the multichoice prompt before hitting submit.The end goal of course is to drill through to another report and display it in a seperate page for all the selected prompts.

The issue is that the users dont like the reprompt button for the multiselect prompts, so we are looking for a way to rewrite the prompt page without the reprompt button.

So now going by what you have mentioned in this topic,is there a way to modify your code to use list box instead of checkbox and also include the comination of single select and multiselect prompts(right now the code points to only multiselec checkbox).

let me know,
Thanks for your help,
Ksiv..

[CognosPaul]

Ksiv, if you're on 10.2 or greater, take a look at this post: http://cognospaul.com/2014/03/20/passing-parameters-without-refreshing-page/

[ksiv6]

Thanks CognoPaul. We are on 10.1 but I saw a comment in that page saying it works for 10.1 too. So I'll try it and let you know. Thanks again.

[CognosPaul]

I'm afraid this method won't work well in 10.1. It relies on the prompt api to get all of the prompt controls and fetch the selected values. Without it the prompt api, you'll need to reference each prompt individually. Part of the issue is that each prompt type needs to have customized code to retrieve and transform the selected values.