COGNOiSe.com - The IBM Cognos Community

Hi Cognos gurus!

I have a very simple Report Studio report, but when I CTRL/click 3 amount columns and click on the summarize icon at the top and choose total it will not summarize.  What am I doing wrong?

Thanks so much for any help!

Dana

Hi Dana,

What other data items are in your report?  Try grouping a column preceding the amount columns before attempting to summarize.  Are you getting an error of some sort?  What version of RS are you using?

Hi Chris,

I am grouping on two columns prior to the 3 I'm trying to total.  We are on 8.4.1 of Report Studio.

If I select one column it will summarize that column, but not all three at the same time.

Thanks for getting back to me!

Dana

No problem at all!

I can't seem to replicate the issue you are seeing, darn (though I am using 8.4.2).  CTRL+click on headers or column contents should allow you to use the Aggregate button at the top, unless that is a bug in the previous version.

Would you be able to send me the XML from your report?  (Tools -> Copy Report to Clipboard)

I'll check to see if I see anything out of the ordinary!

sorry offtopic, is 8.4.2 the latest version?

Sorry forgot to mention, I don't receive any errors with this.

Here's the xml:

Thanks so much for your time,


<report xmlns="http://developer.cognos.com/schemas/report/6.0/" expressionLocale="en-us">
            <modelPath>/content/folder[@name='Test Student']/folder[@name='Packages']/package[@name='FA Loan Disbursement']/model[@name='model']</modelPath>
            <drillBehavior modelBasedDrillThru="true"/>
            <queries>
               <query name="Query1">
                  <source>
                     <model/>
                  </source>
                  <selection><dataItem name="ID" aggregate="none" rollupAggregate="none" label="Student ID"><expression>[FA Loan Disbursement Folders].[Loan Disbursement].[ID]</expression></dataItem><dataItem name="NAME" aggregate="none" rollupAggregate="none" label="Student"><expression>[FA Loan Disbursement Folders].[Loan Disbursement].[NAME]</expression></dataItem><dataItem name="FUND" aggregate="none" rollupAggregate="none" label="Fund Code"><expression>[FA Loan Disbursement Folders].[Loan Disbursement].[FUND]</expression></dataItem><dataItem name="DIRECT_DISBURSEMENT_NUMBER" label="Disb. Number" aggregate="none" rollupAggregate="none"><expression>[FA Loan Disbursement Folders].[Loan Disbursement].[DIRECT_DISBURSEMENT_NUMBER]</expression></dataItem><dataItem name="DIRECT_LOAN_SEQUENCE_NUMBER" label="Loan Seq. Number" aggregate="none" rollupAggregate="none"><expression>[FA Loan Disbursement Folders].[Loan Disbursement].[DIRECT_LOAN_SEQUENCE_NUMBER]</expression></dataItem><dataItem name="DISBURSEMENT_FEED_DATE" aggregate="none" rollupAggregate="none" label="Disbursement Date"><expression>[FA Loan Disbursement Folders].[Loan Disbursement].[DISBURSEMENT_FEED_DATE]</expression></dataItem><dataItem name="DISBURSEMENT_FEED" aggregate="none" rollupAggregate="none" label=" "><expression>[FA Loan Disbursement Folders].[Loan Disbursement].[DISBURSEMENT_FEED]</expression></dataItem><dataItem name="NET_CHECK_AMOUNT" label="Amt Disbursed" aggregate="none" rollupAggregate="none"><expression>[FA Loan Disbursement Folders].[Loan Disbursement].[NET_CHECK_AMOUNT]</expression></dataItem><dataItem name="CHECK_RETURN_AMOUNT" label="Amt Returned" rollupAggregate="none" aggregate="none"><expression>[FA Loan Disbursement Folders].[Loan Disbursement].[CHECK_RETURN_AMOUNT]</expression></dataItem><dataItem name="Total Amt Disbursed" aggregate="none" rollupAggregate="none"><expression>Total([NET_CHECK_AMOUNT] for [NAME], [ID])</expression></dataItem><dataItem name="Total Amt Returned" aggregate="none" rollupAggregate="none"><expression>Total([CHECK_RETURN_AMOUNT] for [NAME], [ID])</expression></dataItem><dataItem name="Total Net Amt" aggregate="none" rollupAggregate="none"><expression>[Total Amt Disbursed] - [Total Amt Returned]</expression></dataItem></selection>
               <detailFilters><detailFilter><filterExpression>[FA Loan Disbursement Folders].[Loan Disbursement].[AID_YEAR] = ?Aid Year Prompt?</filterExpression></detailFilter><detailFilter><filterExpression>[FA Loan Disbursement Folders].[Loan Disbursement].[DISBURSEMENT_FEED_DATE] &gt;= ?Date Prompt?</filterExpression></detailFilter></detailFilters></query>
            <query name="Aid Year Prompt Query"><source><model/></source><selection><dataItem name="VALUE" aggregate="none" sort="descending"><expression>[Prompt Tables].[LOV_AID_YEAR].[VALUE]</expression></dataItem><dataItem name="VALUE_DESCRIPTION" aggregate="none"><expression>[Prompt Tables].[LOV_AID_YEAR].[VALUE_DESCRIPTION]</expression></dataItem></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>
                              <list refQuery="Query1" horizontalPagination="true" name="List1">
                                 
                                 
                                 
                                 <style>
                                    <defaultStyles>
                                       <defaultStyle refStyle="ls"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse"/>
                                 </style>
                              <listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="ID"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/></style><contents><textItem><dataSource><dataItemValue refDataItem="ID"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="ID"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="NAME"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/></style><contents><textItem><dataSource><dataItemValue refDataItem="NAME"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="ID"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Total Amt Disbursed"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/><dataFormat><currencyFormat/></dataFormat></style><contents><textItem><dataSource><dataItemValue refDataItem="Total Amt Disbursed"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="ID"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Total Amt Returned"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/><dataFormat><currencyFormat/></dataFormat></style><contents><textItem><dataSource><dataItemValue refDataItem="Total Amt Returned"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="ID"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="Total Net Amt"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/><dataFormat><currencyFormat/></dataFormat></style><contents><textItem><dataSource><dataItemValue refDataItem="Total Net Amt"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="ID"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="FUND"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/></style><contents><textItem><dataSource><dataItemValue refDataItem="FUND"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="FUND"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="DIRECT_DISBURSEMENT_NUMBER"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/></style><contents><textItem><dataSource><dataItemValue refDataItem="DIRECT_DISBURSEMENT_NUMBER"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="DIRECT_DISBURSEMENT_NUMBER"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="DIRECT_LOAN_SEQUENCE_NUMBER"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/></style><contents><textItem><dataSource><dataItemValue refDataItem="DIRECT_LOAN_SEQUENCE_NUMBER"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="DIRECT_LOAN_SEQUENCE_NUMBER"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="DISBURSEMENT_FEED_DATE"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/><dataFormat><dateFormat dateStyle="medium"/></dataFormat></style><contents><textItem><dataSource><dataItemValue refDataItem="DISBURSEMENT_FEED_DATE"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="DISBURSEMENT_FEED_DATE"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="DISBURSEMENT_FEED"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/></style><contents><textItem><dataSource><dataItemValue refDataItem="DISBURSEMENT_FEED"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="DISBURSEMENT_FEED_DATE"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="NET_CHECK_AMOUNT"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/><dataFormat><currencyFormat/></dataFormat></style><contents><textItem><dataSource><dataItemValue refDataItem="NET_CHECK_AMOUNT"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="DISBURSEMENT_FEED_DATE"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt;font-weight:bold"/></style><contents><textItem><dataSource><dataItemLabel refDataItem="CHECK_RETURN_AMOUNT"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles><CSS value="font-family:Arial;font-size:8pt"/><dataFormat><currencyFormat/></dataFormat></style><contents><textItem><dataSource><dataItemValue refDataItem="CHECK_RETURN_AMOUNT"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="DISBURSEMENT_FEED_DATE"/></listColumnBody></listColumn></listColumns><listGroups><listGroup refDataItem="NAME"><sortList><sortItem refDataItem="NAME" sortOrder="ascending"/></sortList></listGroup><listGroup refDataItem="ID"/><listGroup refDataItem="FUND"/><listGroup refDataItem="DIRECT_DISBURSEMENT_NUMBER"/><listGroup refDataItem="DIRECT_LOAN_SEQUENCE_NUMBER"/><listGroup refDataItem="DISBURSEMENT_FEED_DATE"/></listGroups></list>
                           </contents>
                        </pageBody>
                        <pageHeader>
                           <contents>
                              <block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles><CSS value="font-family:Arial;font-size:14pt;font-weight:bold;text-align:center"/></style>
                                 <contents>
                                    
                                 <textItem><dataSource><staticValue>DL Disbursement Activity - By Student</staticValue></dataSource></textItem></contents>
                              </block>
                           <block>
         <contents><textItem><dataSource><staticValue>For </staticValue></dataSource></textItem><textItem><dataSource><reportExpression>ParamDisplayValue('Aid Year Prompt')</reportExpression></dataSource></textItem></contents>
      <style><CSS value="font-family:Arial;font-size:12pt;font-weight:bold;text-align:center"/></style></block><block>
         <contents><textItem><dataSource><staticValue>Disbursements &gt;= </staticValue></dataSource></textItem><textItem><dataSource><reportExpression>ParamDisplayValue('Date Prompt')</reportExpression></dataSource><style><CSS value="font-family:Arial"/></style></textItem></contents>
      <style><CSS value="font-family:Arial;font-size:12pt;font-weight:bold;text-align:center"/></style></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>
                     <pageSetup orientation="landscape"><namedPaper name="/configuration/pageDefinition[@name='Legal']"/></pageSetup></page>
                  </reportPages>
               <promptPages><page name="Prompt Page1">
         <pageHeader>
            <contents>
               <block>
                  <contents>
                     <textItem><dataSource><staticValue>DL Disbursement Activity - By Student</staticValue></dataSource></textItem>
                  </contents>
                  <style>
                     <defaultStyles>
                        <defaultStyle refStyle="ta"/>
                     </defaultStyles>
                  </style>
               </block>
            </contents>
            <style>
               <defaultStyles>
                  <defaultStyle refStyle="hp"/>
               </defaultStyles>
            </style>
         </pageHeader>
         <pageBody>
            <contents><block>
         <contents><textItem><dataSource><staticValue>Select aid year: </staticValue></dataSource><style><CSS value="font-family:Arial;font-size:12pt"/></style></textItem></contents>
      </block><block>
         <contents><selectValue parameter="Aid Year Prompt" refQuery="Aid Year Prompt Query" multiSelect="false" range="false" required="true"><useItem refDataItem="VALUE"><displayItem refDataItem="VALUE_DESCRIPTION"/></useItem></selectValue></contents>
      </block><block>
         <contents><textItem><dataSource><staticValue> </staticValue></dataSource></textItem></contents>
      </block><block>
         <contents><textItem><dataSource><staticValue>Select disbursement date: (This will pull disbursement dates &gt;= selection)</staticValue></dataSource><style><CSS value="font-family:Arial;font-size:12pt"/></style></textItem></contents>
      </block><block>
         <contents><selectDate parameter="Date Prompt" multiSelect="false" range="false" required="true"><defaultSelections><defaultSimpleSelection>2010-01-01</defaultSimpleSelection></defaultSelections></selectDate></contents>
      </block></contents>
            <style>
               <defaultStyles>
                  <defaultStyle refStyle="py"/>
               </defaultStyles>
            </style>
         </pageBody>
         <pageFooter>
            <contents>
               <promptButton type="cancel">
                  <contents/>
                  <style>
                     <defaultStyles>
                        <defaultStyle refStyle="bp"/>
                     </defaultStyles>
                  </style>
               </promptButton>
               
               
               <promptButton type="finish">
                  <contents/>
                  <style>
                     <defaultStyles>
                        <defaultStyle refStyle="bp"/>
                     </defaultStyles>
                  </style>
               </promptButton>
            </contents>
            <style>
               <defaultStyles>
                  <defaultStyle refStyle="fp"/>
               </defaultStyles>
            </style>
         </pageFooter>
         <style>
            <defaultStyles>
               <defaultStyle refStyle="pp"/>
            </defaultStyles>
         </style>
      </page></promptPages></layout>
            </layouts>
         <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes></report>

I think since those 3 columns already have Total(.....) in the expression, its not letting you sum on them....

bummer. hmm.

Thanks tupac_rd!

Ah, I see the issue.  The order of the columns is the problem.

If you wish to summarize these 3 measures, create a new list (to avoid adjusting all the Grouping & Sorting properties for the existing list).  Add the ID and Name, then the 3 measures.  Group the ID and Name.  CTRL+Click the 3 measures and Total them.  Then, add the remaining columns.

Let me know if you have any issues with this!

This worked perfect.  I had to rearrange the layout quite a bit, but it worked and I love it!  Thanks so much Chris!

Dana

No problem at all, so glad it worked out well for you :)

Quote from: tupac_rd on 24 Aug 2010 02:49:07 PM
sorry offtopic, is 8.4.2 the latest version?

Nope - this version does not exist.  The latest released version currently is 8.4.1 with Fix Pack 2.

Cheers!

MF.

My bad on the lingo again, sorry folks. 8.4.1 with FP2 :)