Downgrading reports from C10 to C8

Here is a great article  by my fellow Axian employee Joshua Burkhow on how to downgrade reports.

How to easily downgrade a Cognos 10 Report.

Business Insight Advanced Limitations

Business Insight Advanced (BIA) is a great step forward for the Cognos Platform.  The intention is to pare down the number of studios employed by a deployment.  Having a go to studio for self-service will reduce user confusion as to how to use the multitude of Cognos studios.  However, BIA has some growing pains.  While there are some great leaps over Query Studio, there are some limitations to what BIA offers.

            1). Prompting limitations.

There are two prompting limitations: No feature that allows for manual entry of a value, and there is no display of the prompt selection on the report output.  The first missing feature is essential and available in both Query Studio and Analysis Studio.  When building a prompt around large value attributes like Sales Order Number or Product Code, scrolling is not an option.  (At the end of this post will be a tutorial around the conditions to which you can build  a manual enter prompt). A user will spend more time waiting for the list to populate and selecting an attribute than building the entire report.  The second missing feature while not essential is a basic element.  The report output should show the selected filtering criteria, full stop. 

            2). Dimensional Package Filtering.

The user must bring a member into the context filter or the SQL being passed does not contain a where clause with the filter criteria.  This is annoying because we have found that it is better to train users to use the Filter does not work when the grain is broken between the filter and query objects.  Example:  Filter on Child, Display Parent.  This query will not filter on Child.  When bring a parent into the query and a child into the context filter, Cognos removes the where clause in SQL.  This is a serious bug that needs to be addressed!

Creating a Write-In Prompt Filter

To create a prompt for a field like Sold To Number the user doesn’t want to scroll through a list of thousands to find a single Sold To Number.  A better solution is to build a report where the user can write in (or paste in) a Sold To Number. The following method only works for a relational package and a before auto aggregation filter.  If a before auto aggregation filter will not work for the query this method wont either. 

Create a prompt in the normal fashion.  A good tutorial on custom prompts in BIA is found here.  If you select the drop down box next to the search feature you will see this:

Simply deselect the Automatically Populate Values List.  You should see this now:

Click OK twice to create the filter.  When you run the report you will see this:

The user can now paste or type in a value.

Cognos 10 Upgrade Fix Pack 1.1

We installed Fix pack 1.1 into the Cognos 10 (C10) installation that we have.  It seems to be a fairly stable build for C10 and I would recommend jumping straight onto this fix pack.  All of the bugs that we experienced with the C10 upgrade have been resolved.  However, we have found a new issue that seems to have been caused by the new fix pack that will be detailed at the bottom of the post.

So far for us, fix pack 1 has fixed everything that we hoped that it would. 

1).  Attributes and Identifiers are (seemingly) randomly changing prompt types.  

     Fixed!

2).  Conditional formatting seems to be changing in C10.

     Example:  We have found several examples where reports in C10 did not display the correct conditional formatting.  Cells that should be green (indicating above a certain number) were red and vice versa.

     Fixed!

3).  Scheduled Reports (batch) failure.  

      Example:  Scheduled Reports are failure because end user is presented with more prompts when running report in batch.

      Fixed!

4).  Outputting a report to Excel failure.

     Example:  We have run into a problem where the report will run for the required time and then quickly flash the open, save, or cancel prompt.  If the user misses the prompt the report does not open.  This seems to be affecting all studios.

     Fixed!

What is happening here is that there is a nested subtotal problem.  Here we see a total for the salesperson and a total for the store.

The pink total should total all of the subtotals in gray.  Instead, it is double what it should be.

However, it is totaling the data items and the subtotals.  Here the Pink should show 1,863,495.  So, depending on how many nested subtotals there are the final total is getting doubled or tripled.  This change happened over the weekend that we implemented the fix pack.  We tried rebuilding this report from scratch to no effect.

I will post more when I know more about the solution to this problem.

Cognos 10 Upgrade

I am involved in the process of regression testing an upgrade from Cognos 8 to Cognos 10.  We moved onto C10.1 so we did not encounter any issues that were in that fix pack.  There has been some serious bugs that we have encountered in our upgrade.  As more companies are upgrading to C10 and it is important to understand what is outstanding still and what content will be in the next fix pack from Cognos.  This is especially true in the infancy of a major release.  IBM targets the most impact-full bugs for the first several fix packs and it is important to understand what to expect when upgrading.  Here is the link for the fix pack release schedule and here is the fix list for 10.1.1 the most recent fix pack. I will be listing in this post all of the problems that I have encountered during our upgrade process.

1).  Attributes and Identifiers are (seemingly) randomly changing prompt types.  
     Example: When a user selects to filter on a certain attribute (say an integer) will change to date type filter bringing up the calendar filter.  This has been extremely problematic for us and impossible to predict.  This problem seems to be a translation into XML problem.
     Fix:  This is supposed to be fixed in the Cognos 10.1.1 refresh package.  Meanwhile, you could hard code all attributes to a prompt filter.  This is a very time consuming fix.

2).  Conditional formatting seems to be changing in C10.
     Example:  We have found several examples where reports in C10 did not display the correct conditional formatting.  Cells that should be green (indicating above a certain number) were red and vice versa.
     Fix:  Again Cognos 10.1.1 refresh package is supposed to fix this.  I have not found a work around for this.

3).  Scheduled Reports (batch) failure.  
      Example:  Scheduled Reports are failure because end user is presented with more prompts when running report in batch.
      Fix:  We have not found a consistent fix for this but it is in the fix list for Cognos 10.1.1 refresh package.

4).  Outputting a report to Excel failure.
     Example:  We have run into a problem where the report will run for the required time and then quickly flash the open, save, or cancel prompt.  If the user misses the prompt the report does not open.  This seems to be affecting all studios.
     Fix:  I have not been able to confirm whether this is in the 10.1.1 fix pack.

These are the major bugs that I have encountered while performing 8.4 to C10 regression testing.  I would recommend that anyone that is about to upgrade to C10 wait until the 10.1.1 fix pack as the large problems that we have encountered are addressed. Some of these problem have workarounds but most do not.  The random attribute switching has been an extremely trying problem and has a very time consuming fix.  I will continue to update this post with any new problems as we move onto fix pack 10.1.1 and beyond.

?Prompt? versus #Prompt#

     Prompts provide the user to perform dynamic filtering, meaning the user can choose the filter criteria (within the parameters defined by the report writer) every time they run a report. This allows one report to span a wide range of user requirements. This is a basic feature that almost all reports use. The purpose of this post is not to explain how to build a prompt IBM has done a good job here and also starting on page 421 of the Report Studio (version 10) user guide. There are two different prompt mechanisms in Cognos: ?prompt? and #prompt#. What is the difference between these two?

     Let us start with #prompt#. This is actually used in Framework Manager and is really powerful when used in Report Studio. The syntax for using the prompt macro is: #prompt(ParameterName,Datatype,Defaultvalue,PreText,Source,PostText)#. Dissecting this: ParameterName is what you will call the prompt so that you can call back to it in other sections of the report. Datatype is the type of data that this prompt is utilizing; string, MUN (member unique name-used in dimensional reporting), numeric, token etc. Defaultvalue is the value it will pass if the user makes no selection. Pretext allows you to define something before the user makes a selection. Source is fairly obvious, it is the source. Posttext allows you to define something after the user makes a selection. #promtmany ()# can also be used to create a multi-selection dynamic prompt.

      Now onto ?prompt?. A great post by Tamas Simon showcases that this prompt is really just a string prompt macro. When you write ?prompt? really it is shorthand for #prompt(‘prompt’ , ‘string’)#. So, as a power user report writer I never use the ?prompt? method so that it is always clear to someone working on my report what exactly this prompt is doing.

      Using a prompt macro with Pretext and Posttext increases the flexibility of the prompt. Pretext allows you to manipulate the prompt before the user makes selection. This could be used to limit what the user select or to perform a function prior to selection. Postext does the opposite; perform a function after the user selects a parameter.

      Tokens are an incredibly powerful and useful tool in the prompt macro toolbox. Tokens enable the report writer to allow the user to select a parameterized filter. This means for a date filter using a token the user could select a max date, date range, or a specific date. Cognos has a great tutorial on how to set this here.

Dimensional vs. Relational

     There seems to be some confusion about the uses and benefits of dimensional versus relational reporting.  A lot of people seem to be on one side of the fence or another.  I myself see significant value in both dependent on the task or process in question.  Let me explain this a little bit better.  When performing ad hoc analysis nothing is better in my mind than dimensional tools.  Often though, complex reports are easier and faster to build when using a relational source.  I often suggest that when considering a new reporting environment both models are utilized.  This allows for flexibility for the end user.

Below is what IBM has to say on the matter:  See also here.

	Relational Reporting	Dimensional Reporting
Report type	Lists Crosstabs Charts Maps	Crosstabs Charts Maps
Models	Relational models	Dimensionally-modeled relational models Online Analytical Processing (OLAP) models
Data tree	package folder namespace query subject query item measure level	A customizable source tree, including the following: package folder namespace fact measure dimension hierarchy level level attribute member
Data items	Data items using relational and common constructs	Data items using dimensional and common constructs Extended data items
Inserting data	Report Studio groups data automatically by query item. For example, when you add the Product type query item to a list, all product types appear when you run the report.	Report Studio groups data automatically by level. For example, when you add the Product type level to a crosstab, all product types appear when you run the report. You can also create sets of members from different levels within the same hierarchy. For example, you can create a set of members that includes only Cooking Gear and Lanterns and than add that data to a crosstab. You can also choose to insert just the member, just the children of the member, or the member and its children.
Summarizing data	Headers and footers in lists Summary functions Member summaries Aggregate within detail	Member summaries Aggregate within set
Focusing data	Add a query item and then add a detail or summary filter to view only the data you want to see. For example, add the Quarter query item to a list and filter by Q3.	Add only the relevant members to an edge of the crosstab or to the context filter. For example, only add the Q3 member to your report.
Drilling	Drilling through by value	Drilling through by member Drilling up and down
Page and section breaks	Simple page breaks Page sets Sections Master detail relationships using parameterized filters	Simple page breaks Page sets Page layers Master detail relationships using parameterized edge or slicer expressions
Report Studio authoring mode	Professional authoring mode	Professional authoring model Express authoring mode
Alternative Studios	Query Studio	Analysis Studio

     When dimensional modeling, the modeler is building relationships between data sets AND parent/child relationships.  This means that a child can only have one parent, but a parent can have multiple children.  Example:  Parent is Northwest, the Children are Oregon, Washington, Idaho, and Vancouver BC.  When using a dimensional model Northwest revenue can be on a crosstab and then a user could drill down on Northwest to see the revenue for Oregon, Washington, Idaho and Vancouver BC.  Dimensional models expand functionality over a relational model.  Also, dimensional model really lend themselves to ad hoc analysis.  Being able to drill up and down a dimension is handy when a  user identifies a problem and needs to uncover the source of said problem.

    A relational model allows for a simple and flexible model.  It is easier to write a report with a relational model and it is easier for an experienced report writer to build a SQL query SQL to achieve very advanced reporting needs.  

    So, what do I prefer to write reports in?  It depends on what I am trying to achieve with the report.  Do I need create something very advanced where I need to do a little remodeling of my own?  If so, I will use a relational model.  Do I need to be able to create drill up and down capabilities?  Then use a dimensional tool. If I am doing strictly ad hoc analysis I would leverage a dimensional model.  If I just need to create a quick simple report I would use a relational model.  Both tools have their uses.

Launch Pages

It is often requested to have a launch page built where users can open up new reports, important reports, and links to other areas of the portal. I have found that the best way to accomplish this by building a custom report that will fit the bill.

To build a link that opens a new report in a specific studio with a specific package use the following html:

yourcognosaddress/cognos8/cgi-bin/cognosisapi.dllb_action=xts.run&m=portal/launch.xts&ui.gateway=yourcognosaddress/cognos8/cgi-bin/cognosisapi.dll&ui.tool=AnalysisStudio&ui.object=/content/folder[@name='Folder in Public Folders']/folder[@name='Next Folder']/folder[@name='Folder After That']/package[@name='Package']&ui.action=new

 Let’s break this down. You will use your own cognos address here.

yourcognosaddress/cognos8/cgi-bin/cognosisapi.dllb_action=xts.run&m=portal/launch.xts&ui.gateway=yourcognosaddress/cognos8/cgi-bin/cognosisapi.dll&ui.tool=AnalysisStudio&ui.object=/content/folder[@name='Folder in Public Folders']/folder[@name='Next Folder']/folder[@name='Folder After That']/package[@name='Package']&ui.action=new

 This is where you open a specific studio.

yourcognosaddress/cognos8/cgi-bin/cognosisapi.dllb_action=xts.run&m=portal/launch.xts&ui.gateway=yourcognosaddress/cognos8/cgi-bin/cognosisapi.dll&ui.tool=AnalysisStudio&ui.object=/content/folder[@name='Folder in Public Folders']/folder[@name='Next Folder']/folder[@name='Folder After That']/package[@name='Package']&ui.action=new

 Here is where you navigate through the folders to where the package resides. This is an annoying way to navigate through a folder path but it must be done.

yourcognosaddress/cognos8/cgi-bin/cognosisapi.dllb_action=xts.run&m=portal/launch.xts&ui.gateway=yourcognosaddress/cognos8/cgi-bin/cognosisapi.dll&ui.tool=AnalysisStudio&ui.object=/content/folder[@name='Folder in Public Folders']/folder[@name='Next Folder']/folder[@name='Folder After That']/package[@name='Package']&ui.action=new

 Here is where you name the package you would like to open.

yourcognosaddress/cognos8/cgi-bin/cognosisapi.dllb_action=xts.run&m=portal/launch.xts&ui.gateway=yourcognosaddress/cognos8/cgi-bin/cognosisapi.dll&ui.tool=AnalysisStudio&ui.object=/content/folder[@name='Folder in Public Folders']/folder[@name='Next Folder']/folder[@name='Folder After That']/package[@name='Package']&ui.action=new

Now you know how create a link that will open a new report with a specific package. This is useful if you have several packages and you want to make it easier for the users to choose the right package, especially when you have dimensional packages as well as relational. Often packages are contained in awkward locations and this provides convenience for the end user.

Please use comments

–the use and purpose of comments
I recently worked on a project where a report wasn’t performing like the business wanted it to perform.  The report was listing prior year actuals (sales) by rep and allowing the rep to plan for next year based off of those actuals.  The rep override table that was used to assign another rep’s products and either take the products away from the original rep or have both reps assigned to the products.  There were some other complications that made this process more complex, but the details aren’t necessary for this discussion.  The problem was, that the table wasn’t taking the products away from the original rep.

Discussions were had with the team that created the original solution and they decided that the solution worked according to the original specs they were given and that a significant amount of money would be required for them to go in and change it.  This meant that the solution was left for me to come up with and that I had to dig into the original SQL to understand why this wasn’t working.This happens more than I would like and happens in both large companies where a BI team bills the business for work and small companies where there is only a few IT employees that share work.

Here is where the discussion starts:  When I examined the code, there was no comments.  Because of this I spent about three times as much time trying to decipher what the code was doing and WHY it was written that way.  The solution was written with 8 outer joins of the same table, a very odd way of writing the code.  After spending a considerable amount of time deciphering the intent of the coder I understood why it was written in this unusual way.  However, if there had been just three or four lines of comment code explaining the intent of original coder I would have accomplished the solution in about a third of the time knocking several full days off of my workload.

Often the BI community thinks only of the actual BI tool (Cognos, Micro Strategy) as BI.  Really it is also the denormalized model that it sits on as well.  I can’t count the number of times that I have had to go into the SQL model to understand or change something.  BI systems are constantly evolving, just as business practices are constantly evolving.  It is for this reason that I want to discuss commenting.

There should be an intro comment:

- =============================================================================

– Object: PLN_ETL.SO_HIS_wPlngCalcs_v View

– Purpose: Provides Planning visibility to Sales Order Data with Configuration

– common calcuated attributes/quantities applied.

–

– Note: Column Aliasing as per naming provided by BusinessTeam in certain document

–

– Level of Granularity: SO_HdrNbr

– SO_ItmNbr

– SO_SchedNbr

– SO_HdrCrtDt

– OrigPhysSrcSysSK

– Sources:

– EIS.SO_HdrItmSched

– PLN_ETL.SO_DocTypeItmCatPlngConfig

– EIS.SlsOrgGPI

– EIS.ProdtAtSlsOrg

– PLN_ETL.SO_BusCalPlngConfig

– PLN_ETL.SO_RjctRsnPlngConfig

– EIS.SO_RjctRsn

–

– =============================================================================

– Date Version Who What

– ——— ——- ———— ——————————-

– 20-Feb-09 v1.0 Code Writer     Initial Creation

– 19-Mar-09 v1.1 Code Writer Added Support for Effectivity on Doc Type Item

– Cat Config.

– 20-Mar-09 v1.2 Code Writer Removed Open Internal Contracts from Net Bookings

– and Total Net Bookings Calculations.

– 29-Mar-09 Code Writer Pointed view to new EIS view as main source.

– 03-Apr-09 v1.4 Code Writer Added initial pass of additional attributes

– to support Cognos BI.

This is a great example of well commented introduction code

Also, there should be comments to help someone else understand what the code is accomplishing:

SELECT DISTINCT

MgmtReptoProdt.ProdtCd ProdtCd,

MgmtReptoProdt.SoldToCustNbr SoldToCustNbr,

MgmtReptoProdt.ShipToCustNbr ShipToCustNbr,

MgmtRepToProdt.SlsOrgCd SlsOrgCd,

MgmtRepToProdt.DistribChnlCd DistribChnlCd,

–Case Statement to select the override Sales Rep. The field will be null unless the join conditions are met.

CASE

WHEN SlsPlanRepOvrd_1.SlsRepUserId IS NOT NULL

THEN SlsPlanRepOvrd_1.SlsRepUserId

WHEN SlsPlanRepOvrd_2.SlsRepUserId IS NOT NULL

THEN SlsPlanRepOvrd_2.SlsRepUserId

WHEN SlsPlanRepOvrd_3.SlsRepUserId IS NOT NULL

THEN SlsPlanRepOvrd_3.SlsRepUserId

WHEN SlsPlanRepOvrd_4.SlsRepUserId IS NOT NULL

THEN SlsPlanRepOvrd_4.SlsRepUserId

WHEN SlsPlanRepOvrd_5.SlsRepUserId IS NOT NULL

THEN SlsPlanRepOvrd_5.SlsRepUserId

WHEN SlsPlanRepOvrd_6.SlsRepUserId IS NOT NULL

THEN SlsPlanRepOvrd_6.SlsRepUserId

WHEN SlsPlanRepOvrd_7.SlsRepUserId IS NOT NULL

THEN SlsPlanRepOvrd_7.SlsRepUserId

WHEN SlsPlanRepOvrd_8.SlsRepUserId IS NOT NULL

THEN SlsPlanRepOvrd_8.SlsRepUserId

ELSE SlsPlanRep.SlsRepUserId

END AS OverrideSlsRepId,

–assigns sales reps to specific products

FROM SCHEMA.RepToProduct

–Left Joins on Eight Ovrd_Table tables to account for all possible wildcard combinations. Left join to capture all products within a wildcard.

–specific assignments with no Wildcards

LEFT OUTER JOIN SCHEMA.SlsPlanRepOvrd Ovrd_Table1

ON Ovrd_Table1.SoldToCustNbr = 

RepToProduct.SoldTo

–Core Focus Wildcard

LEFT OUTER JOIN SCHEMA.SlsPlanRepOvrd Ovrd_Table2

ON Ovrd_Table2.SoldToCustNbr = 

RepToProduct.SoldTo

AND Ovrd_Table2.SlsOrgCd = MgmtReptoprodt.SlsOrgCd

AND Ovrd_Table2.GlblCatCoreFcsCd = ‘*WLD*’ –ProdtGPI.GlblCatCoreFcsCd Wild Card

This code tells the next person to read this code exactly why each coding decision was made.  More importantly, it saves the next person time and consternation when they have to decipher your code.