CODEWITHSUNDEEP
chartscognoscognos-10cognos-bi
maverick
maverick

Reputation: 11

Creating a clickable table/list in Cognos 10.2

I have a list report being populated by prompts. However, I want to use two of the columns in the report as filters to a chart, based on which row is selected by the user. Is it possible to execute this in cognos? Any suggestions are welcome.

e.g.: List report:

State, Product, Revenue

A,a,1

A,b,2

A,c,3

B,d,4

Thus, when the user clicks on a row (say the first) then A and a should be used as filters to a chart below

Upvotes: 1

Views: 712

Answers (1)

Johnsonium
Johnsonium

Reputation: 2005

One way to do this would be to convert your second column to an HTML link. The link would fire some JavaScript that when clicked would perform the following:

  1. Use the passed in value of the second column to populate a prompt (either hidden or visible)
  2. Execute a reprompt action

The reprompt would redraw the chart using the value corresponding to the click.

To convert your data item to a link you change the expression appending HTML tags on either side of it.

If your data item was [Query1].[Item] then your converted version might be:

'<a href="#" onclick="fireJS(''' + [Presentation].[Location].[Item] + ''')">' + [Presentation].[Location].[Location Number] + '</a>'

On the list report, unlock the page and drag over an HTML item. Set the 'Source Type' property of the HTML item to 'Data Item Value' and set your newly converted data item in the 'Data Item Value' property.

A prompt is required to store the value clicked. This can just be a text prompt. The best place to put this is in a List Page Footer. You will need to specify a name property in order to work with it in JavaScript. I will call it 'textPrompt' in the code below.

You will need some JavaScript to handle the click event. The link specified fires a function called 'fireJS' and passes in the value clicked on. You will need a new HTML item in the List Page Footer to hold the JavaScript. It should appear after the prompt.

Here's the code to read the value of the click, populate the text prompt and reprompt the page.

<script>
var report = cognos.Report.getReport('_THIS_');
var textPrompt = report.prompt.getControlByName('textPrompt');

function fireJS(value) {
    textPrompt.setValues([{'use':value}]);
    report.sendRequest(cognos.Report.Action.REPROMPT);
}
</script>

If your chart filters based on the value of textPrompt, then the chart will refresh with data reflecting the choice.

Note: I advised putting the prompt and JavaScript objects in the List Page Footer. There's a good reason for this. By default Cognos pages HTML list output, showing only twenty records at a time. If you put your prompt and JavaScript below your list, they both wouldn't be rendered until you get to the last page of the output. None of the functionality would work until then. By putting these objects in the List Page Footer, we guaranteed that they are rendered on every page.

Upvotes: 1

Related Questions