Topic
  • 11 replies
  • Latest Post - ‏2011-09-29T07:02:28Z by SystemAdmin
santoo66
santoo66
11 Posts

Pinned topic subtotals and totals in list

‏2011-09-22T06:01:02Z |
Hi
i am having requirement where in a list i have product line,product type,revenue....created subtotals on revenue and group on product line and i am having a prompt on order year(2004,2005,2006) which will filter the data and subtotals and totals are displayed depending upon the selected value at run time. but my requirement is grand total should be static(should contain revenue for all years) what ever may be the run time value and subtotals should contain value depending on prompt value..attaching xml specification
can anyone help me...
Thanks in advance
Updated on 2011-09-29T07:02:28Z at 2011-09-29T07:02:28Z by SystemAdmin
  • OpherB
    OpherB
    664 Posts

    Re: subtotals and totals in list

    ‏2011-09-22T16:44:17Z  
    Nothing attached....

    I would create a separate query to total the full value without filter, and then insert the total where needed using a singleton element.

    Good luck,
    Opher
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: subtotals and totals in list

    ‏2011-09-23T00:12:39Z  
    • OpherB
    • ‏2011-09-22T16:44:17Z
    Nothing attached....

    I would create a separate query to total the full value without filter, and then insert the total where needed using a singleton element.

    Good luck,
    Opher
    If this is a cube or DMR package then you can use dimensional functions to accomplish this instead of using a detail filter. If you are using such a package then attach your sample report specification so that we can see the layout you are trying to achieve. This will help to determine which dimensional functions/techniques will be necessary.
  • santoo66
    santoo66
    11 Posts

    Re: subtotals and totals in list

    ‏2011-09-23T05:03:10Z  
    If this is a cube or DMR package then you can use dimensional functions to accomplish this instead of using a detail filter. If you are using such a package then attach your sample report specification so that we can see the layout you are trying to achieve. This will help to determine which dimensional functions/techniques will be necessary.
    i am using dimensional package.tell me what functions should i use to get the req result.

    thanks in advance
  • santoo66
    santoo66
    11 Posts

    Re: subtotals and totals in list

    ‏2011-09-23T05:05:56Z  
    • OpherB
    • ‏2011-09-22T16:44:17Z
    Nothing attached....

    I would create a separate query to total the full value without filter, and then insert the total where needed using a singleton element.

    Good luck,
    Opher
    how do i develop that query i mean the code in the seperate query....
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: subtotals and totals in list

    ‏2011-09-23T05:22:00Z  
    • santoo66
    • ‏2011-09-23T05:03:10Z
    i am using dimensional package.tell me what functions should i use to get the req result.

    thanks in advance
    It would help if you could attach your report specification. Unfortunately it did not get included in your original post.
  • santoo66
    santoo66
    11 Posts

    Re: subtotals and totals in list

    ‏2011-09-23T09:02:14Z  
    It would help if you could attach your report specification. Unfortunately it did not get included in your original post.
    <report xmlns="http://developer.cognos.com/schemas/report/5.0/" expressionLocale="en-us">
    <modelPath>/content/package/model</modelPath>
    <drillBehavior modelBasedDrillThru="true"/>
    <queries>
    <query name="Query1">
    <source>
    <model/>
    </source>
    <selection><dataItemLevelSet name="product line"><dmDimension><DUN>gosales_goretailers.New Dimension3</DUN><itemCaption>New Dimension3</itemCaption></dmDimension><dmHierarchy><HUN>gosales_goretailers.New Dimension3.pro</HUN><itemCaption>pro</itemCaption></dmHierarchy><dmLevel><LUN>gosales_goretailers.New Dimension3.pro.product line</LUN><itemCaption>product line</itemCaption></dmLevel></dataItemLevelSet><dataItemLevelSet name="product type"><dmDimension><DUN>gosales_goretailers.New Dimension3</DUN><itemCaption>New Dimension3</itemCaption></dmDimension><dmHierarchy><HUN>gosales_goretailers.New Dimension3.pro</HUN><itemCaption>pro</itemCaption></dmHierarchy><dmLevel><LUN>gosales_goretailers.New Dimension3.pro.product type</LUN><itemCaption>product type</itemCaption></dmLevel></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>gosales_goretailers.New Dimension1.Revenue</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>gosales_goretailers.New Dimension1</DUN><itemCaption>New Dimension1</itemCaption></dmDimension></dataItemMeasure><dataItem name="Total(Revenue)" aggregate="calculated" rollupAggregate="total"><expression>Revenue</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></style><contents><textItem><dataSource><dataItemLabel refDataItem="product line"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="product line"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="product line"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="product type"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="product type"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Revenue"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><listGroups><listGroup refDataItem="product line"><listFooter><listRows><listRow><rowCells><rowCell colSpan="2"><contents><textItem><dataSource><dataItemValue refDataItem="product line"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="of"/></defaultStyles></style></rowCell><rowCell><contents><textItem><dataSource><dataItemValue refDataItem="Total(Revenue)"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="os"/></defaultStyles></style></rowCell></rowCells></listRow></listRows></listFooter></listGroup></listGroups><listOverallGroup><listFooter><listRows><listRow><rowCells><rowCell colSpan="2"><contents><textItem><dataSource><staticValue>Summary</staticValue></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="of"/></defaultStyles></style></rowCell><rowCell><contents><textItem><dataSource><dataItemValue refDataItem="Total(Revenue)"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="os"/></defaultStyles></style></rowCell></rowCells></listRow></listRows></listFooter></listOverallGroup></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>
    </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"/></XMLAttributes></report>
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: subtotals and totals in list

    ‏2011-09-25T23:06:28Z  
    • santoo66
    • ‏2011-09-23T09:02:14Z
    <report xmlns="http://developer.cognos.com/schemas/report/5.0/" expressionLocale="en-us">
    <modelPath>/content/package/model</modelPath>
    <drillBehavior modelBasedDrillThru="true"/>
    <queries>
    <query name="Query1">
    <source>
    <model/>
    </source>
    <selection><dataItemLevelSet name="product line"><dmDimension><DUN>gosales_goretailers.New Dimension3</DUN><itemCaption>New Dimension3</itemCaption></dmDimension><dmHierarchy><HUN>gosales_goretailers.New Dimension3.pro</HUN><itemCaption>pro</itemCaption></dmHierarchy><dmLevel><LUN>gosales_goretailers.New Dimension3.pro.product line</LUN><itemCaption>product line</itemCaption></dmLevel></dataItemLevelSet><dataItemLevelSet name="product type"><dmDimension><DUN>gosales_goretailers.New Dimension3</DUN><itemCaption>New Dimension3</itemCaption></dmDimension><dmHierarchy><HUN>gosales_goretailers.New Dimension3.pro</HUN><itemCaption>pro</itemCaption></dmHierarchy><dmLevel><LUN>gosales_goretailers.New Dimension3.pro.product type</LUN><itemCaption>product type</itemCaption></dmLevel></dataItemLevelSet><dataItemMeasure name="Revenue"><dmMember><MUN>gosales_goretailers.New Dimension1.Revenue</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>gosales_goretailers.New Dimension1</DUN><itemCaption>New Dimension1</itemCaption></dmDimension></dataItemMeasure><dataItem name="Total(Revenue)" aggregate="calculated" rollupAggregate="total"><expression>Revenue</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></style><contents><textItem><dataSource><dataItemLabel refDataItem="product line"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="product line"/></dataSource></textItem></contents><listColumnRowSpan refDataItem="product line"/></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="product type"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="product type"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Revenue"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lm"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Revenue"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns><listGroups><listGroup refDataItem="product line"><listFooter><listRows><listRow><rowCells><rowCell colSpan="2"><contents><textItem><dataSource><dataItemValue refDataItem="product line"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="of"/></defaultStyles></style></rowCell><rowCell><contents><textItem><dataSource><dataItemValue refDataItem="Total(Revenue)"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="os"/></defaultStyles></style></rowCell></rowCells></listRow></listRows></listFooter></listGroup></listGroups><listOverallGroup><listFooter><listRows><listRow><rowCells><rowCell colSpan="2"><contents><textItem><dataSource><staticValue>Summary</staticValue></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="of"/></defaultStyles></style></rowCell><rowCell><contents><textItem><dataSource><dataItemValue refDataItem="Total(Revenue)"/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="os"/></defaultStyles></style></rowCell></rowCells></listRow></listRows></listFooter></listOverallGroup></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>
    </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"/></XMLAttributes></report>
    When you post to this forum the text is parsed for forum tags and reformatted. For example, a set of square brackets will turn the text between the square brackets into a link. This means that posting a report specification in the text of a forum post will make the specification unusable. Please repost your report specification as a txt file attachment.
  • santoo66
    santoo66
    11 Posts

    Re: subtotals and totals in list

    ‏2011-09-26T06:51:49Z  
    When you post to this forum the text is parsed for forum tags and reformatted. For example, a set of square brackets will turn the text between the square brackets into a link. This means that posting a report specification in the text of a forum post will make the specification unusable. Please repost your report specification as a txt file attachment.
    attaching specification
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: subtotals and totals in list

    ‏2011-09-26T07:22:21Z  
    • santoo66
    • ‏2011-09-26T06:51:49Z
    attaching specification
    Unfortunately you are using an earlier version of the product that I don't have available at the moment. Also, you have created your own FM model from the Cognos sample data instead of using the packages from the Cognos sample deployment files. This makes things a bit difficult to help you. However, I'll do what I can to explain the process for creating the report.

    1. Create a new crosstab report
    2. Put the Product line level on the rows.
    3. Put the Product type level on the rows nested beneath the Product line.
    4. Put the Revenue measure in the columns.
    5. Use the query explorer to access the query definition.
    6. Drag a new Slicer Member Set into the Slicer area of the query definition.
    7. In the Slicer Member Set expression you will need something like the following:
    
    set([gosales_goretailers].[time dimension].[time hierarchy].[year level]->?Year prompt?)
    

    8. Use the page explorer to return to the page layout.
    9. Select the Product type crosstab node and use the toolbar button to apply an Aggregate summary to the Product type set.
    10. Select the Product line crosstab node and use the toolbar button to apply an Aggregate summary to the Product line set.
    11. Drag a new query calculation to the crosstab rows and place it below (not nested) the Product line summary you created in step 10. The calculation expression will be something like:
    
    tuple(<root member of the product hierarchy>, <root member of the time hierarchy>)
    

    The parts in angle brackets need to be replaced with the specified members by dragging them from the Insertable Objects pane into the expression.

    This slicer should apply to the measure values shown for everything except the last calculation. The use of the root member of the time hierarchy in the tuple function will override the slicer context so that you will get the measure value for all products for all time.
  • santoo66
    santoo66
    11 Posts

    Re: subtotals and totals in list

    ‏2011-09-29T06:49:57Z  
    Unfortunately you are using an earlier version of the product that I don't have available at the moment. Also, you have created your own FM model from the Cognos sample data instead of using the packages from the Cognos sample deployment files. This makes things a bit difficult to help you. However, I'll do what I can to explain the process for creating the report.

    1. Create a new crosstab report
    2. Put the Product line level on the rows.
    3. Put the Product type level on the rows nested beneath the Product line.
    4. Put the Revenue measure in the columns.
    5. Use the query explorer to access the query definition.
    6. Drag a new Slicer Member Set into the Slicer area of the query definition.
    7. In the Slicer Member Set expression you will need something like the following:
    <pre class="jive-pre"> set([gosales_goretailers].[time dimension].[time hierarchy].[year level]->?Year prompt?) </pre>
    8. Use the page explorer to return to the page layout.
    9. Select the Product type crosstab node and use the toolbar button to apply an Aggregate summary to the Product type set.
    10. Select the Product line crosstab node and use the toolbar button to apply an Aggregate summary to the Product line set.
    11. Drag a new query calculation to the crosstab rows and place it below (not nested) the Product line summary you created in step 10. The calculation expression will be something like:
    <pre class="jive-pre"> tuple(<root member of the product hierarchy>, <root member of the time hierarchy>) </pre>
    The parts in angle brackets need to be replaced with the specified members by dragging them from the Insertable Objects pane into the expression.

    This slicer should apply to the measure values shown for everything except the last calculation. The use of the root member of the time hierarchy in the tuple function will override the slicer context so that you will get the measure value for all products for all time.
    Hi
    I am attaching report specification which i have created using GoSales package.I want to display static grand total in a list report not in a cross tab report.
  • SystemAdmin
    SystemAdmin
    15496 Posts

    Re: subtotals and totals in list

    ‏2011-09-29T07:02:28Z  
    • santoo66
    • ‏2011-09-29T06:49:57Z
    Hi
    I am attaching report specification which i have created using GoSales package.I want to display static grand total in a list report not in a cross tab report.
    Sorry, a list is only a single-axis query and it doesn't allow you to create heterogeneous axis definitions (different sets next to each other). You really should reconsider using a crosstab. Using a list will make things more complicated than it is worth.