Prompts with Multi-Select and Conditional Cascading

Another issue with Cognos:

Today I learnt a new caveat with Cognos Report Studio. I had a business case where I need to create two prompts, One cascading onto the other.

But the trick was that Cascading had to be conditional. For ex: for a particular value (let’s say ‘YYY’) of Prompt1, Prompt2 should display otherwise it should be blank. And to make it more complicated, Prompt1 was Multi-select.

I am no expert with Cognos so I started with my usual instincts and implemented the situation as:
1. Created Prompt1 with Multi-Select option.
2. Created Prompt2 without the cascade.
3. Add a filter to the Query of Prompt2: ?filter1? like ‘YYY’

Being a database guy, I thought that this should work and to a extent it did. Issue was that when I select ‘YYY’ as the first selection and rest of the values after that then it worked fine.

Now the problem arises when ‘YYY’ is not the first selection. Then second prompt remains blank.

I couldn’t understand that what was the reason. I changed filter to ?filter1? like ‘%YYY%’ but still no result.

Then I thought that Cognos might not treat Like as databases do. So I tried CONTAINS but still the same.
Then I checked IN function and it works like a charm. ‘YYY’ IN ?filter1?

It’s nothing great but just a minor issue which might spoil your Friday evenings.

Hope this helps.

I’ll try to find exact difference between “LIKE” and “CONTAINS”.

Technorati Tags: Prompts,Conditional,Multi-select,Cascading,Cognos,Report,Studio,Value,IN,Like,Contains

How to reference Table data in Excel

 

Recently I had a annoying little issue in Excel. Every month I have to update a excel, which requires updating a source table with raw data in one of the sheets. Rest of the sheets require reference to this table for various calculations.

I’ll use an example to describe the issue more clearly.

I’ve to enter the raw data in the table defined at 5th row. Now there is a formula at Cell G3 which requires summation of 3rd column of the table. So the issue here is that every month raw data might change and hence the cell range will also change. So lets say for Month of June the formula range might be SUM(I6:I43) but for next month I have to update it depending upon the number of rows in the raw data.

So this is the problem and below is the solution for it.

First make the data table recognized as Table to Excel i.e. just making the Header as Bold won’t tell Excel that “Hey, I’m a Table and I have so and so Columns with these many Rows.”

So to do this, Select the Header and the row next to it then Select Insert Tab and Click on Table.

That’s it. Now Excel will recognize your table.

Now comes the second part “How to reference this Table into a Formula”.
For this Just type your regular formula, like "=SUM(“  and then add Table name and add “[‘ after it, like “=SUM(Rally[”.

As soon as you do this excel will show you all the Columns in that table,

Just select the relevant column and close it with “])” and that’s it. So in the end your formula should look like:

=SUM(Rally[SP''s])

I hope it’ll save some precious time of my techno geek community who always wants to do stuff in an optimized and efficient way.

Technorati Tags: Excel,2007,2003,SUM,Table,Reference,data,Function