Master Detail Query

Hi,
Today i got a chance to create/modify one report. I need to add one Master/Detail functionality in that report.

Below are my learnings regarding Master/Detail report.

There are 2 types of Master Detail Query:

1) Filterized Detail Query
2) Parameterized Detail Query

Steps to Create Master Detail Query in Cognos ReportNet:

A master-detail query is an outer join. It is useful when you want to include ALL records in the master query regardless of whether there are matching records in the detail query or not. (These instructions are based on CRN V7 MR3.)
1. On a blank report page, drag in a new list item (called MasterQuery in this example). Populate it as needed.
2. Drag in a new list item (called DetailQuery in this example) and drop it into one of the columns of the MasterQuery list. Populate this list as needed. One of these fields (called ID in this example) must be related to an associated field in the MasterQuery list to link the two queries. This step must be done correctly to allow DetailQuery to be an available selection in Step 5 below.
3. On the Menu Bar, select Data > Master/Detail Relationships...
4. Select MasterQuery in the top listbox.
5. Add DetailQuery as a filtered query and set MasterQuery.ID=DetailQuery.ID. If DetailQuery is not shown as an available detail query selection, then double-check that you performed Step 1 correctly.
6. Run your report.

Some further notes:
1) You can add multiple detail queries to a single master query. You can enhance the appearance of the report by hiding the detail query list headers as desired.
2) When you have Stored Procedures in the back end, Filterized Detail Query won’t work in case of Master Detail Reports.
3) By Default in the window where you choose your detail query. It will list all the queries in the report which uses parameter.

Will meet you soon in my next blog with more learnings.

Bye

Master Detail Report - Error

Hi All,

Today i worked on the Master Detail Report. I got some error while creating and running the report. I am confused and validated the report. I got the follwoing error while running the report.

Cognos ReportNet Error:
Master – Detail Report
RSV-SRV-0036
Details: CCLAssertError:0:Fatal: CCL_ASSERT(m_nodeImpl != NULL);

Trace back:
WPBIBusMethod.cpp(201): CCLAssertError: CCL_CAUGHT: WPBIBusMethod::runRequest
WPReportExecutionMethod.cpp(185): CCLAssertError: CCL_RETHROW:WPReportExecutionMethod::checkRequestForExceptions
WPExecuteRequestThread.cpp(169): CCLAssertError: WPExecuteRequestThread::checkException
WPEngine.cpp(1028): CCLAssertError:
WPController.cpp(263): CCLAssertError: CCL_RETHROW: WPController::executeRendering()
LWDataRetrievalEngine.cpp(804): CCLAssertError: CCL_RETHROW: LWDataRetrievalEngine::iterateResultSets
CCLIDOM_Node.cpp(503): CCLAssertError: CCL_THROW:

This is because of not linking the Master and Detail Query properly. Later when i linked those 2 queries properly the error is resolved.

Thanks and Regards,
Soma

Cognos Interview Preparation

Hi All,
I am back. i thought of preparing for some technical interviews. Since i have been into IT field last 2 yrs, Its worth to get thorough in one. So i started of with cognos Reportnet. Following are sone Interview Questions i collected from net.

1) What is the difference between macros and prompt?
Macro No Idea, Prompt is to filter the data.
2) WHAT IS THE DIFFERENCE BETWEEN LIST REPORT AND CROSSTAB REPORT?
List report show data in rows and columns versus a crosstab report show data in grids with dimension in rows and columns and measures in cells or in the intersection points.
3) Give me some examples for Lifecycle reporting, I mean which lifecycle we will use for reporting?
There is no specific reporting lifecycle. We can generate the reports from datawarehouse/data marts or we can generate the reports directly from OLTP systems.
wt i mean generating reports from OLTP systems is Loading data into one system and generating the reports. But this is not recommended.
This will depends on the business.
4) How we check the errors before running the report?
Validate the report icon in the tool bar
5) What is the difference between group and association?
Group - It Eliminates the Duplicate values from the report break the columns it’s having a one to many relationship.
Association- It Eliminates the Duplicate values from the report and its having one-one relationship....
6) What is IQD? What is contained in IQD? How you create IQD in ReportNet Framework?
IQD Is impromptu query definition, the name it self indicating dat , it contains the SQL statement. To create the iqd in Frame work manager, Create a new query subject with required query items .Set the Externalise method property of that Query subject to 'iqd'.
While the publishing the package select the 'Generate the files for Externalized query subjects'. and publish it to the local machine, which can be used as data source for Transfromer model.
7) What is a loop in Framework manager?
Loop is an very dangerous exception in framework manager we can resolve the loop create alias table. loop Dispaly wrong results in the reportnet.
A Loop is a Closed Path Circuit.
Avoid loops:using shortcuts.we have ambiguous relationships types:hierarchical relationships,recursive relationships,multi-valid relationships.to avoid these relationships using shortcuts.
8) How to create cubes in transformer ?
9) How to create users and permissions in cognos?
Users and Permissions can be given in Access manager - Administration.
Individual users can be created using their names or their Ids (if any) in Access manager and then be given permissions.
This tool is loaded when you install Cognos 7.x in your machine.
In content manager--we have one component called cognos process manager.using process manager we can integrate with third party tools like LDAP or NTLM.we can not create users in reportnet directly.in real time we can use LDAP.
10) What s Cognos Visualizer and Cognos Scripting?

Cognos Visualizer is a tool for creating charts(bar, pie, histogram......) & maps using datasources(datafiles like mdc,iqd,csv,excel etc).

Cognos scripting is like a language, with this we will create macros, Macro is a set of instructions to run a report

Transformer Model files

Hi All,

I am new to Powerplay transformer. Since i have got some experience on the Other OLAP tool SSAS (SQL SERVER ANALYSIS SERVICES), I didnt find this tough enough to learn. I have learnt some primitive steps on how to build the cube in Powerplay but that is not enough for designing big cubes and not well versed on the design considerations. Sure i hope i will gain fluentzy as the day proceeds.

If you are worked in Powerplay Transformer, you may wonder why we need 2 formats of model files (.mdl and .py?) like me. I found out what are the advantages and dis-advantages of 2 model file formats.

If you come across the Cognos Transformer a OLAP tool to model your cubes, you will be familiar with two file types .pyi and .mdl. The base model for the Cube will be saved in these formats. I wonder why we need two formats to save the model? And what is the major difference between these 2 formats.

Today as I browsed through the help files of the cognos documentation, I come across a File named pp_trns_mdl.pdf (Transformer MDL Reference) which explains all about the MDL. I assure you that I not even looked into those files after I install cognos 2 weeks back .lol!

I just skimmed thru the first few pages of that document; I was absorbed by the answer which I was looking for? The first chapter itself explains about the difference between two formats.

Now let’s look at the different features of those two file type (which is in the File I mentioned):

.mdl – Features

1) You can open the Models saved in the .mdl format in the User interface or in the text editor.
2) The .mdl format is the plain text representation of the model that is compatible between the versions of Transformer.
3) It loads more slowly than .py? Format because it creates all of the transformer objects when it loads.
4) Models saved in the .mdl format are machine independent.

.py? – Features

1) The py? Format is the binary representation of the model.
2) It is not compatible between the versions of the Transformer.
3) It loads faster than .mdl format because the Transformer Objects are not recreated when the model reloads.
4) In general .py? Models are larger than .mdl files.
5) As you edit the model, the size of the associated .py? file increases because it maintains the information on the operations performed during model editing.
6) Transformer uses this information in the variety of contexts such as Client-Server Operations and incremental updates.

Tip provided in the same help file: Storing model operations can cause model fragmentation within the binary model. To eliminate this fragmentation save .py? models periodically in .mdl format.

Best practice: It is a best practice to save your model files both in .mdl and .py? formats.

bye..

Error in Drill though Report

Hi All,

I am trying to run a drill through report from a cube. We are passing a set of parameters from the cube to the report which includes date range. This error is specific when you use data range in Drill through reports.

Error:

RSV-SRV-0032 - Unsatisfied parameter values are preventing the report from running. It is possible that a query used to populate a Prompt control depends on a parameter from either the report or the model.

Solution:

When i tried with the solution in the KB link, it is resolved. The solution is casting the date filter of the drill through report to date type.

The following Cognos KB link will explain about this error in detail.

http://support.cognos.com/knowledgebase/googlesearch?load_kb_document=1&dr=kb1&uniqueid=126199

Error in Big PDF Reports

Hi All,

If you ever wonder what will happen if the reports are too big to be rendered in PDF formats. I faced this following error when i try to render a massive number of records in PDF format.

CCLSystemError:0:Fatal: CCLVirtualMemoryMgr::write failed. Error 0

I found out a solution for this in ITtoolbox in the following thread.

The rootcause of this error is - Report PDF is too long to render.

Meet you all with more errors...

Chart Baseline issue in Excel

Hi All,

I have posted one question one month before in the ITtoolbox forum regarding this issue.

Please find my post below.

Hi,
We are using Cognos Report MR1.3.
We have created a Combination Chart Report.
We are showing the baseline of the chart in the separate color.
When we render the report in PDF and HTML, i can able to see that baseline in the report.
But when i render the report in excel format, we are unable to find the baseline of the combination chart in Excel.
Please help us in this regard ASAP.

Later I found out that the base line in the charts are not supported in the excel format report.

I am able to see the baseline in PDF and html formats of the report.

I also found that cognos has some list of limitations which are not supported in the excel reports.

Your comments are welcome on this...

Model Connection Error

Hi All,

I am getting one error when i try to modify the report of Model1 but the model connection is pointing to some other Model.

Error: ‘RSV - DR - 0004: Unable to find the MetadataRequest connection string’.

Later i found out the cause and rectified by changing the model connection. Next time when you do any modifications between different environments, make sure model connections are correct.

Cognos Error - Exception Encountered when building the cube

Its been long time since i blogged here. Good to see you all again after a long time.

I am getting one starange error in the report - QE-DEF-0425 Exception was encountered while building the cube. We didnt get this error when devloping the report.

This error was not coming before, but it’s started coming suddenly now. The Document#: 1009022 in Cognos Knowledge Base explains the problem but does not give any appropriate solution. :-)

Please find below for the description and cause of this problem (I took from Cognos KB doc).

Description:
Intermittent error encountered when executing a cross tab report.
Error Message:
QE-DEF-0425 Exception was encountered while building the cube
RSV-SRV-0025 Unable to execute this request
Solution:
The maximum size for a measure in a crosstab and chart is 19 digits. You can use the maximum of 19 integer with 0 decimal precision. The default decimal precision is 7, so 100000000000 works but 1000000000000 would generate the error.
If default precision is already been set properly, increase the cgi script timeouts on the web server.
Steps:
1. Open CQEConfig.xml file from \configuration in a text editor. If the has an extension .sample, please rename it to CQEConfig.xml.
2. Change the following line:

to

Note: The value="4" may be set lower if needed.