Display running total of records for an attribute on a page.

[bonniehsueh]

Hi Gurus,
Have a list report that shows cars on a contract. There can be many cars on one contract. If all the cars don't fit on one page and is continued to the next page, we want to show the text 'Continued' to indicate to the Account manager that the previous page has cars related to the contract.

My thought was to count the number of cars displayed on the page for the contract. If this count is less than the total count of cars for the contract, then display the text.
I'm having trouble counting number of cars displayed on the page for a contract.

I've gotten the running count function to work at the row level, but it only shows a 1 when I display it on the list header.

Example below. Any ideas?

Contract #1234
  car123
  car111
  car333
<...and so on until page fills up>

<Then the next page starts...>
Contract #1234 'Continued from previous page'

[navissar]

Hey CognosBuddy,
According to the structure you described I'm assuming you're using a section by contract. If that's the case, the solution is quite simple:
Cognos has this little known behaviour, I haven't figured out if it's a feature or bug: when a row number report function is used in a section row, it nullifies with every new category.
So, what you want to do is add to the section row a Layout Calculation with something like the following:

if(RowNumber ()>0) then (' Cont.') else('')

What you'll get is kind of like the attached screenshot:

[bonniehsueh]

Hi Nimrod,
You are correct, I am trying to display the value in a list section.

Great tip! I tried it and it worked for me in html, however, when I ran in pdf, the text did not show. I checked and see that rownum() does not have the same behavior you described when ran in pdf. The function returns a zero even in a new category. Very cool tip though! Thanks again for sharing!

I found a different way to do this using running count. I'll send it out tonight.

Cheers!

[bonniehsueh]

Hi All,
So this is trickier than I anticipated. I've gotten this to work in html, but it doesn't work in pdf for me either.  It's not eloquent by any means. I would say I like nimrod's idea better. I've attached a screenshot of what I got working in HTML, but not in pdf.

I did the following:
1) Created a column called running count. running-count ([Order number] for [Order method],[Employee name])
2) Created a column called Continued to indicated if the row is continued from the previous page or not. if ([Running count] = 1) then ('yes') else ('no'). There will always be a 1 record except for when a list is continued.
3) Create a boolean variable to show and change the color of the list header if rows are continued from the previous page: [Query1].[Continued] = 'no'

The tricky part is that I need to display the text in the List section header so the users knows its continued from the previous page.

The running count function shows what I expect if I put it in the list body and in the list page footer. However, when I put it in the list header, it only shows a 1. The running count function returns the number of rows based on the area (ie list header, list page footer, list footer, etc.) its placed on the list.  Since its the list page header, I'm guessing this is why it only shows 1 record.

I would think this is a common requirement, but it seems it may be a constraint Cognos has with pdf output.

Any ideas are appreciated!

[bonniehsueh]

Update,
So I tried one last idea. It turns out all you need is to use the running count data item in the List Page Header instead of the list section. It results in showing a 1 for everything except for a list that is continued from the previous page. Works in html and pdf.  Once you do this, you'll be all set!  I thought I remembered creating a report like this a couple years back but didn't keep the spec :/. Funny how lists can be tricky sometimes. :).

Cheers!

[Francis aka khayman]

what version are you using? can you paste an xml example? i can't seem to replicate it

[bonniehsueh]

Sorry for the delayed reply. I'm on 10.1. I tried to attach the XML spec but it's not working. I've appended it below for you instead. This is a solution that also works in pdf.

Unfortunately there is a catch. It doesn't work when the page is part of a page set. I am using a page break by customer. Haven't figured out a solution yet. Any ideas are appreciated.

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us">
            <modelPath>/content/folder[@name='!!Samples']/folder[@name='Models']/package[@name='GO Data Warehouse (query)']/model[@name='model']</modelPath>
            <drillBehavior modelBasedDrillThru="true"/>
            <queries>
               <query name="Query1">
                  <source>
                     <model/>
                  </source>
                  <selection><dataItem name="Order number" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Sales order].[Order number]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="identifier" output="no"/></XMLAttributes></dataItem><dataItem name="Order method" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Order method].[Order method]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="count order"><expression>count ([Order number] for [Order method],[Employee name])</expression></dataItem><dataItem name="Running count"><expression>running-count ([Order number] for [Order method],[Employee name])</expression></dataItem><dataItem name="Employee name" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Employee by region].[Employee name]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Product name" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product name]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Product color" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product color]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Product description" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Product].[Product description]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time dimension].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem></selection>
               <detailFilters><detailFilter><filterDefinition><filterInValues refDataItem="Year" dataType="integer"><filterValues><filterValue>2005</filterValue></filterValues></filterInValues></filterDefinition></detailFilter><detailFilter><filterExpression>[Employee name] = 'Abram Ruiz'</filterExpression></detailFilter></detailFilters></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 showColumnTitles="hide" refQuery="Query1" name="List2">
         <listColumns>
            <listColumn>
               <listColumnTitle>
                  <contents/>
               </listColumnTitle>
               <listColumnBody>
                  <contents><list refQuery="Query1" horizontalPagination="true" name="List1" showColumnTitles="startOfDetails">
                                 
                                 
                                 
                                 <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="ls"/>
                                    </defaultStyles>
                                    <CSS value="border-collapse:collapse;width:100%"/>
                                 </style>
                              <listColumns><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><staticValue>Row Number</staticValue></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><rowNumber>
         <style>
            <dataFormat>
               <numberFormat/>
            </dataFormat>
         </style>
      </rowNumber></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Year"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Year"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Order number"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Order number"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product name"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product name"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product color"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product color"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Product description"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product description"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><listPageFooter><listRows><listRow><rowCells><rowCell colSpan="6"><contents><textItem><dataSource><staticValue>List Page Footer              </staticValue></dataSource><style><CSS value="font-size:12pt"/></style></textItem><textItem><dataSource><dataItemValue refDataItem="Running count"/></dataSource><style><CSS value="background-color:yellow;font-size:12pt"/></style></textItem><textItem><dataSource><staticValue> orders of total </staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="count order"/></dataSource><style><CSS value="font-weight:bold;font-size:12pt"/></style></textItem></contents><style><defaultStyles><defaultStyle refStyle="lf"/></defaultStyles><CSS value="border:1.5pt solid silver"/></style></rowCell></rowCells></listRow></listRows></listPageFooter><listPageHeader afterOverallHeader="true"><listRows><listRow><rowCells><rowCell colSpan="6"><contents><textItem><dataSource><staticValue>List Page Header     </staticValue></dataSource><style><CSS value="font-weight:normal;font-style:italic"/></style></textItem><textItem><dataSource><staticValue>Salesperson </staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Employee name"/></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Order method"/></dataSource><style><CSS value="padding-left:15px"/></style></textItem><textItem><dataSource><staticValue> Orders </staticValue></dataSource></textItem><textItem><dataSource><reportExpression>IF ([Query1].[Running count]) &gt;1 THEN ('Cont...') ELSE ('')</reportExpression></dataSource><style><CSS value="background-color:fuchsia"/></style></textItem><textItem><dataSource><staticValue>          Total </staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="count order"/></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Order method"/></dataSource><style><CSS value="padding-left:5px"/></style></textItem><textItem><dataSource><staticValue> Orders</staticValue></dataSource></textItem></contents><style><CSS value="font-size:10pt;border-bottom:1.5pt solid gray"/><defaultStyles><defaultStyle refStyle="sh"/></defaultStyles></style></rowCell></rowCells></listRow></listRows></listPageHeader></list></contents>
                  <style>
                     <defaultStyles>
                        <defaultStyle refStyle="sb"/>
                     </defaultStyles>
                  </style>
               </listColumnBody>
            </listColumn>
         </listColumns>
         
         
         
         <style>
            <CSS value="border-collapse:collapse; width:100%"/>
            <defaultStyles>
               <defaultStyle refStyle="sc"/>
            </defaultStyles>
         </style>
      <listGroups><listGroup refDataItem="Employee name"><XMLAttributes><XMLAttribute name="RS_SectionedEdge" value="columns" output="no"/></XMLAttributes></listGroup><listGroup refDataItem="Order method"><listHeader repeatEveryPage="true"><listRows><listRow><rowCells><rowCell><contents><textItem><dataSource><dataItemValue refDataItem="Employee name"/></dataSource></textItem><textItem><dataSource><staticValue>     </staticValue></dataSource></textItem><textItem><dataSource><dataItemValue refDataItem="Order method"/></dataSource></textItem><textItem><dataSource><staticValue> Orders      </staticValue></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="sh"/></defaultStyles></style></rowCell></rowCells></listRow></listRows><rowCellsStyle><style><CSS value="display:none"/></style></rowCellsStyle></listHeader><XMLAttributes><XMLAttribute name="RS_SectionedEdge" value="columns" output="no"/></XMLAttributes><listFooter><listRows><listRow><rowCells><rowCell><contents><textItem><dataSource><dataItemValue refDataItem="Order method"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="if"/></defaultStyles></style></rowCell></rowCells></listRow></listRows></listFooter></listGroup></listGroups><sectionsFrame refFrame="List1" sectionedEdge="columns"/></list>
                           </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>
                           <block>
         <contents/>
      <style><CSS value="font-size:14pt"/></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>
                     </page>
                  </reportPages>
               </layout>
            </layouts>
         <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2008-10-14T18:31:27.587Z" output="no"/></XMLAttributes><reportName>Running count sample FIXED</reportName><reportVariables><reportVariable type="boolean" name="Boolean1">
         <reportExpression>[Query1].[Running count] &gt;1</reportExpression>
         <variableValues>
            <variableValue value="1"/>
         </variableValues>
      </reportVariable></reportVariables></report>