Transcript Enter your project title here - Northwestern University Information

Northwestern University
Business Intelligence Solutions
Cognos 8 Best Practices
Volume 3
Advanced Developer Techniques
Agenda

JavaScript Prompt Limiter
– How to use JavaScript to limit the number of elements a
user can select in a multi-select prompt.

Report Cache
– Used to cache prompts for a report to optimize the
prompt page response.

Burst Reports
– Use distribution lists to distribute reports.

Join/Union/Intersect/Except/Master-Detail
– How to join data from multiple queries.
Section 1
JAVASCRIPT PROMPT LIMITER
Report Prompt Behavior
Users can overload the selection
easily by using the Select all button.
Multi-Select Prompt Properties
In the properties pane for a
multi-select prompt, you
can set the number of rows
to be displayed per page in
the list, but there is no
option to limit how many
selections a user can
make.
Why Limit User Selection




Oracle only allows 1,000 items to be used in the IN
statement of a SQL query.
Large amount of values in a prompt can cause the
report performance issues.
Large values in a prompt can return a report too
large to supply good Business Intelligence.
Allow the users access to choose from all the
possible dimensional members, even if they can’t
choose all of them at one time.
Add JavaScript to Prompt Page




In Cognos 8.4, IBM Cognos changed the names of
items making older JavaScript not run correctly.
The new way to call items now should be future
proof since the prompt is defined in a DIV tag and
identified with the getElementByID function.
JavaScript should be used minimally in reports as a
best practice, but sometimes the only way to force
client behavior.
The cost/benefit balance in this case lies distinctly
in the use of JavaScript.
How to Build Prompt Page
<div id="selectValues">
</div>
<script …
Put three HTML items on the page, one before the prompt, one
right after the prompt, and one to replace the Finish button.
<div class="clsPromptComponent“ …
HTML Items 1





The first HTML item starts the div tag for this
prompt object.
This item should be the object just before the
prompt object.
If the prompt object is in a table, this item should be
in the same cell.
This will allow the JavaScript function to find the
prompt object
<div id=“select Values”>
HTML Item 2





This item starts with the ending Div tag to close the
prompt object.
This item then starts the JavaScript and defines the
function used to count the items in the prompt and
either move forward or show an alert.
This function needs to be different depending on
the type of prompt used.
It must be placed right after the prompt object to
correctly wrap the prompt in the DIV tag.
</div><script language="javascript"
type="text/javascript">…</script>
HTML Item 3




This object replaces the Finish button.
It must create the Finish button.
On click, instead of running the
PromptAction(‘finish’) it will call the Function we
created in HTML Item 2
<div class="clsPromptComponent"
pt="btn"><button type="button"
name="FinishButton" id="finishButton" class="bp"
onmouseover="" onmouseout=""
onclick="CountSelected()" style="">Finish
</button></div><script>…</script>
Search and Select Prompt
Script for Search & Select


















</div>
<script language="javascript" type="text/javascript">
function CountSelected()
{
var eleSearch1 = document.getElementById("selectValues");
var selectObject = eleSearch1.getElementsByTagName("SELECT");
var eleSearch1Details = null;
var Search1Len = 0;
var foundSelections = false;
if (selectObject[1] && selectObject[1] != undefined)
{
eleSearch1Details = selectObject[1].getElementsByTagName('OPTION');
Search1Len = eleSearch1Details.length;
if (Search1Len < 11)
{
foundSelections = true;}
}
if (foundSelections == true)
{
promptAction('finish'); }
else
{alert('The number of selected items is too high, it should be 10 or less, you currently have ' +
Search1Len + ' items selected. Please remove some items then click on Finish again.'); }
}
</script>
Picklist Prompt
Script for Picklist Prompt
















</div>
<script language="javascript" type="text/javascript">
function CountSelected()
{
var Counter = 0;
var i;
var eleTg1 = document.getElementById("selectPicklist");
var SelectObject = eleTg1.getElementsByTagName("select");
if (SelectObject[0] && SelectObject[0] != undefined)
{
for ( i = 0; i < SelectObject[0].options.length; i++)
{
if (SelectObject[0].options[i].selected == true)
{
Counter++; }
}
if (Counter < 11)
{
promptAction('finish');
}
else
{
alert('The number of selected items is too high, it should be 10 or less,
you currently have ' + Counter + ' items selected. Please remove some items then click on Finish
again.');}
}
}
</script>
Checkbox Prompt
Script for Checkbox Prompt
















</div>
<script language="javascript" type="text/javascript">
function CountSelected()
{
var Counter = 0;
var i;
var eleTg1 = document.getElementById("selectPicklist");
var SelectObject = eleTg1.getElementsByTagName("input");
for ( i = 0; i < SelectObject.length; i++)
{
var node = SelectObject[i];
if (node.checked)
{
Counter++; }
}
if (Counter < 11)
{
promptAction('finish'); }
else
{
alert('The number of selected items is too high, it should be 10 or less, you currently
have ' + Counter + ' items selected. Please remove some items then click on Finish again.');}
}
</script>
Multiple Prompts
Here is a case where you might
have two different multiple selection
prompts and you want each one
limited, possibly even with a different
limit value.
<script language="javascript">
function CountSelected()
{var numOfPrompts = 2;
var limitForPrompt1 = 10;
</div>
var limitForPrompt2 = 10;
<script language="javascript" type="text/javascript">
<div
varid="selectPicklist">
isPromptsGood = 0;
function CountCheckbox()
var counterCheckbox = CountCheckbox(); {var Counter = 0;
var counterPicklist = CountPicklist();
var i;
if (counterCheckbox <= limitForPrompt1) //Check
first =
prompt
var eleTg1
document.getElementById("selectPicklist");
{isPromptsGood += 1;}
var SelectObject = eleTg1.getElementsByTagName("input");
else
for ( i = 0; i < SelectObject.length; i++)
{alert('The number of selected items is too high
in the checkbox, it should be ' +
</div> { var node = SelectObject[i];
<div
id="selectPicklist2">
limitForPrompt1 + ' or less, you currently have if' +(node.checked)
counterCheckbox + ' items
<script language="javascript" type="text/javascript">
selected. Please remove ' + (counterCheckbox{Counter++;}
- limitForPrompt1) + ' items then
function CountPicklist()} return Counter;}
click on Finish again.');}
{var</script>
Counter2 = 0;
if (counterPicklist <= limitForPrompt2) //Check second prompt
var i2;
{isPromptsGood += 1;}
var eleTg2 = document.getElementById("selectPicklist2");
Else
var SelectObject2
= eleTg2.getElementsByTagName("select");
<div class="clsPromptComponent" pt="btn"><button
type="button"
name="FinishButton"
{alert('The number of selected items is too high in the picklist, it should be ' +
if (SelectObject2[0]
&& SelectObject2[0] != undefined)
id="finishButton" class="bp" onmouseover=""
onmouseout="" onclick="CountSelected()"
style="">Finish
limitForPrompt2 + ' or less, you currently have ' + counterPicklist + ' items
{for ( i2 = 0; i2 < SelectObject2[0].options.length; i2++)
</button></div>
selected. Please remove ' + (counterPicklist - limitForPrompt2) + ' items then click
{if (SelectObject2[0].options[i2].selected == true)
<script language="javascript" type="text/javascript">
on Finish again.');}
{Counter2++;} } } return Counter2;}
var promptButtonFinish = new CPromptButton (document.getElementById("finishButton"),
if (isPromptsGood == 2) //Check if all prompts are good
PROMPTBUTTON_FINISH, true, "NS"); </script>
{promptAction('finish');}
oCV_NS_.pageNavigationObserverArray =
Else
oCV_NS_.pageNavigationObserverArray.concat("promptButtonFinish");
{alert('Fix the prompts then click on Finish again.');} }
oCV_NS_.notify();
</script>
</script>
Scripts for Multiple Prompts
Multiple Prompts




This process can also be used to check multiple
prompts.
Create the DIV tags and functions around each
prompt item as before, however don’t use the
PromptAction(‘finish’) inside the individual
functions, instead return a result of True/False.
Create another HTML Item with a function that calls
the individual functions and determines if their
results are all True, if so, it runs the
PromptAction(‘finish’).
Have the Finish button call this function.
Code Examples Folder
A new folder has been created on Dev to house Code Examples
created by the BI team. These objects can be opened in Report
Studio to see and copy/paste the javascript to reuse in other
prompt reports.
Section 2
REPORT CACHE
Report Prompt Performance
A user clicks on run and gets the hourglass waiting for the
prompt page to retrieve its data and render.
Set the Report Cache
Click on the Set Properties for the report.
Set the Report Cache cont.
On the Report tab, click on the Advanced options to
expose the Report Cache properties.
Click on the Refresh the Report Cache property. Then
set the time to hold the cache before it is refreshed. We
recommend it should be refreshed at least as often as
the dimensional data the prompt uses.
Section 3
BURST REPORTS
Why Burst Reports





Burst Reports Run Once Distribute Many
Burst Reports are subset of data in the report
based on grouping
Each grouping can go to a unique recipient
Recipients can be email addresses, Cognos
Contacts, Cognos Groups, or Cognos Distribution
Lists
Recipient/Grouping relationship can be defined in a
table stored with the data and used for all burst
reports
Examples of Report
This grouping report emailed to [email protected]
This grouping report emailed to [email protected]
This report is grouped by the Sales Rep and then sent out to each sales rep
with only their data.
Create Grouping and Burst Data Items
This report has two important columns to allow bursting, the Staff name
data item is the grouping column, and the UserID column actually
defines the recipients for the grouping.
Set Burst Options
Go to File/Burst Options to define
how to burst the report.
Set Burst Options Cont.
Set the data item to define the recipient of the burst group, again, there
must be a relationship in the report, or framework package, between
the recipient and the grouped data item.
Define the grouping data item to use for the burst separation, in this case
the Staff name of the sales rep is used to create our report example.
Set the type of bursting, in this case all of our recipients
are email addresses so that is what we set.
Burst Recipients Mixed Type
case lower(substring([gosales_goretailers].[Sales reps].[First
name],1,1)+[gosales_goretailers].[Sales reps].[Last name])
when 'atorta'
then '[email protected]'
when 'awilcox'
then 'CAMID(":Burst Test Group 1")'
else '[email protected]'
END
Run a Burst Report
In the run options screen, click on the advanced options link.
Run a Burst Report Cont.
Set the report to run in the background.
If the burst
report is to be
emailed to its
recipients, make
sure to click on
the Send the
report by email.
Set the Bursting option on for the report run.
Schedule the Burst Report
The schedule options screen is basically the same, just
make sure you click on the Override the default values to
get to this screen to set the options.
Burst Output in Archive
Burst Table Example
Add a table to your data source, it should include a Recipient
value like what is listed below.
BurstKey
Sales Rep
Recipient
1
Alessandra Torta
[email protected]
2
Allisia Wilcox
CAMID(":Burst Test Group 1")
3
Ana Orozco
CAMID("CognosLDAP:u:authid=3179095188")
Add the table to your Framework Package and create a join, in
this case on the Sales Rep to the Staff Name table in the GO
Sales package.
Section 4
JOINS UNIONS INTERSECTS
EXCEPTS MASTER-DETAIL
Multiple Queries



Reports often require multiple queries
Queries can be related through different types of
operations
It is important to use the correct relationship to get
the results required
Q1
Q2
Joins


Joins are the most basic type of relationship, they
function the same as they would in a SQL query.
There are two basic types of joins that are defined
by the cardinality
– Inner joins
EmpID
Name
EmpID
Position
1
John Smith
1
Manager
2
Ann Wilcox
3
Technician
Name
Position
John Smith
Manager
– Outer joins
EmpID
Name
EmpID
Position
Name
Position
1
John Smith
1
Manager
John Smith
Manager
2
Ann Wilcox
3
Technician
Ann Wilcox
Technician
Unions
Q1



Q2
R
Unions are used to combine multiple queries into a
single result-set.
There must be the same number of data items.
The data items must be compatible types.
EmpID
Name
EmpID
Name
EmpID
Name
1
John Smith
1
John Smith
5
Anna Ortiz
2
Ann Wilcox
2
Ann Wilcox
6
Tom Wilbur
5
Anna Ortiz
6
Tom Wilbur
Intersects
R
Q1 Q2


Intersects are used to combine multiple queries and
return the matching set.
Same criteria as Unions.
EmpID
Name
EmpID
Name
1
John Smith
1
John Smith
2
Ann Wilcox
6
Tom Wilbur
EmpID
Name
1
John Smith
Excepts
R
Q1 Q2


Excepts are used to return Q1 minus the matching
records from Q2.
Same criteria as Unions.
EmpID
Name
EmpID
Name
1
John Smith
1
John Smith
2
Ann Wilcox
6
Tom Wilbur
EmpID
Name
2
Ann Wilcox
Master Detail Relationships



A report that delivers information that would usually
require two or more reports.
Often used to combine lists and charts.
Requires a parent container with a nested detail
container
– Parent frame for master query and nested frame for the
detail
– Associate Report page with master query and data
container for detail query
Page Set Example
In this example, a page set is
created using Query 1 and paged by
the Product name data item.
Page Layout
This line holds the
Query 1 elements of the
Product image and
Product name.
A Chart object is added to
the page, this is Query 2,
after the data items are
added to the query, the
Master Detail relationship
is defined so this chart
only shows the Product
name being displayed on
the current page.
Report Output Example
Product Image
Product Name
Sales by Retailer for
Product
Sales by Staff for Product