Percentage values in Crosstab totals

[pradeepbi25]

Hi,
I am using Cognos 10.2 version and relational database.
I have a crosstab report with 4 rows and 3 columns.
let say, A,B,C,D are rows and X,Y,Z  are columns.
Y and Z are measures and nested them below X.
Y is total and Z is percentage.
Y is total (column) and Z is percentage (Y for A,B,C,D).
And added Subtotals for each row. I used 'Automatic Summary' and not Total for subtotals.
But, I am getting wrong percentage values in subtotals.
Individual percentage values are correct.
Please help me in finding out my mistake. I am working on this from last 4 days.

Thanks,
Pradeep

[MFGF]

Hi,
I am using Cognos 10.2 version and relational database.
I have a crosstab report with 4 rows and 3 columns.
let say, A,B,C,D are rows and X,Y,Z  are columns.
Y and Z are measures and nested them below X.
Y is total and Z is percentage.
Y is total (column) and Z is percentage (Y for A,B,C,D).
And added Subtotals for each row. I used 'Automatic Summary' and not Total for subtotals.
But, I am getting wrong percentage values in subtotals.
Individual percentage values are correct.
Please help me in finding out my mistake. I am working on this from last 4 days.

Thanks,
Pradeep

Hi,

If you're calculating the percentage in your report, try setting the aggregation type for the percentage summaries to "Calculated" (ie it will do the aggregations first, then apply the percentage calculation to the aggregated values)

Cheers!

MF.

[pradeepbi25]

Hi MF

Thanks for quick reply. I changed 'Aggregate Function' and 'Rollup Aggregate Function' to Calculated from Automatic.
But it didn't helped.

[MFGF]

Hi MF

Thanks for quick reply. I changed 'Aggregate Function' and 'Rollup Aggregate Function' to Calculated from Automatic.
But it didn't helped.

Is the percentage being calculated in your report or is it a value being read from the data source?

MF.

[pradeepbi25]

Is the percentage being calculated in your report or is it a value being read from the data source?

MF.

I am calculating percentage in report.
the calculation is:
percentage ([Y] for A,B,C,D)

[MFGF]

I am calculating percentage in report.
the calculation is:
percentage ([Y] for A,B,C,D)

So A, B, C and D are four separate query items in your package? How many distinct values are there in each item? Or do you have one query item with four values?

Why would your percentage expression in the columns area need to have the "for A,B,C,D" scope? Wouldn't it be easier to code it as [Y] / total([Y] for report)

Can you explain in more detail how your data is structured and how your report is built?

Cheers!

MF.

[pradeepbi25]

So A, B, C and D are four separate query items in your package? How many distinct values are there in each item? Or do you have one query item with four values?

Why would your percentage expression in the columns area need to have the "for A,B,C,D" scope? Wouldn't it be easier to code it as [Y] / total([Y] for report)

Can you explain in more detail how your data is structured and how your report is built?

Cheers!

MF.

yes A,B,C,D are separate query items from package. I tried percentage calculation as [Y] / total([Y] for report) but its giving same error.

I think you can understand clearly with below example:


                                                                            X                                     Y                                 Z
                                                              Count        Percent          Count        Percent       Count        Percent
America   Missouri   John  Kristen             10             50%                5                25%            5                25%
               Indiana    Mike  Cecilia               12             55%                6                27%           4                 18%
Total                                                          22            100%              11              100%          9                100%

I am getting 100% in totals instead I must get correct percentage values for totals.

Thanks,
Pradeep

[MFGF]

yes A,B,C,D are separate query items from package. I tried percentage calculation as [Y] / total([Y] for report) but its giving same error.

I think you can understand clearly with below example:


                                                                            X                                     Y                                 Z
                                                              Count        Percent          Count        Percent       Count        Percent
America   Missouri   John  Kristen             10             50%                5                25%            5                25%
               Indiana    Mike  Cecilia               12             55%                6                27%           4                 18%
Total                                                          22            100%              11              100%          9                100%

I am getting 100% in totals instead I must get correct percentage values for totals.

Thanks,
Pradeep

Oh - so your row items are nested alongside each other, not stacked below each other?

Just to be clear, if you put your original expression back in place, and select the D row headings and choose Aggregate > Automatic Summary, do you get correct percentages for the summary of D (ie for C)?

MF.

[pradeepbi25]

Oh - so your row items are nested alongside each other, not stacked below each other?

Just to be clear, if you put your original expression back in place, and select the D row headings and choose Aggregate > Automatic Summary, do you get correct percentages for the summary of D (ie for C)?

MF.

No, I am not getting correct percentages for Automatic summary of D row.

Thanks,
Pradeep

[MFGF]

No, I am not getting correct percentages for Automatic summary of D row.

Thanks,
Pradeep

That's strange! I just built a report against the sample GO Sales (Query) package to do the same thing, and it appears to give the correct results. The report spec is below. Can you duplicate your issue against one of the sample packages?

<report xmlns="http://developer.cognos.com/schemas/report/11.0/" useStyleVersion="10" expressionLocale="en-us">
            <modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Models&apos;]/package[@name=&apos;GO Sales (query)&apos;]/model[@name=&apos;model&apos;]</modelPath>
            <drillBehavior/>
            <queries>
               <query name="Query1">
                  <source>
                     <model/>
                  </source>
                  <selection><dataItem aggregate="none" rollupAggregate="none" name="Year"><expression>[Sales (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value="attribute"/></XMLAttributes></dataItem><dataItem aggregate="none" rollupAggregate="none" name="Product line"><expression>[Sales (query)].[Products].[Product line]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="3"/><XMLAttribute output="no" name="RS_dataUsage" value="attribute"/></XMLAttributes></dataItem><dataItem aggregate="none" rollupAggregate="none" name="Order method type"><expression>[Sales (query)].[Order method].[Order method type]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="3"/><XMLAttribute output="no" name="RS_dataUsage" value="attribute"/></XMLAttributes></dataItem><dataItem aggregate="total" name="Quantity"><expression>[Sales (query)].[Sales].[Quantity]</expression><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="1"/><XMLAttribute output="no" name="RS_dataUsage" value="fact"/></XMLAttributes></dataItem><dataItem name="Perc"><expression>percentage([Quantity] for [Product line],[Order method type])</expression></dataItem><dataItemEdgeSummary solveOrder="1" refDataItem="Order method type" aggregateMethod="aggregate" name="Summary(Order method type)" label="Summary"/></selection>
               </query>
            </queries>
            <layouts>
               <layout>
                  <reportPages>
                     <page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
                        <pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
                           <contents>
                              <crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
                                 <crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents/></crosstabCorner>
                                 
                                 
                                 <noDataHandler>
                                    <contents>
                                       <block>
                                          <contents>
                                             <textItem>
                                                <dataSource>
                                                   <staticValue>No Data Available</staticValue>
                                                </dataSource>
                                                <style>
                                                   <CSS value="padding:10px 18px;"/>
                                                </style>
                                             </textItem>
                                          </contents>
                                       </block>
                                    </contents>
                                 </noDataHandler>
                                 <style>
                                    <defaultStyles>
                                       <defaultStyle refStyle="xt"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse"/>
                                 </style>
                              <crosstabColumns><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Perc" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Perc"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order method type" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Summary(Order method type)" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="il"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Summary(Order method type)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="iv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Product line" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab>
                           </contents>
                        </pageBody>
                        <pageHeader>
                           <contents>
                              <block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style>
                                 <contents>
                                    <textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style>
                                       <dataSource>
                                          <staticValue/>
                                       </dataSource>
                                    </textItem>
                                 </contents>
                              </block>
                           </contents>
                           <style>
                              <defaultStyles>
                                 <defaultStyle refStyle="ph"/>
                              </defaultStyles>
                              <CSS value="padding-bottom:10px"/>
                           </style>
                        </pageHeader>
                        <pageFooter>
                           <contents>
                              <table>
                                 <tableRows>
                                    <tableRow>
                                       <tableCells>
                                          <tableCell>
                                             <contents>
                                                <date>
                                                   <style>
                                                      <dataFormat>
                                                         <dateFormat/>
                                                      </dataFormat>
                                                   </style>
                                                </date>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:left;width:25%"/>
                                             </style>
                                          </tableCell>
                                          <tableCell>
                                             <contents>
                                                <pageNumber/>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:center;width:50%"/>
                                             </style>
                                          </tableCell>
                                          <tableCell>
                                             <contents>
                                                <time>
                                                   <style>
                                                      <dataFormat>
                                                         <timeFormat/>
                                                      </dataFormat>
                                                   </style>
                                                </time>
                                             </contents>
                                             <style>
                                                <CSS value="vertical-align:top;text-align:right;width:25%"/>
                                             </style>
                                          </tableCell>
                                       </tableCells>
                                    </tableRow>
                                 </tableRows>
                                 <style>
                                    <defaultStyles>
                                       <defaultStyle refStyle="tb"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse;width:100%"/>
                                 </style>
                              </table>
                           </contents>
                           <style>
                              <defaultStyles>
                                 <defaultStyle refStyle="pf"/>
                              </defaultStyles>
                              <CSS value="padding-top:10px"/>
                           </style>
                        </pageFooter>
                     </page>
                  </reportPages>
               </layout>
            </layouts>
         <XMLAttributes><XMLAttribute output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=","/><XMLAttribute output="no" name="RS_modelModificationTime" value="2010-06-14T20:07:25.203Z"/></XMLAttributes></report>

[pradeepbi25]

Thanks for trying to replicate my issue.
I am attaching screenshot of my report. I did the same way in my report.

[MFGF]

Thanks for trying to replicate my issue.
I am attaching screenshot of my report. I did the same way in my report.

What is incorrect in the results you are getting here?

MF.

[pradeepbi25]

What is incorrect in the results you are getting here?

MF.

Which I mentioned in the red color box shows:
1 100% 2 67% 1 33%
But I must get:
1 25% 2 50% 1 25%.

Thanks

[MFGF]

Which I mentioned in the red color box shows:
1 100% 2 67% 1 33%
But I must get:
1 25% 2 50% 1 25%.

Thanks

Is X a single item in the columns, or are there multiple items to the left/right of each other in the columns?

As I requested before, can you create the same issue using one of the sample packages and post it up so we can see how your report is working?

MF.

[pradeepbi25]

Is X a single item in the columns, or are there multiple items to the left/right of each other in the columns?

As I requested before, can you create the same issue using one of the sample packages and post it up so we can see how your report is working?

MF.

X is a single Item. Below that I nested Total count and percentage columns.

I don't have sample packages to recreate this issue.

Thanks

[MFGF]

X is a single Item. Below that I nested Total count and percentage columns.

I don't have sample packages to recreate this issue.

Thanks

Well we know that it works fine for me, and unless you can provide either explicit detail of everything going on in your report, or (preferably) get the samples installed and post up an example of the report where we can see the issue, it's almost impossible to know what might be wrong...

Cheers!

MF.

[sunny bachan prasad]

Hi Pradeep,
I was getting this error earlier when i was new to cognos tool and i have struggle a lot for that.Do the following step and i think it should work.
For percentage take the calculated data item and write your formula in that data item after that change the aggregate property of this data item to calculated.When u will do this data item will have a symbol in left corner.

After that for other data item which you are using to calculate percentage change the aggregate function to total.

I think it will give you the correct answer.

[magicksol13]

Hi to all,

I'm having the same type of problems even though it is not exactly the same crossTab.


                        2015 (text)                   2016 (text)                Percent% (text)
                   [PrevYearToDateSales]  [CurrYearToDateSales]
Jan                          150                               175                               16.7%
Feb                          175                               140                              -20.0%
...
Dec                          200                               180                              -10.0%
--------------------------------------------------------------------------------
TOTAL                      525                               495                              -13.3%



[CurrYearToDateSales]  and [PrevYearToDateSales] are both Data items i created in or query.
Percent is also DataItem that i create myself in ReportStudio as follows:  ([CurrYearToDateSales] - [PrevYearToDateSales]) / [PrevYearToDateSales]

I TRIED what some links are suggesting but with NO luck so far !

http://www-01.ibm.com/support/docview.wss?uid=swg21367569



Can anyone please help !

[hespora]

So you're necromancing a thread more than two years old rather than making a new one and expect people who'd be willing and able to help you to read the entirety of the thread to even understand what your issue is, as you have not described it?

Now I may just be crabby as it's been a long day, but I somehow doubt this will be very fruitful...