Hide excel/xml/csv output options in cognos viewer using java script

create HTML item in header/footer of the report and copy and paste the following code
<script language=”JavaScript”>
var y=getFormWarpRequest().elements["cv.id"];
// RS means it has been launched from Report Studio
// _NS_ means it has been launched from Cognos Connection
if(y.value == “RS”) {
window.oCVRS.rvMainWnd.setToolbar(
{
“namespace”:”RS”,
“divId”:”CVToolbarRS”,
“style”:”toolbar”,”S”:[
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"B":
{"N":"runIn",
"ALT":"Change report format",
"M":
{"id":"runInwindow.oCVRS",
"Y":"dropDown",
"IS":[
{"I":
{"N":"viewInHTML",
"A":"window.oCVRS.getRV().viewReport(\'HTML\');",
"E":"View in HTML Format",
"C":"/ibmcognos/rv/images/action_view_html.gif"}
},
{"I":
{"N":"viewInPDF",
"A":"window.oCVRS.getRV().viewReport(\'PDF\');",
"E":"View in PDF Format",
"C":"/ibmcognos/rv/images/action_view_pdf.gif"}
}
]
}
}
},
{“P”:
{“Y”:”vertical_line”}
},
{“B”:
{“N”:”openWith”,
“M”:
{“id”:”openWithwindow.oCVRS”,
“Y”:”dropDown”,
“H”:”true”,
“IS”:[]}
}
}
]
}
);
}else{
window.oCV_NS_.rvMainWnd.setToolbar(
{“namespace”:”_NS_”,
“divId”:”CVToolbar_NS_”,
“style”:”toolbar”,
“S”:[
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"B":
{"N":"runIn",
"ALT":"Change report format",
"M":
{"id":"runInwindow.oCV_NS_",
"Y":"dropDown",
"IS":[
{"I":
{"N":"viewInHTML",
"A":"window.oCV_NS_.getRV().viewReport(\'HTML\');",
"E":"View in HTML Format",
"C":"/ibmcognos/rv/images/action_view_html.gif"}
},
{"I":
{"N":"viewInPDF",
"A":"window.oCV_NS_.getRV().viewReport(\'PDF\');",
"E":"View in PDF Format",
"C":"/ibmcognos/rv/images/action_view_pdf.gif"}
}
]
}
}
},
{“P”:
{“Y”:”vertical_line”}
},
{“B”:
{“N”:”openWith”,
“M”:
{“id”:”openWithwindow.oCV_NS_”,
“Y”:”dropDown”,
“H”:”true”,
“IS”:[]}
}
}
]
}
);
}
</script>

Cognos: Mouse over pop up with our own text box using java script

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..

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

Hide tool bar in cognos viewer:

1) Take HTML drop in Header

paste the following code

<script language=”JavaScript”>

var y=getFormWarpRequest().elements["cv.id"];

// RS means it has been launched from Report Studio
// _NS_ means it has been launched from Cognos Connection
if(y.value == “RS”) {
window.oCVRS.rvMainWnd.setToolbar(
{
“namespace”:”RS”,
“divId”:”CVToolbarRS”,
“style”:”toolbar”,”S”:[
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"B":
{"N":"openWith",
"M":
{"id":"openWithwindow.oCVRS",
"Y":"dropDown",
"H":"true",
"IS":[]}
}
}
]
}
);
}else{
window.oCV_NS_.rvMainWnd.setToolbar(
{“namespace”:”_NS_”,
“divId”:”CVToolbar_NS_”,
“style”:”toolbar”,
“S”:[
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"P":
{"Y":"vertical_line"}
},
{"B":
{"N":"openWith",
"M":
{"id":"openWithwindow.oCV_NS_",
"Y":"dropDown",
"H":"true",
"IS":[]}
}
}
]
}
);
}

</script>

How to burst a crosstab report:

Resolving the problem

Add the burst key to a data container with one dimension, such as a list report. To burst a data container with multiple dimensions such as a chart or crosstab, create a master-detail relationship between this query and a single dimensional query, and then add the burst key to the single dimensional query.

Drop the crosstab within a list object that contains the burst key. Please see the attached Cognos 8.2 report spec going against the GO Sales and Retailers package.

1. Create a list report with the data item (Staff Name (Multiscript)) that you want to burst on as your first column. This will be the master list.
2. Insert your chart or crosstab as the second column of the list. This automatically creates a second query, which will be the detail query.
3. Add data items to your crosstab as needed. (Order Method as Rows, Order Year as Column, Quantity as Measure)
4. In the list, create a section with the item you want to group on. This is for formatting only.
5. Hide the list column title under the List property General -> Column Titles -> Hide.
6. In the Query Explorer, add the burst key data item that is in Query1 to Query2 (Staff Name and Email)
7. Select a crosstab cell and use the ancestor tool to move up to ‘Crosstab’ In the properties for the crosstab, edit the Master Detail Relationship property to link the burst key in both queries. (Staff Name)
8. Click on the row section of the crosstab (Order Method in the example provided). In the properties pane under Data, open the Properties dialog and click on the box for the burst key so it is checked.
9. Set Burst Options to burst on the list object’s burst key.
10. Burst the report and view the results.

COGNOS: Add a Multimedia File to a Report

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…

Cognos: Bursting Report to a file system

Bursting cognos report to Local File system and also run a script so that bursted reports can be renamed.

1) Go to the dispatcher –  ( See the picture below for reference )

2) Click on the dispatcher link to see the content manager service.

3) Go to the settings tab and click on edit on advance setting line -

4) Create following 2 variables and provide the path where you want to get the files bursted.

5) Save it and go to cognos configuration to do the following setting.

6) Run the report that you have to burst with the following settings.

7) You will get the output in D:/Report folder, now you want that bursted reports should get proper names or they should get names according to there bursted key.

8) Create a notepad file giving it a name “burstKey.bat” and paste the following contents and save it in D:\Reports folder.

@echo off

Rem  this batch file is launched with a working directory of \bin

set dest=D:/Reports

echo “BurstKey Script” >> %dest%/batch.txt
echo Batch File Log >> %dest%/batch.txt

rem       Set the values for the PDF file and XML
rem       to environment variables
rem       the reason for this is the scripting language
rem       cannot read variables as parameters
set parameter_pdf=%1%
set parameter_xml=%2%

rem      Logs the variables to a batch log file
echo values set >> %dest%/batch.txt
echo Parameter_pdf: %parameter_pdf% >> %dest%/batch.txt
echo Parameter_xml: %parameter_xml% >> %dest%/batch.txt

rem      Calls the script file
call %dest%/burstKeyRename.vbs

echo Completed >> %dest%/batch.txt

9) Create a file “burstKeyRename.vbs” and paste the following contenets in there and save it in D:\Reports folder.

‘
‘
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set xmlDoc = CreateObject(“MSXML.DOMDocument”)
Set WshShell = WScript.CreateObject(“WScript.Shell”)
Set WshSysEnv = WshShell.Environment(“process”)

‘ Values for Environment can be “user”, “system”, “volitile” and “process
‘ The value “process” is the only valid one for this type of operation.
Set WshSysEnv = WshShell.Environment(“process”)

‘ To check the value for process environment variable
‘ this can be tested at the command prompt.
‘ Uncomment the next line and run this from command line
‘ WScript.Echo WshSysEnv(“parameter_xml”)

‘ Initializing Variables
xFile = WshSysEnv(“PARAMETER_XML”)    ‘Name of the XML file
sFile = WshSysEnv(“PARAMETER_PDF”)    ‘Name of the PDF file

‘ ***** Start override section for testing outside Cognos environment *****
‘
‘ Set the following if-then condition to “true” for local testing using files that
‘ have already been burst.  If the if=then condition  is set to “false”, then the
‘ environment variables must be set properly otherwise a  run-time error will occur.
‘
If (false) Then
‘The filename component of the output file. Do not include the extension.
tempName = “D:\C83Outputs\wottcattondxp\TestOutputs\911_1190657394280″
‘ Derive the name of the XML descriptor file
xFile = tempName & “_desc.xml”
‘ Derive the name of the new output file.
sFile = tempName & “.pdf”
End If
‘
‘ ***** End override section for testing outside Cognos environment *****

sMsg = “”
sNewFileName = “”

‘
‘ Read the XML descriptor file created by CRN/Cognos 8 to get the value of the element. This
‘ value will be used as the new filename. If there is no value in the element, a new file will not
‘ be created.  It is assumed that the value of the element does not contain any of the
‘ reserved characters the underlying OS uses for filename.
‘
xmlDoc.async = False
xmlDoc.Load xFile

If (xmlDoc.parseError.errorCode <> 0) Then
sMsg = xFile & ” : Error parsing XML ” + xmlDoc.parseError.reason
Else
Set rootXML = xmlDoc.documentElement
Set oNodeList = rootXML.childNodes
iNode = 0

For Each Item in oNodeList
If (xmlDoc.documentElement.childNodes.Item(iNode).basename = “burstKey”) Then
sNewFileName = xmlDoc.documentElement.childNodes.Item(iNode).text
If (sNewFileName = “”) Then
sMsg = xFile & ” : element in ‘” & xFile & “‘ is empty. Unable to copy file.”
Else
sMsg = xFile & ” : Burst key value is ‘” & sNewFileName & “‘”
End If

‘ This if condition  was added to support the use of CAMID for the burst key
if (instr(sNewFilename, “CAMID”)) Then
intFirstPosition = instr(sNewFilename, “:”)
intSecondPosition = instr(intFirstPosition, sNewFilename, “)”)
sNewFileName = mid (sNewFilename, intFirstPosition + 1, intSecondPosition – (intFirstPosition + 2))
sMsg = xFile + ” : Value extracted from CAMID is ‘” & sNewFileName & “‘”
End If
End If
iNode = iNode + 1
Next

Set oNodeList = Nothing
Set rootXML = Nothing
End If

‘
‘ If there is a new filename, make of copy of it otherwise just log an error. A local log file with a name
‘ that is derived using the filename given to the output by CRN/Cognos 8 will be created so it is easy
‘ to determine if the file copied successfully  or not. Since this information will also be written to the
‘ persistent log file, writing to the local log file can be bypassed if writing it creates too much clutter in the
‘ location specified in CM.OUTPUTLOCATION.
‘
if (sNewFileName <> “”) Then
sNewFileName = getPath(sFile) & sNewFileName & “.pdf”
writeLocalLogFile sFile, sMsg & vbCrLf & “Copying file named ‘” & sFile & “‘ to ‘” & sNewFileName & “‘” & vbCrLf
objFSO.CopyFile sFile, sNewFileName
Else
‘ Set this variable to an error message so it will be picked up below when writing to the persistent log file…..
sNewFileName = “”
writeLocalLogFile sFile, sMsg & vbCrLf
End If

‘
‘ Update the persistent log file with the result. This log file will have data appended to it in order to keep the history.
‘
sMsg = “—– Start of entry —–” & vbCrLf
sMsg = sMsg & “Date : ” & date & vbTab & “Time : ” & time & vbCrLf
sMsg = sMsg & vbCrLf & “Original Name :” & sFile & vbCrLf
sMsg = sMsg &  “New Name : ” & sNewFileName & vbCrLf
sMsg = sMsg &  “—– End of entry —–” & vbCrLf & vbCrLf

sPersistLogFile = getPath(sFile) & “crn_pdf_rename.txt”
writePersistLogFile sPersistLogFile, sMsg
‘
‘ All done.. Release references to the objects used in this app.
‘
Set objFSO = Nothing
Set xmlDoc = Nothing
Set WshShell = Nothing
Set WshSysEnv = Nothing

Function getPath(sFileName)
sPathOnly = “”
lastSlashPos = InStrRev(sFileName, “\”)

if (lastSlashPos > 0) Then
getPath = mid (sFileName, 1, lastSlashPos)
End If
End Function

‘
‘ writeLocalLogFile
‘     Create a log file using a name derived from the filename that was generated by
‘    CRN/C8 before it was written to the location specified by CM.OUTPUTLOCATION.
‘
Sub writeLocalLogFile(sFileName, sData)
sLogFile = left(sFileName, instr(sFileName,”.”)-1) +   “_log.txt”
writeLogFile sLogFile, sData
End Sub

‘
‘ writePersistLogFile
‘     Write a record to the persistent log file
‘
Sub writePersistLogFile(sFileName, sData)
writeLogFile sFileName, sData
End Sub

‘
‘ writeLogFile
‘     Generic routine to open a file, append a record to it and close the file
‘
Sub writeLogFile(sFileName, sData)
If (objFSO.FileExists(sFileName)) Then
Set logFile = objFSO.GetFile(sFileName).OpenAsTextStream(8)
Else
Set logFile = objFSO.CreateTextFile(sFileName)
End If

logFile.Write sData
logFile.Close

Set logFile = Nothing
End Sub

Now follow the 6th step again and you will get proper pdf output with name same as burst key.

Tree Prompt on Relational Models using Prompt API

This article focuses on using the Prompt API against the Tree prompt making it easier to use the Tree prompt against a relational model.

This method works for multi-select tree prompts as well. The solution can be extended to get prompt selected data for the various levels and using them individually in the reports for various other solutions.

Using this method we can do away with writing complex macros to massage the prompt data to suit the relational model.

The solution is simple:

Assuming the MUNs have Year, Q, Month included in them to help identify the levels, if not you will have to modify the sections referring to val.indexOf(“Year”), val.indexOf(“Q”), val.indexOf(“Month”) to match your MUN sections to identify Year, Quarter and Month:

Have a couple of hidden text box prompts in the report depending on the number of levels you have in the tree prompt.
Use the prompt API to get the list of selected prompt values.

• Identify the level of the selected prompt value.
• Transform the prompt value to a relational value.
• Append the prompt value in the respective text box prompt.
• Create filters in the query passing in the text box prompt values.

Step 1: Create the tree prompt and a list report to get the required data.

Step 2: Name the tree prompt as “treePrompt”.

Step 3: Create 3 hidden text box prompts named YearPrompt, QrtrPrompt, MonthPrompt.

Step 4: Create filters in the list query to accept the parameter values from YearPrompt, QuarterPrompt, MonthPrompt as mentioned below:
(#csv(split(‘;’,promptMany(‘pYear’,’token’)))#)

Step 5: Insert an HTML item with the below prompt API code:

cognos : 100% stacked Bar chart displaying percentages in bars using Java Script

We can’t display percentages in middle of the bars in cognos 10.2, 100% stacked bar chart. But the same has been fixed in Cognos 10.2.1.

We can do the same thing using Java Script. just follow the below steps..

Suppose we have the following columns in List Report..

Region, Actual, 100% stacked bar chart with percentages, Budget as follows..

1) Take the columns Region, Actual, and budget into List Report

2) Goto Query Explorer

3) Open Query1

4) Add Data Item, enter the following expression to calculate Actual percentage over total of actual and budget
[actual]/([actual]+[Budget])* 100

5) Name this data Item in properties as “actp’

6) Add another Data Item to calculate Budget percentage and enter the below expression
[Budget]/([actual]+[Budget])* 100

7) Name this data Item in properties as “budp’

8) Goto Page explorer and select Page1 to see the list report

9) Drag one HTML item and drop between the columns Actual and Budget as a column

10) Click LOCK icon in tool bar to unlock

11) Add 4 more HTML items next to previously added HTML item, so you should have 5 HTML items

12) double click First HTML item and add the following script

You will get the output as below…

Note: This chart does not show percentage on bar if percentage < 20 as the width bar is very low so we can’t show text on it.

Please note that, we can do the same thing using Micro Chart, but we will get extra space around the bar therefore, the lines appear with unnecessary space that we can’t avoid

Cognos: Age calculation

We have two ways to calculate age in years and age in yy years mm months and dd days format

Calculate Age in years…

Create a query Calculation as below…

_years_between (current_date, [Birth date column])

Calculate Age in yy years, mm months and dd days format…

(replace [Birth Date] with your own Data of birth column)

substring (cast(_ymdint_between (current_date, [Birth date]),char(6)),1,2) || ‘ Years ‘||

substring (cast(_ymdint_between (current_date, [Birth date]),char(6)),3,2)

|| ‘ months ‘ ||

substring (cast(_ymdint_between (current_date, [Birth date]),char(6)),5,2)

|| ‘ Days ‘

IBM Cognos Report Design Best Practices

Installing the Appropriate Fonts on the Client Computer

The PDF extension does not embed the fonts in the report. In order to view a report in the correct font, you need to make sure that the fonts are not only installed on the Report Server, but also on the client computer that is used to view the report. Otherwise, font substitution will most likely occur.

HTML

The HTML rendering extension generates HTML 4.0 pages that are compatible with Microsoft® Internet Explorer, Mozilla Firefox, and Apple Safari.

There are several differences between HTML rendering and other renderings, including the following:

• The HTML rendering extension builds a table in HTML to contain each set of report items. Items are positioned in the table to preserve the report layout.
• Locations and sizes are expressed in millimeters (mm). Differences of less than .2 mm are rendered as 0 mm.
• HTML does not support item overlap. This can result in layout changes as the report is displayed.
• A subreport is rendered as a DIV tag in the HTML report.

Client Variations

When displayed on the client, an HTML report uses the browser-specific settings. In addition to possible font substitutions, other browser settings can likewise produce changes. If you want to precisely control the layout of the report on all clients used to view the report, consider using the image rendering extension.

Avoiding Blank Pages

Sometimes, you will see blank pages when you output reports to a physical page format such as PDF or print. Generally, this will happen when the size of the report body exceeds the size of the page.

To ensure that all the contents fit on a single page, the body width plus the margins should be less than the defined page width.

A textbox or other report item can cause the width of the body to exceed the page width, even when the portion of the item that exceeds the width has no visible contents. In addition, report items growing horizontally (matrix data regions and images set to automatically Autosize or Fit) can also cause the body to grow.

Using Page Breaks to Improve Performance for Large Reports

If you do not specify a page size or page breaks for a report that returns a large amount of data, some report formats will try to render the report as a single page.

For example, Excel has no default notion of a fixed page size. As a result, if you have a very large report, Excel will try to render it as a single worksheet. In general, using page breaks improves the performance for the users accessing the report, because they can view the first page while the rest of the report is being rendered.

Using Filters Instead of Query Parameters

Reporting Services has several methods for dynamically filtering report contents, including the following:

• Query parameters filter data at the source as it is retrieved.
• Report filters, applied to a dataset or data region, limit the data that is displayed from a generated report.

Using filters retrieves all data, but only data that is relevant to the user is displayed. This may be less efficient on an individual report basis than filtering at the source. However, it lets you retrieve the data once from the source and store in it a snapshot to serve many different user communities.

On the other hand, when using query parameters, you must revisit the data source for each new value of the query parameters. Filters enable you to use execution snapshots and still get full parameterization.

Adding Alternating Bars to a Table

It is possible for you to create a report that contains a table or matrix in which every other row is shaded. This bar effect makes it easier to visually track the different rows across a page.

To more closely simulate the old “green bar” paper that was used at one time to run large reports on high-volume data center printers, you can make the alternating bars green.

Following:

Adding Global Values to Headers and Footers

The members of the Global object collection, shown in Table 6, are available for use within report expressions.

Displaying Report Items in the Page Header

For long reports, you may want to include text from the body of the report in the header. For example, a directory listing could list the first and last occurrence of the last name field in the header to indicate the range of names included on the page.

Creating Drill-Down Links with Conditional Formatting

Reporting Services lets you put hidden items on a report page and make the items visible, based on the user interaction with the report.

You can use this capability to create a drill-down link within a report. For example, by clicking in a certain area, the user could drill down from a summary view of data to detailed information.

To do this, first select the group, column, or row of a table to hide and set its Hidden element to True. Next, create a toggle item for a text box in a containing group. Then, when a user clicks the text box, hidden data becomes visible or visible data disappears.

IBM Cognos Report Design Best Practices

Using Tables Instead of Lists for Excel-specific Reports

A table uses a fixed column width. This matches very well with the tabular format of Excel reports. The items in the report table will line up as you expect them to when rendered in Excel.

In contrast, a list is a freeform style. Items in the list are positioned in the worksheet relative to their location in the report. This can lead to unexpected results. If your report uses a list, be sure to check the rendering to Excel to see if the results are acceptable.

Even with tables, if you have a header that spans multiple columns in a report, the Excel rendering extension may need to merge cells or introduce new columns. This can affect the ability to sort and manipulate data in the Excel spreadsheet. If you are planning to render to Excel, try to ensure that the left or right edges of the report item line up in order to minimize cell merging.

Maximum Number of Pages in Long Reports

To prevent Excel from generating an error, you should keep track of the number of pages in lengthy reports. Specifically, each page in a report becomes a worksheet in Excel. However, Excel can only support a maximum number of worksheets per workbook, limited by available memory. If the report pages exceed that limit, Excel generates an error.

Color Differences in Rendering to Excel

Excel supports a predefined set of colors. When you render a report, the Excel rendering extension maps the report colors to the best match in the natively supported colors in Excel.

Image

The Image rendering extension renders reports in bitmap or metafiles. By default, it renders data in TIFF format. However, it can generate files in any format supported by GDI+, including BMP, EMF, GIF, JPEG, and PNG.

An image rendering extension on the Report Server processes the report on a virtual page and creates the image from that virtual page. As a result, the image-based report will look the same, in terms of font and layout, on every client.

When you are working with TIFF files, they can be viewed in multiple pages. However, other image formats generate one file for each report page.

Image rendering also supports page height, page width, and margins. Any headers and footers contained in the report are rendered inside the margins of the report.

Using Image Rendering to Create the Same Reports on All Clients

If you want your reports to look the same on every client, you should use image rendering. Specifically, HTML reports use the client font and browser settings when rendering reports. This means that the layout can change when different browser clients are used. Because image files are formatted on the Report Server, they are rendered to an image file that should appear the same on every client.

Installing the Appropriate Fonts on the Report Server

You should make sure that the fonts you need are installed on the Report Server. This is because the report is actually rendered on the Report Server and it uses the fonts that are installed on the Report Server.

Portable Document Format

The portable document format (PDF) rendering extension creates reports that can be viewed with Adobe Acrobat readers. The PDF extension is similar to the image extension in many ways, except for the following important differences:

• Fonts are not embedded in the PDF reports.
• Document maps are rendered as PDF bookmarks.
• You can specify page width and height, margins, and resolution of the PDF, among other device options.
• The rendering extension creates PDF 1.3 files that are compatible with Adobe Acrobat 4.0 and later versions.
• The PDF rendering extension does not support RepeatWith.

IBM Cognos Report Design Best Practices – Item Positioning and Sizing

Item Positioning and Sizing

A report consists of three main areas: the page header, page footer, and the body. Reporting Services supports the freeform placement of report items in a report. Data regions can be side-by-side with other regions, or nested within groups.

When designing reports, it is important to understand how items will behave if they grow. The items in a report may grow either horizontally or vertically, depending on section repeats, content size, and on such rendering-specific reasons as font substitutions.

When an item grows, such as a table, it pushes peer items out of the way. Specifically, peer items are those items within the same parent container. Following are the two ways in which peer items are shifted:

• Each item moves down to maintain minimum spacing between itself and all the items ending above it.
• Each item moves to the right to maintain minimum spacing between itself and the items ending to the left of it.

If an item grows so that it would extend beyond the bounds of the containing item, the container grows to accommodate the contained item.

If an item overlaps another item, the Index element in the report definition determines which element overlaps the other. The item with the higher Index value is rendered on top of the item with the lower value.

There also are techniques you can use to control the way items grow and move other report items. For information about using rectangles to group items, see the section “Useful Tips for Report Design” later in this paper.

Pagination

One key design issue for long reports is controlling where the page breaks occur. Page breaks are controlled by two factors:

• Page size
• Page breaks that you specifically include before or after objects

Page Size

To control page size, set the page height and width properties for the report by using the following guidelines:

• For rendering formats that render physical pages, use the Page Height and Page Width properties for the report.
• Interactive rendering formats, such as HTML, do not render physical page breaks. Instead, use the Interactive Height and Interactive Width properties to create a soft page break.
• Some rendering formats, such as Excel, do not support page size. For these reports, you will have to specifically include page breaks to break the report into multiple pages.

Note that if the report itself is wider than the defined page width, the report will break across multiple pages horizontally.

IBM Cognos Report Design Best Practices – Introduction

Introduction:

The Report authors face many decisions, from what kind of data region to use to which format to use for report delivery.

Crystal Reports provides support through a wide range of report designs and formats, from paper-based tabular reports to interactive reports with images and drill-through capabilities.

This document consolidates guidelines, suggestions, and tips for report authoring and design. The intent of this document is to address common questions and concerns and to serve as a starting point for further investigation. The Reporting Services Books Online document offers a rich and comprehensive source of information on report authoring and design, and also detailed descriptions of report options and available functions and parameters.

Report Design Concepts

This section describes the key factors that affect report design and layout.

It supports four kinds of data regions: lists, tables, matrices, and charts.

• It offers a range of other items such as text boxes, images, links, rectangles, and sub reports.
• User interactivity is available through parameters, links, conditional display, end-user sorting, drill-down, and a document map.
• Reports may be rendered in a variety of formats, including HTML, Excel, PDF, image files, XML, and comma-separated values (CSV).

The decisions you make about each of these key areas can affect the final display and functionality of the report. The following sections offer more information about some of the factors that affect design and layout.

Data Regions

One of your first critical design decisions is which type of data region to use in your report. Reporting Services offers the following types of data regions:

• Tables
  A table represents data, row by row. The columns are static in that they do not expand, but the rows expand downward. Thus, as a table grows, it pushes the data beneath it downward. You can also group data within the table.
• Cross tabs
  a matrix, or crosstab is like a table, but both columns and rows can expand to accommodate data. A matrix can push other report objects downward and outward on the report page. You can group data and also nest columns and rows within the matrix.
• Lists
  A list is a freeform data region that is often used for creating forms. You can nest lists within lists to group data.
• Charts
  A chart is a graphical representation of data. Reporting Services offers a wide range of chart formats.

Your choice of data regions may depend on the type of data you are using, what you want the report to look like, and how you are rendering the report. For example, reports rendered to Microsoft® Excel® tend to work better when using a table instead of a list.

IBM Cognos FAQ’s – Event Studio

167) Define Event Studio:

Use the event studio to perform the tasks when the business event occurs.

When an event occur people are alerted to take actions.

168) Define burst:

To create many report results by running a single report once. For example, you can create a report that shows sales for each employee, and run it once, sending different results to regional managers by bursting on region.

You set up bursting in Report Studio and enable it in the portal.

169) What is a report?

A set of data deliberately laid out to communicate business information. Depending on the context, “report” may refer to report specification or report output.

Report (more precisely, report specification) is the generic term for the objects created by edited by Query Studio, Report Studio, and Analysis Studio.

170) What is Report specification?

The definition of queries, prompts, layouts, and styles that make up a report. A report specification is combined with data by a run operation to create report outputs. You create report specifications by using Report Studio, Query Studio, Analysis Studio, or through the Software

Development Kit.

171) Define report output?

A report output combines data at the point in time when the report was run with a report specification. It is a document that can be displayed, printed or emailed. Cognos 8 can produce report outputs in HTML, PDF, Excel, or CSV formats.

172) Define Content Manager:

The Cognos 8 service that manages the storage of customer applications, including application-specific security, configuration data, models, metrics, reports, and report output.

Content Manager is needed to publish models, retrieve or store report specifications, manage scheduling information, and manage the Cognos namespace.

173) What is data source?

A relational database, dimensional cube, file, or other physical data store that can be accessed though Cognos 8.

174) Define data tree:

Within a studio, contains objects such as query subjects, query items, dimensions, levels, and members. A data tree is used as a palette of the available data that can be inserted into calculations, filters, display areas, and other authoring gestures.

175) Define drill down:

The act of navigating from one level of data to a more detailed level. The levels are set by the structure of the data.

176) Define drill up:

The act of navigating from one level of data to a less detailed level. The levels are set by the structure of the data.

177) What is hierarchy?

A hierarchy represents a collection of dimensional members organized into a tree structure, with each member having one or more parent members and an arbitrary number of child members.

The root of a hierarchy has no parent, and leaf members of a hierarchy have no children.

178) What is layout?

In reporting, layout defines the appearance of the report, including formatting, style, and design.

In report specifications, layout is the portion of the specification that defines how the data returned by queries is presented.

179) Define level:

A level is a set of members that have common attributes. For example, a geographical dimension might contain levels for country, region, and city. Levels are used to define the structure of hierarchies in a dimension.

180) What is member?

A member is a unique item within a level. For example, Camping Equipment and Golf Equipment are members of the Product Line level.

181) Define locale:

A code that is used to set the language or dialect used for browsers, report text, and so on; and the regional preferences, such as formats for time, date, money, money expressions, and time of day.

For Cognos products, you can specify a locale for the product interface (product locale) and for the data in the report (content locale).

182) Define multidimensional expression language (MDX):

An abbreviation for multidimensional expression language, MDX is the multidimensional equivalent of SQL. Cognos 8 allows report authors to type in their queries using MDX expressions in Report Studio for dimensional data sources.

183) Use of repeater:

In Report Studio, a cell container that repeats values within itself with no predefined internal structure.

184) Define Repeater table:

In Report Studio, a table-like container that repeats cells across and down the page for row in the associated query.

185) What is template?

A reusable reports layout or style that can be used to set the presentation of a query or report.

186) Define work area:

The area within a studio that contains the report, analysis, query or agent currently being used.

187) What is a Hot File?

A separate local data table that can be added to your catalog or used in the report as a data source and as if it is a regular database table. A Hot File can be joined either with a database table or with another Hot File itself.

188) What is Impromptu?

Impromptu is a windows based powerful interactive query and reporting tool that lets you query data and create sophisticated reports in a easy – to- use and sophisticated desktop and interface.

189) What is the difference b/w macros and prompt?

Macro is a dynamic object which is used to substitute the values at query run time.

Prompt is a report element that asks for values before the report is run.

190) What is a Cube size?

Cube size is not static. it can be defined according to Project but we can create up to 2GB

191) What is refreshing of cube and how can we do it?

Refreshing a cube means getting the latest records from content store. we have an option to refresh cube.

192) what is meant by dash boards/flash cards?

Dash Board reports are used to create many reports in one report.

193) What is the usage of html object in tools box?

By using HTML object, We can insert multimedia file into report. At the run time we can see the file multimedia file.

194) How can we use conditional blocks?

Conditional Blocks adds an empty block that you can use for conditional formatting. You can hide and show report elements.

195) What is cube group?

Set of similar cubes built by Transformer.

196) What is burst reporting?

Burst reporting means sending a single report to multiple recipients using Cognos connection.

197) What is Level Span?

Level Span is used to avoid 1:1 relationship.

IBM Cognos FAQ’s

147) Cognos 8

Cognos Report Net with enhancements + EP7 series.

148) which server tire is responsible for transforming information to Cognos 8 Application Server?

A Web server contains one or more web gateways which is responsible for transforming the information from one server to an appropriate server.( Cognos 8 Application Server) .

149) Which Server Tire processes the users request?

Application Tire server, It’s a main server which process users request.

150) what is the role of Data Tire?

Data tire contains content store and data source.

151) what is content store?

The database that contains all data that Report Net needs to operate, such as

• Report specifications, published models, and the packages that contain them

• Connection information for data sources

• Information about the external namespace, and the Cognos namespace itself

• Information about scheduling and bursting reports

Design models and log files are not stored in the content store.

The ReportNet service that uses the content store is named Content Manager.

152) What the gateways Cognos web server supports?

CGI (Common Gateway Interface):- This is the default gateway supported to all web servers.

ISAPI (Internet service Application Programming Interface):-  Use this gateway for Microsoft Internet Information Services (IIS) web server.

Apache-Mod :- This gateway is used for Apache Web Server.

Report Studio

153) What are enhancements in list report in Cognos 8 Report Studio?

A list report is enhanced with following features:

1.     Can apply List column Title Style

2.     Can apply list column Body Style

3.     Can apply the table Style

154) What are enhancements in cross tab report in Cognos 8?

A Cross tab report is enhanced with following features:

a.     Can create the union Cross tabs.

b.    Can create headers and footers for a cross tab.

c.     List like cross tab.

155) What are enhancements in Chart report in Cognos 8?

A Chart report is enhanced with following features:

1. The following new charts are add

a.     Gauge Charts

b.    3D point chart

c.     By polar axis charts (used for Bio-technology).

2. The chart can be applied with patterns, Fonts, Colors.

3. Can perform drill on chart.

4. Now we can create map reports which include Geographical information and compare the business with various geographies.

156) what is Prompt Cache?

When a report contain a prompt executes a prompt queries the data from database at very first time and stores the results in prompt cache.

Analysis Studio

157) what is Analyses studio?

In analyses studio users can explore analyses and compare dimensional data. Use Analyses studio to read the data from multiple dimensional structures known as Cubes.

158) what are report Analyses Studio supports?

Analyses studio supports only cross tab, charts.

159) what is Cube?

A physical data source containing a multidimensional representation of data. A cube contains information organized into dimensions and optimized to provide faster retrieval and navigation in reports.

Query Studio

160) what are enhancements in Cognos 8 Query Studio?

1.     Formatting: – Now query studio allows you to format the report with font, colors, and border styles.

2.     Conditional Formatting: – The data in the report can be formatted based on given condition this allows you to quickly identify the data for faster analyses.

3.     Applying Templates:-Templates can be created in report studio which can be applied to the reports created in Query Studio.

4.     Improved Filtering: – Query Studio allows you to create more advanced filters using logical operators. AND or OR.

5.     Query Data from OLAP sources (cube) :- Now Query Studio supports in reading the data from multi-dimensional structures known as Cubes.

6.     Supports Hierarchy Levels: – Since Query studio reads the data from Cube which is defined with hierarchy levels it supports multi-dimensional analyses. (Drill Up, Drill Down, Drill Across).

161) what is Conditional format?

The data in the report can be formatted based on given condition this allows you to quickly identify the data for faster analyses.

Metric Studio Application

162) A set of metric types, metrics, and scorecards that monitor the performance of an organization.

Each organization may have several Metric Studio applications monitoring distinct units, such as operating companies or divisions that have different user communities, metric types or reporting calendars.

163) Define Metric designer?

It’s a Cognos 8 modeling tool use to create extracts for using Cognos 8 score cording applications.

Transformer

164) what is Transformer in Cognos 8?

It’s a modeling tool for creating the Power Cubes for use with Cognos EP7 series and Cognos 8.

165) what is a Power Cube?

A file that contains data that is structured to provide for fast retrieval and exploration of data in Power Play.

166) what is the role of Transformer?

Transformer is used to create dimensional models and also create cubes.

IBM Cognos BI Interview Questions – Query Studio

142) What is Query Studio?

Query Studio is the adhoc querying tool.

143) What is a adhoc report?

An adhoc report is a simple report or one-time report that is outside the set of standard reports you may have available to you.

A design time environment acts as a runtime environment.

It’s also known as self service.

144) What is measure?

A query item that contains values that can be aggregated to produce meaningful results. For example, product costs can be treated as a measure because average and total costs have some meaning, but product codes, though numbers, are not usually treated the same way. Measures are quantitative performance indicators.

Measures give the numbers that usually appear in the cells of cross tab reports or in the numbers of a chart.

Measures are also known as facts.

145) How Grand totals and Sub totals are generated in Query Studio?

In Query Studio the footer summaries (Grand Totals) are automatically generated for measures.

The sub totals can be generated by creating the Groups.

146) Define layout:

In reporting, layout defines the appearance of the report, including formatting, style, and design.

In report specifications, layout is also the name of an element that defines how the data returned by queries is presented.

IBM Cognos BI Interview Questions – Report Studio

100) What is a Report Studio?

Using Report Studio we can create more advanced professional Business Reports.

101) What is burst?

To create many report results by running a single report once. For example, you can create a report that shows sales for each employee, and run it once, sending different results to regional managers by bursting on region.

You set up bursting in Report Studio and enable it in the portal.

102) Define calculated member?

A member of a dimension whose measure values are not stored but are calculated at run time using an expression.

103) What is cardinality?

For OLAP data sources, cardinality is the number of members in a hierarchy. The cardinality property for a hierarchy is used to assign solve orders to expressions.

For relational data sources, cardinality indicates the nature of the relationship between two query subjects, query items, or other model objects.

104) Define report specification?

An XML representation of the queries, prompts, layouts, and styles in a report. You create report specifications by using Report Studio or Query Studio, or by writing your own report specifications in XML.

105) What is report view?

A reference to another report that has its own properties, such as prompt values, schedules, and results.

You use report views to share a report specification instead of making copies of it.

106) What is report item?

A query item that is added to a report is known as a report item.

Report items appear as columns in list reports, and as rows and columns in crosstab reports. In charts, report items appear as data markers and axis labels.

107) What is work area?

The area within a studio that contains the report, analysis, query or agent currently being used.

108) What is Simple List Report?

The data is displayed in a series of columns (Tabular format).

109) How to set Page Breaks?

To set the page breaks in the report needs to be designed with either Group or Section.

Each Group or section will be displayed in a separate page.

110) What is the procedure for Page Breaks?

Select the Grouped Report Item, from the changed layout menu click on set page breaks.

111) Define Sections:

Like group a section also eliminates the duplicates and display the report item as a separate block and separated from rest of other report items.

112) How to eliminate duplicates?

The duplicates can be eliminated by creating the Groups

113) What is a prompt?

A prompt is an end users object which allows you to select the values or enter the values to restrict the data displayed in the report.

A report element that asks for parameter values before the report is run.

A Prompt is known as dynamic filter.

Prompt can be created using Parameters enclosed between ? marks.

Ex :- ?prompt?

114) What are the types of Prompts?

The following are the types of prompts:

1.     Text box Prompt

2.     Value Prompt

3.     Select and Search Prompt

4.     Date Prompt

5.     Time Prompt

6.     Date and time Prompt.

115) How many ways are there to create prompt?

To create a prompt you can:

Ø  Use the Build prompt page tool.

Ø  Build your own prompt and prompt page.

Ø  Create a prompt directly in a report page.

116) What is a Non-reusable prompt?

The prompts that are created in the report environment are called as Non-reusable prompts.

117) What is a cascading prompt?

A prompt that uses values from a previous prompt to filter the values in the current prompt or Pick list.

118) How many ways we can create Cascading Prompt?

A Cascading Prompt can be developed in a two different ways.

1.     Single select and auto submit cascading prompt.

2.     Multi select and Re-prompt button cascading prompt.

119) What kind of relationship is required for cascading prompt?

To develop the cascading prompt the relationship between the dimensional query Items should be 1 to many (1: M).

120) What is a filter?

A filter is a condition, which restricts the amount of data displayed in the report.

121) What is Detailed Filter?

If the filter is applied before aggregation known as aggregated records are filtered. (Detailed Records are filtered)

122) What is Summary Filter?

If the filter applied after aggregation than aggregated records are filtered.

123) What is Group Filter?

A filter which is applied to the group is known as Group Filter.

124) Define Sorting:

Sorts the data in the following 2 directions

1.     Ascending

2.     Descending

The default sort is don’t sort.

125) Define the Custom Group:

A custom group allows you to compare one element in the attribute with remaining all elements with in the same attribute.

It is defined for comparative analyses.

It allows comparing one element with remaining all.

126) How the pages of a single report delivered to the browser?

Based on the order of the pages add to the report.

127) In How many file formats we can export Report Data.

The data can be exported into the following file formats

1.     PDF format (Portable Document Format)

2.     CSV Format (Comma Separated Value)

3.     XML Format

128) Use of Header and Footer in Reports?

Headers and Footers allow you to format the report and present the data in a readable format.

129) Can you convert list to Cross tab report?

Yes, to gain a different perspective on your data, you can convert a list report into a cross tab report.

Note: To convert list to cross tab report minimum 3 report items required.

130) Can you converting to repeater?

Only a list report can be converted into a repeater. You cannot convert a cross tab or chart into a repeater.

131) What is a Calculation?

According to the report requirement a calculation needs to be done using an object known as calculation object this process is known as OLAP Scrubbing.

132) What are the types of Calculations?

There are three types of Calculations:-

1.     Detail:- Calculation is performed for non-aggregated (Detail)  records

2.     Group: – Calculation is performed for aggregated records.

3.     Layout Calculation: – Calculation is performed to develop customized report titles, page No, etc…

133) To get the possible outcomes what types of variables you use?

Boolean variable: If there are only two possible outcomes, where the value will be YES or NO.

String variable: If there is more than one outcome, based on string values you will specify.

Language variable: Language variable when the values are different languages.

134) What is Master detail relationship?

A Master detail relationship allows you to merge a Master query and Detail query.

To link the Master query and the Detailed query there should be a common column between the query.

A Master detail report provides navigation form summary to detail.

135) What is Drill Through?

A Drill Through allows you to navigate the information from summery to detail.

A Drill Through id developed with two reports

1.     Parent Report which contains Summary Data.

2.     Child Report which contains Detailed Data.

Their should be a common column between the two reports.

136) Define Cross Tab Report:

Design a Cross Tab Report for comparative analysis. Cross tab report is created with 3   drop zones.

1.     Row

2.     Column

3.     Measure

137) What all the types of Cross Tab layouts Cognos Report Net supports?

Cognos Report net supports only Basic and Nested Cross Tab layouts.

138) Define a chart:

A Chart is a graphical presentation of business data.

A chart Report is designed for comparative Analysis.

139) What are the different types of charts?

1.     Column chart

2.     Bar Chart

3.     Pie Chart

4.     Line Chart

5.     Area Chart

6.     Point Chart

7.     Radar Chart

140) Define Collapse Group:

It will suppress (hide) the details of a grouped report.

141) Define Expand Group:

It restores the details. A report needs to be designed with group.

IBM Cognos BI Interview Questions – Cognos Connection

89) Define Cognos connection?

It is a web portal, which allows you to perform the following tasks.

1.     Creating the Reports

2.     Performing the scheduling tasks

3.     Deployment

Use to access all corporate data available in ReportNet.

90) What are the types of folders in Cognos connection?

Report can be organized by creating the folders.

Public Folders: Public Folders can be viewed by many users.

My Folders: Personal Folders that you can organize according to your preferences.

91) What is a job?

A group of reports and other jobs in Cognos that you run and schedule as a batch.

92) What is a job step?

The smallest part of a job that can be run separately. Usually, a job step is a report. A job step can also be another job.

93) Define content language?

The code or setting that specifies what language and regional preferences to use for the data that appears in a report.

94) What is deployment?

The process of moving a Reports, models to a different instance of Cognos. For example, you often create reports in a test environment and then deploy them to production.

When you deploy an application, you export, transfer, and import it.

95) What is the use of Schedule?

The schedule allows you to run a report at a later time or run it on a recurring basis.

96) What are the uses of Schedule Management tool?

*  Change the properties of a job

*  Modify a scheduled report

*  View the run history of a report

*   Run a scheduled entry once

*   Delete a schedule

97) What is a Sub Report?

A sub query that is associated with a main query. You can include several sub reports in one main query. You can have more than one query on a page.

98) What is a Cognos Script?

A Script visual basic used to write macros.

99) What is a Catalog?

A Catalog is a data base for a user to create own tables, joins, items, prompts, calculations and conditions.

IBM Cognos BI Interview Questions – Framework Manager

22) What is Framework manager?

Frame Work Manager is windows based metadata modeling tool for Cognos studios.

23) Why we need Framework manager?

To create the interface known as Project.

To create model the metadata derived from one or more data sources

24) How a project appears in a Frame Work manager?

A frame work manager project appears as a folder that contains a project file (.cpf) and the specific XML files that define the project.

25) What are the Building Blocks in Frame Work Manager?

The Query subjects are called Building Blocks in Framework Manager.

26) What are phases the workflow process consists in Framework Manager?

The workflow process consists of the following phases:

* Design and create project

* Prepare Metadata

* Prepare the Business View

* Create and Manger Packages

* Set Security

* Publish

* Manage the Project

27) How you set the security Framework manager?

To set the security you have to set as:

Ø  Define access to packages.

Ø  Create security filters

Ø  Define access to objects

Ø  Define package administration access

28) Define Name Space?

In security, a collection of user accounts and user groups from an authentication provider.

A Name Space is a container, which holds the Query Subjects.

A Name Space uniquely represents the Query Subjects.

29) Can you delete Cognos namespace?

No, we cannot delete Cognos namespace

30) What is the Cognos Namespace? And what it contains?

The Cognos namespace is the Cognos built-in namespace

It contains Cognos objects:

Groups

Roles

Sources

Distribution lists

Contacts

31) What are the Groups and Roles?

Groups and roles represent collections of users that perform similar tasks.

32) What is Folder?

A Folder is used to organize the Query Subjects.

33) Define Relationship?

A Relationship is a connection that explains how the data in one Query Subject relates to data in other Query Subjects.

34) What is a Package?

A container of model or set of models. Modelers create packages in Framework Manager to publish models to the Cognos connection.

35) What is Physical layer?

The Physical layer provides the physical query layer and is made up primarily of data source and stored procedure query subjects. It acts as the foundation for the presentation layer.

36) What is Presentation layer?

The Presentation layer makes it easier for report authors to find and understand their data. The Presentation layer is made up primarily of model query subjects that you create.

37) Define function set:

A function set is a collection of vendor specific database.

The Expression editor lists the function sets for all available vendors. However, you can restrict the function sets, so that it lists only the vendors that you want to use in your project.

38) What is a Metadata?

Data about data is called as Metadata. The Metadata contains the definition of a data.

39) What is a Project?

When we work with a framework manager you work in a project.

Project is a set of metadata organized for Report Authors according to the Business rules and Model.

A Project created appears as folder, which contains following files.

a.     Project Name .CPF (Cognos Project File)

b.    Model .XML

c.     Preferences .XML

d.    Custom data .XML
e.    archive-log.xml
f.     log.xml
g.   session-log.xml
h.   session-log-backup.xml
i.   IDLog.xml

The files in the folder are unique to that Project.

40) Define Publish?

To transfer all or part of a Framework Manager model to the Cognos connection, so that report authors can use it.

41) What a Project contains?

A.    A Model

B.    Namespaces

C.    Data sources

D.    Parameter maps

E.    Packages

F.    Folders

G.    Query Subject

H.    Query Item

I.      Relation ship

42) What is a Model?

A model in Frame work manager is a business presentation of the structure of the data from one or more databases.

Or

A model is a set of related query subjects and other objects.

43) What are Dimensions?

Dimensions are categories by which summarized data can be viewed.

44) What are Confirmed Dimensions?

The Dimensions which are reusable and fixed in nature.

45) What are Fact Tables?

A Fact Table is a table that contains summarized numerical (facts) and historical data. This Fact Table has a foreign key – primary key relation with a dimension table.

46) What are the types of FACTs?

The types of Facts are:

1.     Additive Facts: – A Fact which can be summed up for any of the dimension available in the Fact Table.

2.     Semi-Additive Facts: – A Fact which can be summed up to a few dimensions and not for all dimensions and not for all dimensions available in the Fact Table.

3.   Non-Additive Fact: – a fact which cannot be summed up for any of the dimensions available in the Fact Table.

47) Define Query?

A description of the data to be retrieved from a database.

48) Explain about Data Source Query Subject?

Data Source query subjects contain SQL statements the directly reference data in a single data source. Frame work manager automatically creates a data source query subject for each table and view that you import into model.

49) When the default data source query subject is created?

During the initial metadata import operation, a default data source query subject is created for each object you select (for example table) and creates the default relationships between query subjects for you automatically.

50) How can you edit the data source query subject?

i.    Apply or embed a filter

ii.    Apply or embed a calculation

iii.    Insert a Macro

iv.    Insert a data source reference.

v.    Change the SQL type.

vi.    Change how SQL is generated.

These all are we can find it in “Edit definition window”.

51) What are the types of SQL?

SQL is the industry language for creating, updating and querying relational data base management system. Types of SQL.

1.     Cognos SQL

2.     Native SQL

3.     Pass-through SQL.

52) Define Cognos SQL?

By default Cognos Frame work manager uses Cognos SQL to create and edit Query subjects.

Advantages:

1.     Can contain metadata from multiple data sources.

2.     Have fewer database restrictions

3.     Interact more effectively with Cognos applications.

Disadvantages:

You can not enter nonstandard SQL.

53) Define Native SQL?

Native SQL is the SQL, the data source uses, such as Oracle SQL, but you cannot uses Native SQL in a query subject that references more than one data source in the project.

Advantages:

1.     Performance is optimized across all related query subjects.

2.     You can use SQL that is specific to your database.

Disadvantages:

1.     You cannot use SQL that the data source does not support for sub queries.

2.     The query subject may not work on different database type.

54) Define Pass-Through SQL?

Pass-Through SQL lets you use native SQL without any of the restrictions the data source imposes on sub queries.

Advantages:

1.     You can enter any SQL supported by the database.

Disadvantages:

1.     There is no opportunity for Frame work manager to automatically optimize performance. The SQL may not work on a different data source.

55) What are Query Processing Types?

There are two types of query processing.

1.     Limited Local: The database server does as much of the SQL processing and Execution as possible. However, some reports or report sections use local SQL processing.

2.     Database only: The database server does all the SQL processing and execution with the exception of tasks not supported by the database. An error appears if any reports of report sections require local SQL processing.

56) What is Query Subject?

A Query Subject maps to the table in the database.

A Query Subject uses an SQL to retrieve the data from the data source.

A Query Subject is also known as Business View.

57) What are the types of Query subjects?

1.     Default data source query subjects

2.     Model Query Subjects

3.     Stored Query subjects

58) What is a Query Item?

A query Item maps to columns to the database table. A Query Subject contains Query Items.

59) How can you restrict the tables retrieved by a query?

By setting governors we can restrict the tables.

60) What is meant by Governors?

To apply privileges and restrictions for a user class. Governor settings are used to set restrictions on queries by user class, such as

1.     Sorting on non-indexed columns

2.     Outer joins

3.     Suppress duplicates

4.     Cross-product queries

5.     Retrieval of large text items

6.     Number of records

7.     Number of tables

8.     Query execution time

61) What is a User Class?

A defined group of users with the same data access needs privileges and restrictions.

62) What is Scrubbing at Project level?

According to the report requirements derive the new Items with the help of existing Query Items. This process is known as Scrubbing at Project level.

63) How can you add the calculated data to a report?

a. Summarize existing detail values predefined summary functions (such as sum, avg)

b.    Create new report items using data from existing report items.

c.     Use model calculations provided in the package.

64) What are the sources to create new Query subjects?

A new Query Subject can be created from the following sources.

1. Model (Query subjects & Query Items)

2. Data Sources (Tables and Columns)

3. Stored Procedure.

65) What is Multi Database Access in Cognos ?

In Cognos ”Framework Manager” a Project can be created from multiple databases. The databases can be either homogenous or hydrogenous.

66) What are Parameter Maps?

A Parameter Map is a key value pair used in creation of conditional Query Subjects.

A Parameter map sub situates the values at the query at runtime.

67) What is the default formats of reports?

HTML, PDF, CSV, and XML.

68) What are the different ways to modify multi lingual metadata?

1.     Macro :- To modify dynamically

2.     Directly

69) What is a Macro?

A Macro is a run time object, which uses the parameter maps and session parameters in developing the conditional query subjects.

70) What is usage property? And what are different settings to set usage property?

It identifies the intended use for the data represented by each query item.

It determines the aggregation rules of query items and calculations.

The different usage property settings are:

* Identifies
* Fact
* Attribute
* Unknown

71) What are the types of filters in Framework manager?

In framework manager we can create two types of filters.

1.     Stand Alone Filters.

2.     Embedded Filters.

72) Define Model Filter?

A filter is a condition used to restrict the data displayed in the Report.

Model filter are reusable.

73) Define Data restriction:

A filter is a condition, which restricts the amount of data displayed in the report.

74) What is condition?

An expression that yields a Boolean value. Conditions are used in query expressions, query filters, and Boolean report variables that can be used for conditional formatting, styles, data sources, layouts and blocks.

75) Types of Conditions:

Conditions are of 2 types:

1.     Static Condition: – Condition doesn’t change whenever you run the report.

2.     Dynamic Condition: -A condition can be keep on change whenever you run the report.

76) What are the types of filters in Report Studio?

In the report studio we can find two types of filters.

1.     Tabular Filters:- These filters are two types

a.     Summary Filters

b.    Detailed Filters

2.     Grouped Filter

77) What is a Loop?

A Loop is a closed path which is resulted due to joins.

A Loop causes performance degradation of query execution and wrong data will be displayed in the report.

A Loop can be resolved by creating the short cuts (Alias).

78) What is Alias Table?

An alternative name for a table generally used in self joins.

79) What are the uses of Alias Table?

The uses of Alias Table are:

1.     To resolve the Loops.

2.     To create self-joins with in a table.

3.     To provide alternate join structure.

80) What is Associated Data Item?

A Data Item linked to the group data item. Associated data item suppress duplicate data values but do not generate a control break. The associated column displays only one data values for the group with which it is associated.

81) What is Automatic Association?

The group association of a newly created summary. The location of the group where you create the summary determines its automatic association.

82) What is an Ambiguous Relationship?

An ambiguous relationship is where there are multiple relationship paths between one or more query subjects leaving multiple options for how to write a query.

83) What is a Join and types of Joins?

A Join identifies the columns from one table that are used to link to another table.

A Join is commonly formed by a foreign key definition within the database.

Types of Joins:-

1.     Equi-joins

2.     Non equi-joins

3.     Outer joins

84) What is a dimension?

A broad grouping of descriptive data about a major aspect of a business, such as products, dates, or markets. Each dimension includes different levels of members in one or more hierarchies, and an optional set of calculated members.

85) What is a Confirmed Dimension?

If a Dimension is connected to multiple fact tables then it is called as Confirmed Dimension.

86) What is a Junk Dimension?

A randomly used dimension is Junk Dimension.

87) Define Surrogate Key?

It has system-generated artificial primary key values, which allows to maintain historical records in the Data Warehouse more effectively.

88) What are the User Interface Components?

Ø  Cognos Connection

Ø  Report Studio

Ø  Query Studio

Ø  Frame work manager

Ø  Cognos configuration