Tips and Tricks

[jessimica602]

Hey all!!

I am building a reference guide for people in my company, many have never used Cognos and are just now beginning to learn basics. What are your best tips and tricks you wish you would have known when you first started working in Cognos?

Thank you!!!

[MFGF]

Hey all!!

I am building a reference guide for people in my company, many have never used Cognos and are just now beginning to learn basics. What are your best tips and tricks you wish you would have known when you first started working in Cognos?

Thank you!!!

I'll kick off proceedings here, although when I first started working with Cognos tools it was over 30 years ago, and they were unrecognisable from today's Cognos Analytics :)

When authoring a report and defining a query calculation, there are two timing points at which the calculation could happen - before the automatic aggregation, or after the automatic aggregation. This is important to know, because Cognos automatically groups and aggregates detail rows in a report, and for some calculations you will get different answers depending on the timing.

Consider the following data set:

PRODUCT_LINE      QUANTITY   PRICE
Camping Equipment   20      5.00
Camping Equipment   10      2.00
Camping Equipment   8      10.00

If you were to report on this in a Cognos report, by default you would see a single grouped and aggregated row in the report

Camping Equipment   38      17.00

Now consider adding a calculation to derive the total price. You'd want to multiply the QUANTITY and PRICE items to get the answer. But you would get different results if the calculation was done before aggregation or after aggregation. Before would look like this:

PRODUCT_LINE      QUANTITY   PRICE   TOTAL_PRICE
Camping Equipment   20      5.00      100.00
Camping Equipment   10      2.00      20.00
Camping Equipment   8      10.00   80.00

The TOTAL_PRICE results would then be aggregated to produce a row that would be

Camping Equipment   38      17.00   200.00

However, if the calculation was done after aggregation instead, it would use the aggregated values of QUANTITY and PRICE to give this:

Camping Equipment   38      17.00   646.00

So, as a report author, how can you control the timing of a calculation so you can drive the appropriate result? Unlike when creating a detail filter, there is no radio button in the calculation dialog to specify before or after aggregation.
The trick to success is knowing that bringing in items to your expression from the source directly (package or data module) will result in before-aggregation timing. Bringing in items to your expression from your existing query will result in after-aggregation timing. It is easy to differentiate between them just by looking at the expression - items from the package/data module will have a fully qualified name [Namespace].[Query Subject].[Query Item] whereas items from the query will have a simple name [Query Item].

So in this case, a before-aggregation calculation would look like

[GOSALES].[SALES_FACT].[QUANTITY] * [GOSALES].[SALES_FACT].[PRICE]

whereas an after-aggregation calculation would look like

[QUANTITY] * [PRICE]

Cheers!

MF.

[dougp]

It depends...  Define "basics".  Do you mean people using reports, or creating reports.  At what level of complexity do you recommend they contact you for report development work?  (hint:  That's a tip/training point.)

I treat different users differently.  I occasionally compress 1-2 weeks of training for a quick "what's possible" presentation.  I cover the UI (buttons on the Welcome screen, the switcher, and common buttons, personal menu, etc.); the folder structure; when to [not] use My Content (because if you're gone, that report won't help your boss or coworkers); what each of the tools (reporting, dashboarding, etc.) does; shortcuts, report views, schedules; and try to get into basic report development.  It's a whirlwind tour intended to show what can be done, not not how to do it.  I don't slow down.  I don't wait for questions.  They can ask questions or schedule a deeper dive after the initial presentation.  I just don't want my users, who have real work to do, to spend days or weeks in training and never use 95% of it.  Most users are amazed.  They had no idea Cognos could do all that.  Some of my users stop there and have me do all report development.  Others know Cognos almost as well as I do now -- some parts better.

Regarding report development complexity -- I tell them to spend no more than 20 minutes trying to find the answer to something that has them stuck.  20 minutes of effort is worth it because they may learn something (even if only how to form a better Google search expression).  After 20 minutes, contact me.  I've probably seen it before and we need to keep you moving.

I have found that some basic report standards help:
Prompt pages enable you to create a prompting system that works well.  The auto-generated prompts are not always helpful.
Whether in a report footer (last page) or a page footer, always include two things:
    All parameter values.
    The path to the report.
(Since we went self-service, I have over 18,000 reports.  Getting a question like, "Why doesn't my expenditure report work?" is not helpful.  Which of the 2200 expenditure reports are you asking about?  What parameters did you use?  A screen cap or attached report output with the right info helps reduce frustration.)

Other than that, it really depends on how you expect your users to behave.

I created a wiki (internal only, sorry) with documentation on most aspects of Cognos Analytics.  I find I'm the only one who uses it.  But it's still handy because there are things I don't do every day.

[jessimica602]

The trick to success is knowing that bringing in items to your expression from the source directly (package or data module) will result in before-aggregation timing. Bringing in items to your expression from your existing query will result in after-aggregation timing.

Whelp.... I'm learning things too it turns out....Holy buckets.... I knew there was a difference in pulling directly from the package vs pulling from query....It all makes sense now! I am definitely adding this to the documents of need to know things.

Thank you for spelling it out and giving an amazing tip!!

[jessimica602]

It depends...  Define "basics".  Do you mean people using reports, or creating reports.  At what level of complexity do you recommend they contact you for report development work?  (hint:  That's a tip/training point.)

I have found that some basic report standards help:
Prompt pages enable you to create a prompting system that works well.  The auto-generated prompts are not always helpful.
Whether in a report footer (last page) or a page footer, always include two things:
    All parameter values.
    The path to the report.
(Since we went self-service, I have over 18,000 reports.  Getting a question like, "Why doesn't my expenditure report work?" is not helpful.  Which of the 2200 expenditure reports are you asking about?  What parameters did you use?  A screen cap or attached report output with the right info helps reduce frustration.)

Other than that, it really depends on how you expect your users to behave.

I created a wiki (internal only, sorry) with documentation on most aspects of Cognos Analytics.  I find I'm the only one who uses it.  But it's still handy because there are things I don't do every day.

It depends on my training too; I have about 15 people learning true basics as in how to run reports, I have another 10 people actually learning basic builds (as in heres a simple list report) and of that 10 I have 3 who want to learn a deep dive. I am currently the only person in the company with more then 2 years experience with Cognos and even my skills are not anywhere close to many people on here because until 4 months ago my job was payroll only (what I did learn from Cognos was driven by a need to improve payroll), but I have moved into an HRIS/Reporting SME type role.  I am now overwhelmed with the requests and people wanting to learn that I wanted to bring a few people up to my level so atleast they won't be coming to me for the easy things. I do however want to set them up for success so knowing those "man I wish I would've known" things is so helpful for everyone. I am also putting together a "wiki" out of OneNote for everyone to reference things.

I love prompt pages to an extent (I think I need to learn more about them and parameters) but I do have a whole section on building simple ones. Do you have an particular tips on how to make them more amazeballs?

I really like that page/report footer idea!!!! Our cognos is getting a bit discombobulated so that is a great way to help keep track of what they are talking about when they have questions or referencing certain reports.

I have my own personal "wiki" for long case statements and nifty expressions; if I wrote it once I don't want to do it again and I'll forget what I did so keeping those in OneNote keeps it all organized and able to reference super fast.

Thank you!!!!!!

[MFGF]

Whelp.... I'm learning things too it turns out....Holy buckets.... I knew there was a difference in pulling directly from the package vs pulling from query....It all makes sense now! I am definitely adding this to the documents of need to know things.

Thank you for spelling it out and giving an amazing tip!!

You're welcome!

I'll also point you to the Reporting FAQs post. I suspect 1, 2, 4 and 5 might be useful?

https://www.cognoise.com/index.php/topic,27563.0.html

Cheers!

MF.

[dougp]

Do you have an particular tips on how to make them more amazeballs?

I'd just keep it simple.  You don't want the situation where people are more amazed by the painting frame than the painting.

I usually use a table with 2 columns:  description/question and prompt
I try to make all of my prompt pages look and behave pretty much the same.
If I have cascading requirements, I make sure to include the parameter from the source in a filter for the query for the target.  Letting Cognos decide always ends up biting me -- plus it has caused significant problems when using DQM packages.
If I have prompt processing/flow rules or if a prompt page gets too big or if some (of many) prompts are required and some or optional, I use multiple prompt pages.

You may have a similar need for invisible prompts, so here's an example...
We have a biennial budget.  So for our financial data mart, to make data management easier (and queries faster, it turns out), we have partitioned the database by biennium.  Many times the user wants to filter the report to a fiscal year or a specific month.  When they do that, I can compute the biennium (rather than bother the user with that question) to filter on the partition key.  By filtering on the partition key (hard-coding the value in the SQL) I can improve query speed by 2 to 20 times.  This is better for the user, but also reduces the load on the database server.  Here's an example:

Code Select Expand

[ExpenditureNamespace].[FactTable].[BienniumId] = ?FiscalYear? - 1 - mod(?FiscalYear?, 2)
produces this SQL

Code Select Expand

fact.FiscalBienniumId = 2017 - 1 - 1
which is deterministic so the SQL engine makes good choices.
You can see I need the prompt page to ask for the fiscal year, but not the biennium.

There are other things you can do with prompt pages to make them more functional if you use JavaScript:
Require a certain number of prompts populated, but nothing specific.  Making all prompts optional can cause headache for both you and the user (and the database administrator: commencing download of the internet...).  But you can control this with JavaScript.
Automatically select the first n or all values in a value prompt by default.
Set a default other than today for a date prompt.
Take a look at https://github.com/dougpulse/Cognos  (OK, there's a lot of stuff there and a bunch of it is probably garbage.  Look at prompts.js and related.)

[oscarca]

I'll throw in some general tips/best practise:

• Consistency in naming convention e.g. I always name all the Query objects with "q" in the beginning of the name, all the paramters with "p" and variables with "v". (qCustomerSales, pProduct, vPDF)
• Highlight hidden items
• Comment code
• Become familiar with the XML code structure. This will save you a lot of time when editing and modifying reports (ctrl + H) 
• Reduce the number of query items by deleting unreferenced/redundant query objects

One little trick that I use quite often is hiding a column by changing the padding to 0 pixels in all directions and deleting the text inside the column. This method is great when you cannot use "Box type: none".

[jessimica602]

I'd just keep it simple.  You don't want the situation where people are more amazed by the painting frame than the painting.

Thank you!!! I'm definitely adding that info to my instructions - I think I'll cut it a little short with extreme prompts come to me :)

[jessimica602]

I'll throw in some general tips/best practise:

• Consistency in naming convention e.g. I always name all the Query objects with "q" in the beginning of the name, all the paramters with "p" and variables with "v". (qCustomerSales, pProduct, vPDF)
• Highlight hidden items
• Comment code
• Become familiar with the XML code structure. This will save you a lot of time when editing and modifying reports (ctrl + H) 
• Reduce the number of query items by deleting unreferenced/redundant query objects

One little trick that I use quite often is hiding a column by changing the padding to 0 pixels in all directions and deleting the text inside the column. This method is great when you cannot use "Box type: none".

OOO I like all of these!! They are definitely things I am adding!
Although, what do you mean by Highlight Hidden Items?

Thank you!

[MFGF]

Although, what do you mean by Highlight Hidden Items?

Thank you!

In a report, you can add objects, then mark them as hidden. Normally you won't then see them - even when editing the report. You can go to the View option and choose "Show Hidden Objects" so that they are then visible to you when editing.

Cheers!

MF.