IBM COGNOS Report Studio query based report with optional prompt syntax

Many a times, we can’t wait till the metadata is created for every element that is there on the application, especially if your company is an extreme programming one. (Like the one I am working with.) So we end up writing query based reports in the COGNOS report studio.

The most common format that we find on the internet is:

               SALES_REP  in (#promptmany ('Rep_Prompt')#)

Where,

SALES_REP ----- Metadata element

Rep_Prompt   ------ name of the filter

As you know, using this kind of prompt results in a required prompt on the prompt page; but it is not always that we want the end-user to use these filters. What if we need to build a query based report in the report studio with an optional prompt from the query ……… :)

 Here is the format;

                SALES_REP in (#promptmany ('Rep_Prompt',' ','SALES_REP')#) 

I have built reports on COGNOS report studio based on this syntax and they works fantastically.

Where did I find this syntax ------ IBM COGNOS website.

IBM COGNOS Insight 10.2

Installed the IBM COGNOS Insight 10.2, feels like a DV (Data Visualizer) tool. Very neat and simple. I will post in some of my + / - on the product soon.

I must say that it was easy to install and play around.

IBM COGNOS - Playing around the system to solve the BLOB issue / error

IBM COGNOS 8 does not support text field on most versions. So what do we do ???? Play around the system to solve it :)


My scenario -- out of the 10 columns on the report, 2 were text fields. I built metadata for the 8 columns and wrote a SQL query with substr function for the 2 text columns. Then, I joined these two queries subjects with the common key.


One more thing, I used Pass Through SQL option. Reason, if we use the Native sql --- we cannot add a prompt on the sql. If we use COGNOS sql --- we cannot add the a substr function. So, we use Pass Through sql which allows you to add substr function as well as prompt on the query for better performance.


Here is an syntax / example from the IBM website



http://www-01.ibm.com/support/docview.wss?uid=swg21341148



Select * from GOSL.dbo.ORDER_HEADER 
Where ORDER_DATE = {ts # sq(prompt('TestDate', 'datetime') )# }

F Y I

http://www-01.ibm.com/support/docview.wss?uid=swg21341127

Problem(Abstract)

This document describes the difference between Native SQL, Cognos SQL and Pass-through SQL in Framework Manager and Report Studio (SQL Queries).

Resolving the problem

Framework Manager generates the most optimized SQL possible while preserving its functionality. In this way, Cognos SQL offers the most added value.

Native SQL is the SQL the data source uses such as Oracle SQL. You cannot use Native SQL in a model query subject that references more than one data source in the project.

Pass-through SQL lets you use Native SQL without any of the restrictions the data source imposes on sub queries. Pass-Through SQL query subjects are not processed as subqueries. Instead, the SQL for each query subject is sent directly to the data source where the query results are generated.

Further details can be found in the Framework Manager User Guide in the chapter on Modeling Relational Metadata.

I guess this helps.

IBM COGNOS error sqlPrepareWithOptions status = -69

The main reason on why we get this error is due to the fact that the data source is not able to convert THE data item into appropriate data type (conversion issue) that is needed for the calculation. Here is a link related to IBM

http://publib.boulder.ibm.com/infocenter/cfpm/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.ug_cr_rptstd.10.1.0.doc%2Fug_cr_rptstd_id60540rqp-def-0177.html

The attached image in this section shows an example where I faced this issue. Once I removed the casting on the date data item used in the filter, the issue got fixed. But let me tell you why this casting was required in the first place :). This particular report was built using a SQL query and not from metadata. Since SQL Server saves both date and time in a date column we had to cast it; later when a different user ran it on MySQL it threw an error. :)

Working with the IBM COGNOS Active Reports and the iPad

The new feature ‘Active Reports’ in the IBM COGNOS report studio is amazing. The best part of it, the entire active report is saved as .mht (MHTML --- MHTML, short for MIME HTML, is a web page archive format used to combine resources that are typically represented by external links (such as images, Flash animations, Java applets, audio files) together with HTML code into a single file.  ***source --- wikipidia)

Check out all the great demos out there on youtube.

1. Create a simple report, using all the sliding effects
2. Run as an active report and save the mht file locally
3. Now email this file to your iPad account
4. If you have the IBM COGNOS app installed, then when you click the mht file in the email it will automatically open in the app
5. Now play around with the report.

Unfortunately the ease of use with active reports is not great. It is the same problem that I faced while creating COGNOS 8 reports for the iPad; when you design for iPad on Active Report you need the report to be designed at pixel level. I think we need these BI tools to scale automatically for the mobile devices; I know it is tuff but we need to figure this out.

Anyways, I love the sliding effects; sliding prompts and the new graphs. Awesome!!!!. I have written about this once before in 2010 when COGNOS 10 was launched but implementing them in full fledge is altogether a different thing.

Moving along with BI reporting; I believe everyone is behind the buzz word ‘Big Data’. So am I :).

Should I start blogging about Big Data now????? The last couple of weeks have been crazy and the brain is full of analysis and reports now; from discussing data visualization, Big Data, SPSS, BI and ETL.  “So much to learn in such a short time.”

Implementing File Sharing on Network Drive or simply BI COGNOS on the Cloud

The basic concept in this blog that I want to emphasize is that, Business Intelligence (BI) should move to the Cloud  with minimal cost and in a cost effective way. F Y I --- IBM has already started selling COGNOS on cloud. 

Large institutes with multi networks / server environment face this issue of sharing report output among all the users across the company. IBM COGNOS 8 series functionality has the option of saving a copy of any report output in another file location outside of IBM Cognos 8 BI but only on the local machine.(this is my understanding and findings). To solve this issue, I started using 'Dropbox'. (I know I have been promoting Dropbox a lot) but we have a lot of other cloud based services like Google Drive, Box.net, Sky Drive etc that one can use. 

F Y I --- What is Dropbox ? (Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!) I love Dropbox because I have been using it for a while and have implemented this entire architecture using Dropbox. (for personal use)

Anyways, lets move on to the process of implementing the idea.

1. Install Dropbox on the server where IBM COGNOS has been installed

2. All the users who need these report outputs should create a Dropbox account

3. Now, the Dropbox on the server should share this folders through Dropbox to all the Users on Dropbox.

4. Dropbox has APIs and so does IBM COGNOS, together we can create an enterprise level security on these.

For file sharing, here are steps given by IBM; follow it to deploy.

        

Problem(Abstract)

By default, report output files are only saved in the content store. This document describes how to leverage IBM Cognos 8 BI 8.3 functionality to have the option of saving a copy of any report output in another file location outside of IBM Cognos 8 BI.

Environment

This functionality is only available as of IBM Cognos 8 BI 8.3

Resolving the Problem

To use this feature, you must first configure a root directory in Cognos Configuration and then an administrator must then set the file location in Cognos Administration. Steps below. 

How to Save Report Outputs to a File Location on a Per Report Basis

NOTE: When the property CM.OUTPUTLOCATION is set for the ContentManagerService service, all reports will be saved to the file location. Remove the CM.OUTPUTLOCATION parameter in order to save report outputs to a file system outside the content store on a per report basis only.

Steps:

Part A: Configure a root directory in Cognos Configuration.Create a shared directory (folder) in Windows (this will not work in Unix, or Linux environments).

Tip: Ensure that the directory is accessible to users and separate from the installation directory. For example, in a distributed installation, an archive folder such as\\servername\directory could be used.On the Content Manager computer, start Cognos Configuration.From the Actions menu, click Edit Global Configuration.In the Global Configuration window, click the General tab.For Archive Location File System Root, type a URI using the format as shown below:

file://\\servername\directory

Ensure that the the user starting the Cognos service has full permissions on the Share. Also ensure that the share is in the same domain as the Cognos server.

To confirm that the correct location will be used, click Test.

Click OK.

In the Explorer window, click Data Access, Content Manager.For the Save report outputs to a file system property, click True.

To test the connection to the report output directory, from the Actions menu, click Test.Ensure that you DO NOT have "localhost" for the server name in the configuration. Change this to either the hostname of the server or the fully qualified domain name. Leaving the server name as localhost will cause errors when attempting to save files to a network share.From the File menu, click Save.Restart the Cognos service to let the modifications take effect immediately

Part B: Define File System Locations in Cognos ConnectionFrom Cognos Connection, click on Launch (top right)

Click Cognos Administration

Click on Configuration tab

Click Dispatchers and Services (left side)

Click the Define File System Locations button ( top right)

Click New

Type the name (this name will be displayed when you use Run with options and then you would choose to save report -> Advanced options-> Save to file System-> click on Edit the options then you rename your report if you want. Under Location you will see the name of folder you created in this step)

Example: you can call it Test

Type description, screen tip

Specify the name of the folder that will show up under your shared folder. (This folder will show up first time you select RunWithOptions and save report to new Location). Example: name it CogTest

Select Finish. 

Part C: Saving a report to the file systemFrom Cognos Connection, go into the Run with options of a report

Go Advanced options --> Save to file System --> click on Edit the options if you want to rename your report. Under Location, you will see the name (Test) of the folder that you created in Part B step 7.

After running the report check the shared folder that was created in Part A step 1.

Combine these sections and you have your reports on the Cloud ...... yahooooooo!!!!!

Source: IBM, Dropbox & the internet.

IBM COGNOS down grading a report studio report from 8.4 to 8.3

This is a unique situation where we down grade a report from 8.4 to 8.3. Strange isn’t. There are some users who just don’t want to update themselves. On one side we are with the COGNOS 10 beta customers and on the other side we have these handfuls of Clients who are on 8.3 but want all the new reports that were built with 8.4.  We have manually downgrading these set of new reports and give them out through a package separately created on a 8.3 environment.

Anyways, here is a simple example with screen shots on how to do it.

1.        Open the report in a report studio (8.4)
2.        Go to tools and copy to clip board
3.        Open a notepad and paste
4.        On 8.4 reports we will see something similar to what is shown in the first screen shot.
5.        Delete everything after the language specification up to the ‘layouts’

Your report XML should be something like screen shot 1 initially and end like screen shot 2, now copy the data from the notepad and open it in 8.3 report studio.

Your report should working on 8.3 environments now J

IBM COGNOS 8 Report Prompt Page taking too long to load

Before going in to the solution we need to know few things:

1.      Prompts Reconciliation ---- COGNOS ensures the parameter definition matches the parameter usage. Parameters are defined in filters and calculations.

2.       The resulting parameters are used in prompts. A parameter definition contains several key items

a.       Cardinality – The number of input values that can be supplied to the parameter

b.       Discreteness – Whether an input value defines a single value or a value range

c.        Optionality – Whether a parameter is required or optional in the context of the filter or calculation.

d.       Data type – The required data type that is expected in the context of the filter or calculation to match other referenced data items or constants. These types can be one of Numeric, Date, Time, Date

e.      Time, Interval, String, or Member Unique Name (MUN).

Here is an example I found in the IBM COGNOS documentation ----- In order to perform prompt reconciliation, IBM Cognos 8 examines the queries to determine which parameters exist and their characteristics. The larger and more complex the queries, the longer this process can take.  A customer report in IBM Cognos 8.1 with over 200 queries has been noted to require over 40 seconds to show the first prompt page. Most of this time was spent on prompt reconciliation. 

(Content taken from IBM COGNOS knowledge forums)

I had similar issue, what I saw was that the query subjects (this is particular report) were very complex and had about 20 tables joined under it.  But the main culprit for this problem was due to a join that was created on the report with 2 such (complex) query subjects that are totally different with a dummy value and this new query had couple of filters. Crazy mistake and yes, the prompt was taking 40 seconds to load for us J

Solution --- > We created new metadata / query subject that was a combination of the 2 query subjects that we were joining i.e. basically we moved the join from the report to the framework and all the filters that we used on the report were made optional on the framework. (They were generic filters and so we did not have any issues). Now the prompt page comes up in a flash. :)