Cognos Report - move totals to top of list

If you create a list object in a Cognos Report with some simple attributes and measures, then you may want to add a Grand Total. By default it will look something like this...

If you want to move the Grand Total to the top of the report, then you need to follow these steps:

1. Go to Structure -> Headers & Footers -> List Headers & Footers

2. Tick ‘overall header’ so that new blank row appears

3. You may need to split the cells on this new row as it can appear as a single merged cell

4. Go to the old cell that contains the summary (at the bottom of the table)

5. Unlock cell using the padlock icon

6. Copy the text item containing the summary content

7. Paste into your new cell at the top of the table

8. Merge cells as necessary to get the look and feel you want

9. Go back to Structure -> Headers & Footers -> List Headers & Footers

10. Untick ‘overall footer’

11. The summary at the bottom of the table should now have disappeared

and you should have a list object like this.

To fully replicate the formatting of the previous Total row you may need to amend alignment, background, colour etc.

Indentation of hierarchy parameters in Reporting Services pickers

When the requirement is to have a nice indented Date Hierarchy picker in you Reporting Services report, along the lines of...

here is the technique.

1. Extend MDX statement

You need to extend the MDX query in the Dataset so that it brings in 'level' information. Something like:


WITH 
MEMBER [Measures].[ParameterValue] 
AS '[My Date].[Financial Year-Quarter-Month].CURRENTMEMBER.MEMBER_CAPTION' 
MEMBER [Measures].[ParameterLevel] 
AS '[My Date].[Financial Year-Quarter-Month].CURRENTMEMBER.LEVEL.ORDINAL' 
SELECT {
   [Measures].[ParameterValue], 
   [Measures].[ParameterLevel]} 
ON COLUMNS , 
DESCENDANTS(
   [My Date].[Financial Year-Quarter-Month].[All Time],
   [My Date].[Financial Year-Quarter-Month].[Financial Month],
   SELF_AND_BEFORE )
ON ROWS 
FROM 
   [My Cube]

Note that you'll need to have built a Hierarchy to hold the Year->Quarter->Month structures.

2. Add calculated field to Dataset

Call it ParameterCaptionIndented and the calculated field expression will be:

=Space(3*(Fields!ParameterLevel.Value-1)) + Fields!ParameterCaption.Value

3. Define parameter fields

Create a parameter to hold the values returned by your querySet the following properties:

Value Field = ParameterValue

Label Field = ParameterCaptionIndented

Hey presto, this should work. Can be used to do other hierarchies such as Organisation hierarchies as well.

Point in Time Reporting of metrics through Cognos

I'm relatively fresh to Cognos Reporting but this particular issue I can see arising in any technology stack. We were dealing with 'lifecycle' data so that customers go through a number of lifecycle stages. So for instance, they:

1. request a product
2. are approved for a product
3. receive the product

or..

1. request a product
2. get turned down for a product

or even...

1. request a product
2. are approved for a product
3. receive the product
4. Cancel the product

And for each of these stages, you will know the date on which that stage was reached. You end up in a potential state of confusion with this kind of data as the users may request metrics such as:

• How many customers were approved in March?
• How many customers were waiting for approval at the end of March?

With this kind of time series data the understanding can be subtle and users (and developers) can easily get confused. Of course, it depends on the definitions provided but generally for:

a) Customers approved in March

you would count the number of 'Approved' events that occurred in the month of March - relatively easy. The SQL would be straightforward and your report would just have a single Date Range Calendar widget.

     SELECT COUNT(*)

       FROM tFACT

       WHERE Stage = 'Approved'

       AND EffectiveFromDate BETWEEN '01-MAR-2012' AND '31-MAR-2012'

b) Customers were waiting for approval at the end of March

This is trickier, as you potentially have some weird cases such as where the Customer has been waiting for approval since February and has no activity in March. Having a Date Range calendar widget here wouldn't work as it wouldn't pick up these cases.

In such cases you need to consider ALL data up to the end of March and (not only that) work out what the last event was. This is, of course, not the same as the current event assigned to the Customer as lots of other events could have happened since then.,

To do this you need to use Ranking functions within Cognos. Within the Query Explorer, for your main query add an additional Data Item with an appropriate name such as 'Data Rank'. Then use the Expression Definitions as follows:

rank ([Effective From Date] DESC FOR [Customer ID])

then within the Detail filters set:

[Data Rank] = 1

[LifeCycle Stage] = 'Approved'

[Effective From Date] < pReviewDate

where pReviewDate comes from a single entry Calendar widget.

One gotcha is that the Ranking has to be done before the filters are applied. I managed to fix this by setting 

Application = Before Auto Aggregation

on the [Data Rank] filter.

This worked a treat and I extended the report so that I could input the Lifecycle Stage I was interested in. This meant I could drive the report and make it show me all the Customers at a given Stage at any moment in time. 

The next stage would be count how many had reached a certain stage Cheers.

Multiple dates in an Analysis Services Cube

This was a requirement for me around 2 years ago, and I always thought worth storing the experience in case it came around again. Essentially the client wanted to analyse by 5 different dates. Imagine 'Delivery Date', 'Order Date' etc etc and you'll get the idea...

Possibly doesn't sound that bad but the killer requirement was to have a picker on the SSRS reports where you chose the date you wanted for that particular analysis. Without this you'd potentially end up with 5 separate calendar widgets on your report. Maybe sounds innocent enough but did prove very difficult. After a bit of work-shopping and brainstorming the solution we finally came up with was to:

• Create 5 separate views of the Fact data, identical except for 2 columns
• Have a column called 'Date Type' that has values 1-5, a different value in each view
• Have a generic date in the views called 'Analysis Date'
• This date was mapped to a different source date depending on the value of 'Date Type'

We also created a simple little dimension with 2 columns like..

Date Type Code || Date Type

1                            Delivery Date

2                            Order Date

etc...

The trick then was to create 5 partitions in the cube and load the Fact data 5 times. Depending what value of 'Date Type' the user chooses then they are analyzing with a different date. Its pretty clever when its working and the users were a happy bunch.They were knocking up Excel books with pickers and we could use them in SSRS as well.

One gotcha of course is that they MUST choose a value of 'Date Type' or all the measure values will be a factor of 5 to big!! We got round this by placing a default value in the cube dimension - choosing the most popular 'Date Type'. This isn't the only drawback though, as you are also loading the data 5 times and depending on the cube size this could cause you problems. 

So it did work for us but eventually we were able to solve the problem alot of more efficiently using Dynamic MDX. I'll cover that in a separate blog, I can see this technique here being used again though as the users did like the way the pickers worked. Dynamic MDX has its own overheads and this way you can put the MDX directly in the reports.

TNS NAMES EDITOR

Working in an environment where new Development and Test Oracle databases are created on a routine  basis means I tend to maintain my own local copy of tnsnames.ora and sqlnet.ora.

People working in other locations then tend to send you new entries in an email or even their complete tnsnames files. You can then end up trying to do a patchwork job of adding their entries into your own file.

I consider myself reasonably knowledgeable in such things and rarely have a problem but got into a tangle when I added a new entry and started getting TNS-12533: TNS:illegal ADDRESS parameters 

Fiddling around with tnsping and surfing Oracle forums led me to the conclusion that there was a typo or bracket missing somewhere. But then I read someone make a fairly valid point, which was to use the actual editors provided by Oracle!

Although I knew they existed, I rarely use them. But by using 'Net Manager' in the 'Configuration & Migration Tools' section of your Oracle Client install you can effectively manage both the sqlnet and tnsnames file. (Obviously this isn't exactly news to most Oracle Pros!).

When I initially used it to open my tnsnames file, it showed no entries in the Service Naming section which confirmed there was some sort of corruption. I ended up re-adding the entries using the editor and hey presto - all worked fine!