Sunday, 21 December 2014

What are Dimensional Tables in Cognos ?

Dimension Tables are also tables and contains descriptive information used to describe the rows in the fact table. Look at the following fact table row….
dimensionaltable1-quontrasolutions
There is no product Name, no staff name, no Customer name. Without having these names, how do you describe the qty_sold as 1 and unit_sales_price as 200..?  We can’t..!
By looking prod_id (3), customer_id (5) in the product dimension and Customer dimension, we will get product name as “Santoor” and customer name as “Jhon“. So, by using dimensional tables we can describe fact rows with information such as product name, customer name etc..
facttable-quontrasolutionsdimensionaltable2-quontrasolutions

Fact less Fact Table in Cognos – QuontraSolutions

Fact less Fact table does not contain any facts. Generally Fact less fact tables are used to record the events such as students attendance, attendance of participants for an Event like a Meeting. Actually this is not a Fact table but due its position we should call it as Fact table without facts.. Look at the following image..
factlessfacttable-quontrasolutions
See the attendance Table in the Image, There is no measure or fact but Attendance Table sits like a Fact table and also have the connections to dimension tables and hence we can say that it is Fact Table and since it does not contain any measures so that we can call it as fact less fact table.
Now Let us understand the use of the Attendance Fact less Fact table….
If a student attended a class then we enter a Record in Attendance table else no row will  be stored. Only aggregation here is possible only count.

Mouse over pop up with our own text box using java script in Cognos – QuontraSolutions

Cognos: Mouse over pop up with our own text box using java script
Sometimes we need a popup message box when we over the mouse on some image like below..
Mouse over pop up with our own text box using java script
Take Html object into RS
paste the following code
<style>
.tooltip
{
text-decoration:left;
position:relative;
}
.tooltip span
{
display:none;
-moz-border-radius:6px;
-webkit-border-radius:6px;
border-radius:6px;
color:black;
background:#f2f2f2;
}
.tooltip span img
{
float:left;
margin:0px 8px 8px 0;
}
.tooltip:hover span
{
display:block;
position:absolute; top:-40px;  left:5px;
width:300px;
height:75px;
max-width:400px;
min-height:75px;
border:1px solid #888888;
margin-top:12px;
margin-left:32px;
overflow:hidden;
padding:8px;
}
</style>
<a class=”tooltip” href=”#” style=”border:none”>
<img src=”../samples/images/red.jpg” style=”border:0px solid #cc3300;” />
<span style=”text-align=left”>
Insert definition here
</span>
</a>
you can put your own image in the above script

Add a Multimedia File to a Report in Cognos – QuontraSolutions

Place your Video (.wmv or .avi format) in the following path
C:\Program Files\ibm\cognos\c10\webcontent\samples\images\

Note: 
You must have Windows Media Player installed on your computer.
  1. In the Insertable Objects pane, on the Toolbox tab, drag the HTML Item object  to the report.
  2. Select the HTML Item.
  3. double-click the HTML Item
  4. In the HTML dialog box, type the following:
<OBJECT classid=”CLSID: 6BF52A52-394A-11D3-B153-00C04F79FAA6″> <PARAM NAME=”URL” VALUE=”/c10/webcontent/samples/images/yourVideo.wmv”/> </OBJECT>
Run the report and click on Video and enjoy…

Expand and Collapse in Cognos – Quontra Solutions

One of the most requested features in Cognos is expand/collapse feature. At the moment you can expand members in Workspace, but my users find the tool slow and generally difficult to use. There is a JS solution, but it only works in the rows and requires all of the data preloaded. If your data contains thousands of members, this can make the report run slow.
It works by passing the member unique name of the current row to a hidden prompt, appending that value to a set, and reordering the set to the natural order. Collapsing the row is a simple matter of removing the member from the prompt. As an added feature, this means you keep the third level visible while hiding the second level.
Expand-Collapse-QuontraSolutions
We’ll begin with the query. Each crosstab node refers to three data items. The set, the member unique name, and the count of children.
The set looks like:
1#promptmany(
2  'ProductsSet'
3  ,'mun'
4  ,'[sales_and_marketing].[Products].[Products].[Product line]'
5  ,'hierarchize(union([sales_and_marketing].[Products].[Products].[Product line],descendants(set('
6  ,'[sales_and_marketing].[Products].[Products]'
7,'),1)))'
8)#
If nothing is selected, it defaults to [sales_and_marketing].[Products].[Products].[Product line]. That can be any valid set expression. If a value is selected, it unions the descendants to the set, and reorders it to the natural order.
The count is:
1count(1 within set children(currentMember([sales_and_marketing].[Products].[Products])))
This is to control when the expand button appears. It obviously doesn’t make sense to show the button if there are no children.
The member unique name is simply:
1roleValue('_memberUniqueName',[Product line])
The count and mun are added to the properties of the node, so we can refer to them in an HTML expression.
1case when [Query1].[PLChildren] = 0 then '' else
2
3case when ParamDisplayValue('ProductsSet') contains ([Query1].[PLMun])
4then '<input type="button" onclick="
5paulScripts.removeMunsFromPromptCompat(''Product line'','''+[Query1].[PLMun]+''');
6oCR.sendRequest(cognos.Report.Action.FINISH);
7"
8value="-"
9>'
10else '<input type="button" onclick="
11paulScripts.passMunsToPromptCompat(''Product line'','''+[Query1].[PLMun]+''');
12oCR.sendRequest(cognos.Report.Action.FINISH);
13"
14value="+">'
15end
16end
If there are no children, then don’t render it. If the member already appears in the parameter, then show the collapse button. Otherwise show the expand button.
This method will also work in previous versions of Cognos if you adapt the JS to work with the old JS API.

Hiding Cognos Connection Elements – Quontra Solutions

By default Cognos offers a ride range of UI options. Based on the group or role you can programmatically decide who can see which UI Elements. A simple modification of the system.xml file is all that is needed to take care of this.
But what happens when you have a requirement to remove elements that don’t appear in the list? This is possible by modifying one of the template files that controls the structure of the portal. These files are saved as XSL files, and are easily modified using any text editor. I strongly recommend using a text editor with XML support, such as Notepad++.
Before I continue, it’s worth mentioning the following. Changing these files may be risky. A misplaced semicolon will prevent Cognos from loading, and IBM has a tendancy not to support installations that have modified the Cognos internals. These changes will also be overridden by any patches or upgrades.
Always make a backup of any files you modify. When trouble does occur and you need IBM’s help, simply switch the active files with the backup, and IBM will be none the wiser (and if the problem is fixed, you know where to look).
You should also maintain a change log of everything you do. Upgrades will replace the template files, and there may be differences between the versions. Instead of simply overwriting the upgraded version with your modified version, you should make all the changes again manually. Fortunately (unfortunately?) patches and version upgrades tend to be rare occurrences.
A brief explanation of the files to modify:
There are two primary XSL files which control the portal.
1. \templates\ps\logicsheets\presentation\controls\presentation.xsl
2. \templates\ps\logicsheets\presentation\main\presentation.xsl
The \controls\presentation.xsl renders, among other things, the HTML behind tabs, and the rows and columns in the Public Folders/My Folders table.
The \main\presentation.xsl renders the links to the correct style sheets, the page headers, and the individual links in the Public Folders/My Folders table.
Between the two, they control the HTML between most of the objects you see on the page.
The system.xml file that contains the UI black list can be found in \templates\ps\portal\system.xml
And now the problem. The client has decided that no user, except administrators and report authors, should be able to see the Properties or More… links for any reports or folders.
First, hiding the properties.
In the \main\presentation.xsl do a search for action_properties.gif. There should be four instances of that string. Each of these instances controls the properties for a different type of object. Series 7 object, CRN object, Job and… well, I’m not entirely sure what the fourth one is for, but I’m sure it’s very important. 5 points to whoever enlightens me.
In each of these four cases the action_properties.gif is part of a xsl:call-template reference.
hide-prop-QuontraSolutions1
This statement is calling the renderActions template with values for the onclick, icon and tooltip parameters.
In order to prevent this segment from being rendered we need to add it to the “Elements you can hide” list. The code that controls items on the list is as follows:
1<out:if test="not(contains($ui_black_list, 'NAME'))">        
2</out:if>
This will check the system.xml for an appearance of NAME in the ui_hide list. Should it appear, it will not render anything inside the code block except for users or groups listed in the show parameter. Knowing this, all that is needed to hide the properties is to add the black list code to all four appearances of the renderAction call.
1<out:if test="not(contains($ui_black_list, ' PROPERTIES '))">        
2    <xsl:call-template name="renderAction">
3        <xsl:with-paramname="onclick">actions('{xtsext:javascriptencode(string($obj-name))}', '{$obj-class}', '{xtsext:javascriptencode(string($obj-path))}','properties_general.xts');</xsl:with-param>
4        <xsl:with-param name="icon" select="'action_properties.gif'"/>
5        <xsl:with-param name="tooltip" select="'IDS_PROPERTIES'"/>
6    </xsl:call-template>
7</out:if>
In this case, I set the black list name to PROPERTIES. Now, in the system.xml simply add the following to the ui_hide param:
1<PROPERTIES show ="Administrators RSUsers"/>
When you restart, only administrators and authors will be able to see the properties for any object.
Now for the More…
The technique to hide the “More…” link is almost identical.
Simply find all occurences of IDS_ACT_MORE in the same file. In my modified file all occurences appear between lines 4640 and 4722. Thanks to Notepad++’s XML support, I can see that all of these are held inside a single group.
hide-prop-QuontraSolutions2
Instead of black listing each individual appearance of the More… link, let’s try black listing the entire group.
hide-more-QuontraSolutions3
Now to add MORE to the system.xml black list
As before, only administrators and authors will be able to see the More… link.
The following shows how folders look to normal users:
folders-no-properties-QuontraSolutions4
And this is what reports look like:
sample-reports-no-properties-QuontraSolutions5
It’s very important to do extensive tests to ensure this meets the client needs. You could probably also reverse the black list by getting rid of the “not()” from the code. Remember that the idea behind this article is not to simply show how to hide specific elements from the Cognos Connection. It’s to show how easily it is to shape Cognos to meet your needs. The XSL sheets are there to be modified.