Visualizing Data Using IBM Cognos Map Manager

IBM Cognos Map Manager  Tips

August is here. Finally, it’s time for that long awaited summer road trip. It’s great fun to pull out the map and choose your exciting destination. Maps are great visual aids. They’re a great way to add pizzazz to your Sales Region reports over the standard layout of columns of numbers. But wait, your Sales Regions are comprised of multiple states. So, you can’t use one of the canned maps that IBM Cognos BI offers. Despite this glitch, creating custom maps for use in Report Studio is really quite easy to start visualizing data using IBM Cognos Map Manager.

Before the fun begins, check with your system administrator as to where on your network the IBM Cognos Map Manager program resides and that you have administrative privileges. The path should be the same as the IBM BI product installation. Knowing the right path will be essential when saving your custom map.

I know my company's sales regions are within the continental United States. After opening IBM Cognos Map Manager from IBM Cognos Connection, I begin building my custom map by opening the file for the 48 states of the continental US. To prevent accidentally overwriting the canned map, I immediately do a File Save As. In order for the map to be viewable in IBM Cognos Report Studio you’ll need to make sure the map in saved to the right location. Look for the Maps folder within the Cognos installation directory and save your file as a .cmf file there.

I’m saving my file as
C:Program FilesIBMCognosC10MapsSalesRegions.cmf

I have four separate sales regions in my company. So, I’ll be creating four custom layers. To create a custom layer in IBM Cognos Map Manager go to Tools then Define Custom Layer (from the menu bar). Next, click on the Add button in the Define Custom Layers dialog box. After I name my layer with the name of my sales region, I click on the Features tab and add the states included within that region. When I finish creating my four custom layers representing my four sales regions, I save the file and can close out of IBM Cognos Map Manager.

Now I want to display my customized Sales Regions map in my Sales by Region report. I open the report in IBM Cognos Report Studio. From the toolbox tab I drag the Map object over to the Page area. Then the Choose Map dialog box opens. You can see your Sales Regions map as one of the options to choose. Notice that your four sales regions are listed under the Display Layers. Now, Click on your Sales Regions map and all four of your sales regions. Choose (None) for the Regions layer since we want our map to show the four geographical sales regions.

ibm cognos map manager

Next, Click OK and run your report. Viola! Cognos Viewer displays our color coded customized Sales Regions map.

ibm cognos map manager

Look for a future blog on drilling through to detailed sales reports from your customized map. In the meantime, take out your road map and enjoy your summer vacation travels. If you have any question or need assistance with IBM Cognos Map Manager, or Cognos BI, contact us at Services@lodestarsolutions.com.

Calculating Prior period in Cognos BI

Calculating Prior period in Cognos BI

Have you ever needed prior periods or last period within a report? For financial reporting, these calculations are a must and also a headache for report writers. This is the first issue you have to contend with when calculating last and prior is current. I know that line seems strange, but in order to calculate the prior period or the last period, you have to know that current period. Now, you can take the easy way out and ask the user to tell you the current period via a prompt, but what happens when the report has to be run on a schedule and you can’t rely upon a user?  See … you need to calculate the value.

Step one – Calculate the current date

Date prompting is pretty straight forward in IBM Cognos Report Studio and there are a bunch of functions available to manipulate a date field. If you want to get the current date, you will use the current date function. It looks weird when you create data items based on the current data function because it creates what looks like a hardcoded date. However, it is not hardcoded but will change each day the report is run.  More importantly, if you try to type in the exact same thing into the data item and expect the date to update when the report runs…get ready to stare at the screen, because it won’t work. I know it looks the same, but when you bring over the current data function, it does something in the background that you cannot replicate with a hardcoded date.

Step two – Get Year Part

Now that you have the current date, you may need to get the year portion of the date in order to calculate the fiscal year. This is really important when fiscal calendars do not match with the Gregorian calendar. If you have ever tried to extract the year part of a date within IBM Cognos, you know that it can’t really be done with a standard function. Before my dba’s get all worked up and correct me, let me just say that I am aware of a little trick … don’t steal my thunder I am getting there. What I was saying is that IBM Cognos functions for a Sql database does not have year as a function built in, however; you can still use it in an expression. To extract the year part out of a date field, use year(date value) and that will return the year part. If you need to add to the year, you can nest the add_year function as using a variation of this calculation add_year(-1,year(date value))

Putting it all together

Once you have the current date and can extract the various components of a date such as year, month, day; you can calculate last and prior very easily for your financial reporting. Of course, experienced dba’s will point out that we could build a flag within framework manager that would identify the current period, week, month, and year instead asking report writers to calculate it individually. In many ways, the dba is right and these values should be created in framework manager and available to report writers, but I am a realist and know what sometimes the right thing to do is not always the easiest or common thing done within a business.

For more information on this or other topics, email us at Sales@LodestarSolutions.com.

X