Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
11 replies Latest Post - ‏2011-09-29T07:02:28Z by SystemAdmin
santoo66
santoo66
11 Posts
ACCEPTED ANSWER

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
    ACCEPTED ANSWER

    Re: subtotals and totals in list

    ‏2011-09-22T16:44:17Z  in response to santoo66
    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
      ACCEPTED ANSWER

      Re: subtotals and totals in list

      ‏2011-09-23T00:12:39Z  in response to OpherB
      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
        ACCEPTED ANSWER

        Re: subtotals and totals in list

        ‏2011-09-23T05:03:10Z  in response to SystemAdmin
        i am using dimensional package.tell me what functions should i use to get the req result.

        thanks in advance
        • SystemAdmin
          SystemAdmin
          15496 Posts
          ACCEPTED ANSWER

          Re: subtotals and totals in list

          ‏2011-09-23T05:22:00Z  in response to santoo66
          It would help if you could attach your report specification. Unfortunately it did not get included in your original post.
          • santoo66
            santoo66
            11 Posts
            ACCEPTED ANSWER

            Re: subtotals and totals in list

            ‏2011-09-23T09:02:14Z  in response to SystemAdmin
            <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
              ACCEPTED ANSWER

              Re: subtotals and totals in list

              ‏2011-09-25T23:06:28Z  in response to santoo66
              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
                ACCEPTED ANSWER

                Re: subtotals and totals in list

                ‏2011-09-26T06:51:49Z  in response to SystemAdmin
                attaching specification
                • SystemAdmin
                  SystemAdmin
                  15496 Posts
                  ACCEPTED ANSWER

                  Re: subtotals and totals in list

                  ‏2011-09-26T07:22:21Z  in response to santoo66
                  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
                    ACCEPTED ANSWER

                    Re: subtotals and totals in list

                    ‏2011-09-29T06:49:57Z  in response to SystemAdmin
                    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
                      ACCEPTED ANSWER

                      Re: subtotals and totals in list

                      ‏2011-09-29T07:02:28Z  in response to santoo66
                      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.
    • santoo66
      santoo66
      11 Posts
      ACCEPTED ANSWER

      Re: subtotals and totals in list

      ‏2011-09-23T05:05:56Z  in response to OpherB
      how do i develop that query i mean the code in the seperate query....