IBM COGNOS 10.2 iPad mobile report samples xmls and creating the Metric Control active report

The new IBM COGNOS 10.2 active reports and their mobile samples on the iPad are really fascinating to look at on what we can achieve with the new IBM COGNOS version.  To what I have seen and experienced with the creation of active reports; there are a lot of advantages and disadvantages depending on how we want utilize them.  Especially the color scheme and the new graph representation are good; where as the size and the filters options avaialble kills the active report on the other hand. We did find that it has a lot of restrictions on the way filters work. I will add in my opinion of IBM COGNOS active reports soon.

Anyways, I found this URL that leads to the IBM portal with all the report ppts, xmls and screen captures. Every time a new IBM COGNOS mobile sample is updated on the IBM COGNSO iPad app this page gets updated with the report specifications.

ftp://public.dhe.ibm.com/software/data/sw-library/cognos/mobile/All/index.html

The only thing to observe here is that, the ‘Control Metrics’ sample has lots of custom graphs / images that need to be added manually for now.

All you got to do is download the visualizations from 

https://www.analyticszone.com/homepage/web/visualizationsDownload.action 

and import them to your IBM COGNOS install location and you have them available for your active reports; just like any other custom report that we build with our own custom images.

F Y I --- The new IBM COGNOS 10.2.1 has improved icons and colors,  the feel is modern.

IBM COGNOS 10 Active Reports and the iPad IBM COGNOS app

Couple of weeks back I got a glance into the COGNOS 10.2.1 version that is yet to release and last week I saw an update to the IBM COGNOS app on iPad which had an extra sample report added to it - 'Control Metrics'  and some additional features like pinch and zoom; the 'to highlight, draw on the report' option...etc. It is just fantastic and amazing to see such small things added in to make a huge impact for the end user's usability. I have added a screen short of the sample report from IBM COGNOS iPad app.

I love the colors and the new graphs used to represent the data. I am sure months of work would have gone into creating these wonderful reports from top data scientist at IBM COGNOS.  The idea of including gestures and small animations to these Active Reports add the wow factor.

I should say, we had implemented the left panel using JQuery on the 8.4 report studio but now in IBM COGNOS 10 report studio, I am assuming it will be a built-in features. This shows that IBM COGNOS is working very closely with a lot blogger out there who add in sample XML showing their creative scripting on top of the IBM COGNOS report studio.

I looked into the  some of the sample report XML that I found online, the reports are really really complex. One important thing to notice is that, IBM COGNOS 10 Active Reports are designed for a certain set of reports where we got to show a lot of content at high level but not the general detailed list or cross tab data. There are some limitation like the filtering options but lets keep it a side for now.

Over the last couple of months, I have been working on creating some really cool reports with new themes and graphs.  Am very excited using this new version of IBM COGNOS 10 features. 

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