CrossTab calculations

[hansi]

Hi,

I'm using a single query to retrieve all data, then I create CrossTab as follows :

Country     Jan-14   Feb-14   Mar-14   Apr-14    TotalYTD   TotalYonY   TotalLastMonth
UK              100       200        300        200         ??             ??               ??
France        200       100        400        500         ??             ??               ??
Spain          100       300        300        600         ??             ??               ??

How can I add columns at the end for :
a) Total for Year to date (TotalYTD)
b) Total for past 12 months (TotalYonY)
c) Total for Last Month (TotalLastMonth)

[Bark]

Is this a relational or an OLAP source?

[Nike_BA]

I have been unable to get the total columns in a cross tab to populate. Several sources have informed me that this functionality is not working and IBM is aware of the issue. However, I haven't seen any official documentation on this subject.

To accomplish what your are trying to do, I have had to build total calculations within the list report for each fact you are going to include in the cross tab. Then you can create the cross tab like you have in the example without the total columns. Once the cross tab is built, add the calculated totals and you will be all set. The down side to this is that if you have to add or remove items from the report, the calculated items sometimes break and you have to start over from a new list report. It does work well if you are committed to your format and do not foresee modifications.

[MFGF]

I have been unable to get the total columns in a cross tab to populate. Several sources have informed me that this functionality is not working and IBM is aware of the issue. However, I haven't seen any official documentation on this subject.

Hi,

Are these sources reliable? Would you trust them if they told you your cheque (check) was in the mail? :)

I'd be intrigued to know who these sources are and what their Cognos skill level is! I have been using Cognos for more years than I care to remember, and I have never in all that time encountered an issue where simple crosstab summaries don't work. :)

MF.

[Nike_BA]

I cannot personally speak for their skills or reliability, but based on their job titles, they should know. Additionally, I had four internal report writers from two separate teams trying to get cross tab totals to populate without success before we reached out to our center of excellence. I should qualify that as a company we are fairly new to the Cognos world, so even our center of excellence often relies on these forums. Simple cross tab summaries with sub-total and grand total rows for grouped items are not an issue, but getting totals to populate in columns is another story. Hansi seems to be experiencing the same issue, so it doesn't appear to be isolated to our report packages. Fortunately the workaround is quite simple if not as flexible as desired, but if you know how to get them to work properly, you could help both Hansi and my team out. Can you share a screenshot of one of your reports to see if it is what we are trying to do?

[Lynn]

I do crosstab summaries on columns all the time without issue using both relational and dimensional sources.

Someone who tells you that IBM is aware of the issue ought to be able to provide the issue/bug number which is helpful to track in the fix list as new versions come out.

In this case I'd be surprised if it were truly an issue with the product. Hansi hasn't provided any further details on the source so it could well be that the relative time periods aren't available in the source which makes figuring out YTD, period over period, and current month the challenge. Until there is further detail we don't really know what his/her issue is.

Attached is a very simple example from the go sales query package.

[hansi]

OK - I'm using relational db

can someone explain how I can display c) in my crosstab summary? i.e. the value of the data item in the last monthly column?

[BigChris]

Create a calculation along the lines of

total(if([SalesDate] >= _first_of_month(current_date)) then ([SalesValue]) else (0))

that might need to be flexed depending on your database etc. but you get the idea

Edit - I forgot to mention that you might need to add "for [Country]" before the last bracket.

[hansi]

Cheers 'BigChris' ... exactly what I was looking for  :)

[BigChris]

You're welcome - glad I was able to help.