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
Quote from: lporter@wsutech.edu on 17 Dec 2020 03:17:46 PM
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.
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.
Quote from: lporter@wsutech.edu on 08 Feb 2021 04:04:12 PM
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.
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!
This has been resolved, thank you.
Quote from: lporter@wsutech.edu on 10 Feb 2021 08:53:57 AM
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)?
Quote from: lporter@wsutech.edu on 10 Feb 2021 09:43:59 AM
This has been resolved, thank you.
Ah good. Was it just finding the right place to drop or did something different work?
Cheers!
MF.