11.0.7 - Crosstab report - additional row totals

[lporter@wsutech.edu]

I have a basic crosstab that pulls credit hours (measure) by semester by program type (columns).

The row fields are: organization code, subject, and subject description.

Cognos gives us the total for the entire row based upon the three key fields but we want to add three more columns to pull totals for each of the three program types (Adult, CTE, and HS). In other words, sub-totals for the program types for each key combination.


I've tried numerous times to upload spreadsheet to illustrate what I'm asking but keep getting error that says path can't be located. I'd be happy to explain further if this doesn't make sense.

Thank you,

Lois Porter
lporter@wsutech.edu
WSU Tech
Wichita, KS

[MFGF]

I have a basic crosstab that pulls credit hours (measure) by semester by program type (columns).

The row fields are: organization code, subject, and subject description.

Cognos gives us the total for the entire row based upon the three key fields but we want to add three more columns to pull totals for each of the three program types (Adult, CTE, and HS). In other words, sub-totals for the program types for each key combination.


I've tried numerous times to upload spreadsheet to illustrate what I'm asking but keep getting error that says path can't be located. I'd be happy to explain further if this doesn't make sense.

Thank you,

Lois Porter
lporter@wsutech.edu
WSU Tech
Wichita, KS

I don't think uploads are working on the site at the moment. An alternative might be to take a screenshot of your example (making sure it doesn't show real data for security), upload it to a site such as imgbb, then post the BB code for the image into your thread.

In the meantime, I'd assume you would see measure values for each program type as you are using this item in your column edge? Is the issue that these are within each semester because program type is nested below semester? If that's the challenge, you can maybe stack program type to the right of semester in the columns edge?

I'm guessing about what the problem is, though, so ignore this if my guess is incorrect.

Cheers!

MF.

[lporter@wsutech.edu]

See whether this makes sense:                    Fall 2020                       Spring 2021                                             Grand Totals
                                                           Type1      Type2                 Type1      Type2                            Type1_Total           Type2_Total
                                                              5            10                        15         20                                   20                          30




Problem is getting Cognos crosstab to do the Grand Totals by Type.

[MFGF]

See whether this makes sense:                    Fall 2020                       Spring 2021                                             Grand Totals
                                                           Type1      Type2                 Type1      Type2                            Type1_Total           Type2_Total
                                                              5            10                        15         20                                   20                          30




Problem is getting Cognos crosstab to do the Grand Totals by Type.

Hi,

Hopefully it's as simple as this:
1. Click on a major Semester column heading value (eg Fall 2020) and from the toolbar, press the Summarize button and choose Total. This should add a major total column on the right for your semesters.
2. In the Insertable Objects pane on the left, click on the Data Items tab, and drag the Program Type item into the new Total Semesters column, nested below the existing column heading.

Cheers!

MF.

[lporter@wsutech.edu]

Thank you. Not quite there yet. Tried your idea but got error (see below); here is what I have so far; fine except need individual type totals.

              Fall 2020                                      Spring 2021                     Grand Total         
Type1   Type2    Term Total              Type1      Type2     Term Total                                  *Type1_Total  *Type2_Total
  5            10             15                    15            20              35                 50                         20                 30


Error:
The aggregate dataItem="Total(ACADEMIC_PERIOD_DESC)1" referenced by valueSet="3" is not expected to nest non-measures'.
Academic Period Desc is the term name; Fall 2020 Spring 2021; maybe I'm not dropping type into correct location; Cognos is so picky!

[lporter@wsutech.edu]

This has been resolved, thank you.

[MFGF]

Thank you. Not quite there yet. Tried your idea but got error (see below); here is what I have so far; fine except need individual type totals.

              Fall 2020                                      Spring 2021                     Grand Total         
Type1   Type2    Term Total              Type1      Type2     Term Total                                  *Type1_Total  *Type2_Total
  5            10             15                    15            20              35                 50                         20                 30


Error:
The aggregate dataItem="Total(ACADEMIC_PERIOD_DESC)1" referenced by valueSet="3" is not expected to nest non-measures'.
Academic Period Desc is the term name; Fall 2020 Spring 2021; maybe I'm not dropping type into correct location; Cognos is so picky!

I'm wondering if you're trying to drop in the wrong spot? Look for the drop zone directly below the Total heading (but not in the cells area)?

This has been resolved, thank you.

Ah good. Was it just finding the right place to drop or did something different work?

Cheers!

MF.