Inserting a calculated value(s) into a crosstab report

[gulgidd]

Can anyone provide information on inserting a row with calculated values into a crosstab report?  We tried unlocking the report, adding a block, and then putting a query calculation into the block.

Thanks,
Doug

[globalbear]

Just drag a query calculation into the report. No blocks needed. Works fine.

I made a crosstab with quantity sold for each year and sales territory. Then I added a calculated row that gives the percentage of the total quantity per sales territory.


Try this report on the GO sales and retailer package:

<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en"><!--RS:8.2-->
   <modelPath>/content/package[@name='GO Sales and Retailers']/model[@name='model']</modelPath>
   <queries>
      <query name="Query1">
         <source>
            <model/>
         </source>
         <selection><dataItem name="Order year" aggregate="none"><expression>[gosales_goretailers].[Orders].[Order year]</expression></dataItem><dataItem name="Sales territory" aggregate="none"><expression>[gosales_goretailers].[Countries].[Sales territory]</expression></dataItem><dataItem name="Quantity" aggregate="total"><expression>[gosales_goretailers].[Orders].[Quantity]</expression></dataItem><dataItem name="Perc_of_tot"><expression>[Quantity]/total([Quantity] for [Order year])</expression></dataItem><dataItem name="Total(Sales territory)" solveOrder="1"><expression>total(currentMeasure within detail [Sales territory])</expression></dataItem></selection>
      </query>
   </queries>
   <layouts>
      <layout>
         <reportPages>
            <page class="pg" name="Page1">
               <pageBody class="pb">
                  <contents>
                     <crosstab class="xt" refQuery="Query1">
                        <crosstabCorner class="xm"><contents><textItem><dataSource><dataItemLabel refDataItem="Quantity"/></dataSource></textItem></contents></crosstabCorner>
                        
                        
                        <style>
                           <CSS value="border-collapse:collapse"/>
                        </style>
                     <crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order year" class="ml" edgeLocation="Order year"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity" class="ml" edgeLocation="Quantity"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Perc_of_tot" class="ml" edgeLocation="Data Item1"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Sales territory" class="ml" edgeLocation="Sales territory"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Total(Sales territory)" class="ol" edgeLocation="Total(Sales territory)"><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell class="ov"/></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><defaultMeasure refDataItem="Quantity"/><crosstabFactCell class="mv"><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents></crosstabFactCell><crosstabIntersections><crosstabIntersection row="Data Item1" column="Order year"><style><dataFormat><percentFormat/></dataFormat></style></crosstabIntersection></crosstabIntersections></crosstab>
                  </contents>
               </pageBody>
               <pageHeader class="ph">
                  <contents>
                     <block class="ta">
                        <contents>
                           <textItem class="tt">
                              <dataSource>
                                 <staticValue/>
                              </dataSource>
                           </textItem>
                        </contents>
                     </block>
                  </contents>
                  <style>
                     <CSS value="padding-bottom:10px"/>
                  </style>
               </pageHeader>
               <pageFooter class="pf">
                  <contents>
                     <table class="tb">
                        <tableRows>
                           <tableRow>
                              <tableCells>
                                 <tableCell>
                                    <contents>
                                       <textItem>
                                          <dataSource>
                                             <reportExpression>AsOfDate()</reportExpression>
                                          </dataSource>
                                       </textItem>
                                    </contents>
                                    <style>
                                       <CSS value="vertical-align:top;text-align:left;width:25%"/>
                                    </style>
                                 </tableCell>
                                 <tableCell>
                                    <contents>
                                       <textItem>
                                          <dataSource>
                                             <staticValue>- </staticValue>
                                          </dataSource>
                                       </textItem>
                                       <textItem>
                                          <dataSource>
                                             <reportExpression>PageNumber()</reportExpression>
                                          </dataSource>
                                       </textItem>
                                       <textItem>
                                          <dataSource>
                                             <staticValue> -</staticValue>
                                          </dataSource>
                                       </textItem>
                                    </contents>
                                    <style>
                                       <CSS value="vertical-align:top;text-align:center;width:50%"/>
                                    </style>
                                 </tableCell>
                                 <tableCell>
                                    <contents>
                                       <textItem>
                                          <dataSource>
                                             <reportExpression>AsOfTime()</reportExpression>
                                          </dataSource>
                                       </textItem>
                                    </contents>
                                    <style>
                                       <CSS value="vertical-align:top;text-align:right;width:25%"/>
                                    </style>
                                 </tableCell>
                              </tableCells>
                           </tableRow>
                        </tableRows>
                        <style>
                           <CSS value="border-collapse:collapse;width:100%"/>
                        </style>
                     </table>
                  </contents>
                  <style>
                     <CSS value="padding-top:10px"/>
                  </style>
               </pageFooter>
            </page>
         </reportPages>
      </layout>
   </layouts>
</report>

[gulgidd]

Thank you for the comprehensive response, I used your sample to compare to our report and found the error.  I changed the calculated item from "crosstab member cell (calculated)" to "crosstab member cell" then it worked.

This is our first production report using Cognos and it really helped to have the XML example to import.  Thank You!!!

Doug