Column issue in cognos

[newuser14]

Hi,

My report consists of a column with (country + state) combination for example "Us-Newyork, Us-alabama, Canada-Ontario....". Now after listing all the US states I am generating their totals for whole states. Issue is to be listed immediately after all of that country respective states.

For example after all US-states "US-Total" should come, after all canada "canada-total..."
Please can anyone let me know how to do this in cognos 8.4.

CURRENT OUTPUT
US-MASSACHUSETTS
MAUHMS
MAUMPB
MAUTUF

US-MARYLAND TOTAL
MDUIMD
MDUJHU
MDUIDB
MDUMIH
MDUMLM

US-MINNESOTA TOTAL
MMUMAY
MMUMIM

CANADA-MANITOBA TOTAL
MBBAMM
MBBBFB
MBBDRG
MBBTAV
MBBVSS

US_TOTAL
CANADATOTAL
SUMAARY


REQUIRED OUTPUT

US-MASSACHUSETTS
MAUHMS
MAUMPB
MAUTUF

US-MARYLAND TOTAL
MDUHMD
MDUJHU
MDUMDB
MDUMIH
MDUMLM

US-MINNESOTA TOTAL
MMUMAY
MMUMIM


US_TOTAL

CANADA-MANITOBA TOTAL
MBBAMM
MBBBFB
MBBDRG
MBBTAV
MBBVSS

CANADA TOTAL

SUMMARY


Thanks,

 

[RyanDonohue]

You will need to have a data item for country. This would typically come from the database, but from the sounds of it you would have to derive country from the country-state combination.

To do this, create a new data item and use the following definition:
substring([Country-State], 1, position ('-', [Country-State])-1)

This will take the beginning characters up to the hyphen.

If your report is a list report, select the list and in properties go to grouping and sorting. Bring over this new data item. Then back in your list, select headers and footers, and check off this data item for header and footer. In the footer, you can bring over your totals and it will summarize each country.

If your report is a crosstab, you will need to nest your Country-State item under this new data item. When you select total, cognos will create a new row to summarize each country.

This is my first time to respond to another's issue. Hope this helps.

 

[blom0344]

Perhaps I am missing the point, but why not section the list on country and group on state?

Ties Blom