Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Column issue in cognos

Status
Not open for further replies.

newuser14

Technical User
Aug 24, 2011
1
US
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,
 
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.
 
Perhaps I am missing the point, but why not section the list on country and group on state?

Ties Blom

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top