Durable Model Issue With Upgrading

Recently, our company migrated from Cognos 8.4 to 10.1. We were not using a durable model in 8.4. After switching to 10.1, we switched the framework to be a durable model. This was all no territory for me (and our team), and one of the issues we encountered may be of help to you as well. Durable models are great for long term sustainability. As part of the migration, we migrated thousands of reports. For the most part, there was no issue found.

We did, however, encounter an issue that many newbies seem to encounter when switching to a durable model. Migrated reports built on a non-durable model do not automatically become durable after migrating to a durable model. This is important to understand, as it will save you much headache and time troubleshooting.

After the initial migration of reports, some members of our team decided to open the reports, update the reports to use fields from the new durable model, and then re-save the reports. This made them durable - in their minds. The next time a field label was changed (which is no big deal in a durable model), the reports would no longer run. They were broken by label changes.

Of course, panic set in. Why would durable reports break from a label change? All hands were on deck to see if the model was set up correctly. Did we miss something? After a while, it was revealed that the reports were originally created on a non-durable model. This was the key to everything.

Reports built on a non-durable model are always non-durable. A report has to be written on a durable model in order to be durable. Lesson learned for the whole team. Hopefully, our pain will save you in your future migrations.

Not familiar with durable models?

IBM has some good information about durable models here: http://www.ibm.com/developerworks/data/library/cognos/modeling/design/page496.html

Framework Manager - Relative Date Logic Continued

After my last post about relative dates to use in Framework Manager, I received a few requests for additional relative dates and also some questions for clarity. So, today, let's look at a couple of additional relative dates, along with some additional points of consideration.

First Day of Current Week (based on a Sunday through Saturday week) - cast(dateadd({wk}, datediff({wk}, 7, Current_Timestamp), 6) as date)

First Day of Current Month - cast(dateadd({mm}, datediff({mm}, 0, Current_Timestamp), 0) as date)

Last Day of Current Quarter - cast(dateadd({dd}, - 1, dateadd({qq}, datediff({qq}, 0, Current_Timestamp) + 1, 0)) as date)

Yesterday - cast(dateadd({dd}, datediff({dd}, 0, Current_Timestamp) - 1, 0) as date)

Today -  cast(Current_Timestamp as date)

Some additional points of consideration

While implementing these in the framework (thereby making them available as just data items to be pulled into a report) is the most ideal scenario, these functions can be used directly in a data item in a query if you would like. I personally use this relative date logic when producing reports for senior management. They typically want to see reports for last month, last week, YTD, etc. All of this logic allows you to incorporate this.

This logic may cause some problems if you mix it with pure Cognos functions when running large sets of data. It is important to always try to not mix and match functions. Use pure database functions as much as possible to save yourself some headache when running large reports.

All of the relative date functions presented in my last post and this post use a cast(date expression as date) function. If you would like to filter a particular data item using this logic, please also make sure your data item is cast as a date as well. Keeping the data types in sync will ensure proper results from Cognos. Filtering a date-time field with a date filter can produce unexpected results.

Enjoy the relative dates!

Framework Manager - Relative Date Logic

Cognos provides a way for you to add relative dates into the framework so a report author can just drag in the relative date fields on their reports. This is a great thing, but there are two major aspects of this design that must be considered. These two points of consideration are based on my experience with using SQL Server 2008 as the database.

First, the preferred logic should use database functions. A lot of blogs and websites say to use Cognos functions, but this is not a sustainable option. Yes, Cognos functions work great for small sets of data that will not cause Cognos to flip from Local Process to Database Processing. Everything changes, however, once the data flips to Database Processing. Large sets of data will tend to error out when it flips over ot the database due to a mismatch of functions.

Second, not all database functions can be used in the framework model. Framework Manager prevents the use of SQL functions that use an empty parentheses. An example of this would be the function GetDate(). This is an invalid function according to Framework Manager, despite the fact that it is an absolutely valid function directly in SQL. The solution for this is to find non-Cognos alternatives, that either have a value contained within the parentheses or don't use parentheses at all. A great alternative is to use the SQL function Current_Timestamp. Framework Manager sometimes will require you to put this in a separate data item and then reference it in your relative date logic, but it should work directly in your date fields as shown below.

Here are some examples for you to use if you also in a SQL Server environment.

Same Day Last Year - cast(dateadd({yy}, -1, Current_Timestamp) as date)

First Day of Prior Week (based on a Sunday through Saturday week) - cast(dateadd({wk}, -1, dateadd({wk}, datediff({wk}, 7, Current_Timestamp), 6)) as date)

Last Day of Prior Week (based on a Sunday through Saturday week) - cast(dateadd({wk}, datediff({wk}, 7, Current_Timestamp), 5) as date)

First Day of Prior Month - cast(dateadd({mm}, datediff({mm}, 0, Current_Timestamp) - 1, 0) as date)

Last Day of Prior Month - cast(dateadd({dd},-1,dateadd({mm}, datediff({mm}, 0, Current_Timestamp), 0)) as date)

First Day of Current Calendar Quarter - cast(dateadd({qq},datediff({qq},0,Current_Timestamp),0) as date)

Last Day of Current Calendar Quarter - cast(dateadd({dd},-1,dateadd({qq},datediff({qq},0,Current_Timestamp)+1,0)) as date)

First Day of Prior Calendar Quarter - cast(dateadd({qq},datediff({qq},0,Current_Timestamp)-1,0) as date)

Last Day of Prior Calendar Quarter - cast(dateadd({dd},-1,dateadd({qq},datediff({qq},0,Current_Timestamp),0)) as date)

First Day of Current Year - cast(dateadd({yy},datediff({yy},0,Current_Timestamp),0) as date)

Last Day of Current Year - cast(dateadd({dd},-1,dateadd({yy},datediff({yy},0,Current_Timestamp)+1,0)) as date)

First Day of Prior Year - cast(dateadd({yy},datediff({yy},0,Current_Timestamp)-1,0) as date)

Last Day of Prior Year - cast(dateadd({dd},-1,dateadd({yy},datediff({yy},0,Current_Timestamp),0)) as date)

First Day of Two Years Ago - cast(dateadd({yy},datediff({yy},0,Current_Timestamp)-2,0) as date)

Last Day of Two Years Ago - cast(dateadd({dd},-1,dateadd({yy},datediff({yy},0,Current_Timestamp)-1,0)) as date)

First Day of Three Years Ago - cast(dateadd({yy},datediff({yy},0,Current_Timestamp)-3,0) as date)

Last Day of Three Years Ago - cast(dateadd({dd},-1,dateadd({yy},datediff({yy},0,Current_Timestamp)-2,0)) as date)

First Day of Four Years Ago - cast(dateadd({yy},datediff({yy},0,Current_Timestamp)-4,0) as date)

Last Day of Four Years Ago - cast(dateadd({dd},-1,dateadd({yy},datediff({yy},0,Current_Timestamp)-3,0)) as date)

First Day of Five Years Ago - cast(dateadd({yy},datediff({yy},0,Current_Timestamp)-5,0) as date)

Last Day of Five Years Ago - cast(dateadd({dd},-1,dateadd({yy},datediff({yy},0,Current_Timestamp)-4,0)) as date)

First Day of Ten Years Ago - cast(dateadd({yy},datediff({yy},0,Current_Timestamp)-10,0) as date)

Last Day of Ten Years Ago - cast(dateadd({dd},-1,dateadd({yy},datediff({yy},0,Current_Timestamp)-9,0)) as date)

Default Prompt Value Using JavaScript

Cognos offers the ability to schedule a report to allow for automation. The schedule requires you to set the default values for the prompt. If you are running the report for static choices (i.e. the prompt requires the user to select the Customer's State), this can be an easy and straight forward task because the choice doesn't change.

What do you do when your prompts are more dynamic? For instance, what if you always want the default to be the second value in a value prompt drop down, and those values change each month? Here's a simple JavaScript (JS) solution to accomplish this.

You will need to add 3 html items to your prompt page for each prompt for which you would like to have the dynamic default applied. In this post, we're only going to do one.

Step 1 - Add an html item to the left of the prompt. Place the following in there:

<span id="promptname">

You can replace that with whatever name you would like to associate with this prompt. This name needs to be unique for each prompt to which you apply this method.

Step 2 - Add an html item to the right of the prompt. Place the following in there:

</span>

Step 3 - Add an html item to the right of the html item in step 2. Place the following in there:

<script type="text/javascript">

var theSpan = document.getElementById("promptname");

var theSelect = theSpan.getElementsByTagName("select");

theSelect[0].options[3].selected=true;

</script>

Notice that the name used in step 1 is used again here in step 3. This will not work correctly if they don't match. Also, the number in brackets next to the options (step 3, line 4) determines what item in the drop-down list will be selected. In this example, I used 3 to select the 2nd option in the list. You can change that number to select the appropriate option in the list as needed.

The end product should look like this - 

There you have it: default prompt value in 3 easy steps. Enjoy!

Active Reports Not Running in Internet Explorer 7 or 8

Having spent the last few years working in Cognos, I was quite excited to have the features and power of Cognos 10.1.1. Our company made the upgrade from 8.4.1 to 10.1.1 and allowed me to instantly dive into Active Reports. For those who may not know about Active Reports, there is some great information found here:  http://www-01.ibm.com/software/analytics/cognos/business-intelligence/active-report.html

One of the recent problems discovered with Active Reports is that when you click an Active Report link in Cognos Connection using Internet Explorer 7 or 8, the report does not run. This is a known issue with IBM (reference APAR PM53589).

There are 3 ways to get around this issue.

1. Upgrade to Cognos 10.2.0 - An upgrade is almost always the resolution recommended by IBM, even though it is rarely a practical option for companies.

2. Use a different browser - Firefox is the next best option. I have found it difficult in our particular environment to use any browser other than Internet Explorer (IE) to write reports in Report Studio. However, running reports has proven more stable and reliable in Firefox.

3. Save the report in Cognos Connection and then open it in IE - Though this is an option, it is still probable that you will encounter problems trying to download the file from Cognos Connection in IE. You should have no problem being able to open the saved report. If you need to download the report to your computer, and you receive an error message, try downloading it using Firefox.

4. Email the report - This option provides another workaround for downloading the file to your computer, regardless of your browser.

Of all the options, option #3 has been my preference since option #1 is not a possibility for me right now. These options will hopefully save you some time and frustration if you're in the same situation as me. Happy report writing!

Welcome to my new blog!

Have you found yourself spending hours trying to find a solution for Cognos that should be easy? We've all been there. Every BI tool has it's advantages and disadvantages. Cognos is no exception. Whether you are new to Cognos or have been working in it for a while now, there are so many things available that you may not know about. That's exactly why this blog is here.

     This blog is the result of many hours of trial and error related to Cognos BI tools. The majority of the tips and suggestions will be focused on maximizing the potential of Cognos 10.1.1, from Business Insight Advanced (BIA) to Report Studio (RS) and Active Reports. Tips will range from the simple to the complex and hopefully help you meet the ever challenging role of Report Author.

     So, feel free to bookmark this page and plan to come back frequently.