[solved]Report Fomatting

[BIsrik]

Hi i have report of this type.

ColAÃ,  Ã,  Ã,  Ã, ColBÃ,  Ã,  Ã,  Ã,  Ã,  ColCÃ,  Ã,  ColD

001Ã,  Ã,  Ã,  Ã,  2776Ã,  Ã,  Ã,  Ã,  Ã,  ABCÃ,  Ã,  Ã, 10
002Ã,  Ã,  Ã,  Ã,  2775Ã,  Ã,  Ã,  Ã,  Ã,  ABCÃ,  Ã,  Ã,  25
Ã,  Ã,  Ã,  Ã, Sub Total For ABCÃ,  Ã,  Ã,  Ã,  Ã,  25
003Ã,  Ã,  Ã,  Ã,  2775Ã,  Ã,  Ã,  Ã,  Ã,  XYZÃ,  Ã,  Ã,  30
004Ã,  Ã,  Ã,  Ã,  2660Ã,  Ã,  Ã,  Ã,  Ã,  XYZÃ,  Ã,  Ã,  06
005Ã,  Ã,  Ã,  Ã,  2665Ã,  Ã,  Ã,  Ã,  Ã,  XYZÃ,  Ã,  Ã,  09
Ã,  Ã,  Ã,  Ã, Sub Total For XYZÃ,  Ã,  Ã,  Ã,  Ã,  06
Grand TotalÃ,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  80

Please Note: The subtotal is taken whenever the value of in ColC changes..in other words it is grouped on ColC.

The subtotal value is taken for the lowest ColB Value. In the First Case ie for ABC the 2775 is lowest value in the ColB. The corresponding Value of 2775 in ColD is 25. This value has to come in the subtotal.

The grand total should be addition of all values except the subtotals. In the eg.U get 80 when u add all the values except the subtotal values.

How should i go about doing this.

srik

[BIsrik]

Can anyone give me an idea of how do this.

[JO]

hi

i am able to achieve this almost 80%.
Let me explain it.

1.First drag all the 4 columns to the list.
2.Group on Column C.
3. Click on Column B and Go to Property Window and Set "SOrt" to 'Sort Decending'( also u can do from tool bar) and" Aggregate  Function" to 'Minimum'.
4. Select Column D and got to "Aggregate" function in toolbar and select "Custom"
U will get one more column under ColD like ColD1.Select that ColD1 and make "Aggregate Function " in Property window to "None".

These will achieve till the Sub Total. I am  trying to get the Grand Total. Still i didnt get any solution. Let u know once i done.

[Darek]

That was an interesting challange. Solution is posted below. The main trick was to leverage nested Tabular Models and minimum( a for b ). But you will have to allow cross-joins or create specific query subject in the model.

[Darek]

<report xml:lang="en-us" xmlns="http://developer.cognos.com/schemas/report/1/"><!--RS:1.1-->
   <layoutList>
      <layout>
      
         <pageSet>
         
            <page name="Page1">
               <pageBody>
                  <list refQuery="Query1">
                     <listColumnTitles>
                        <listColumnTitle>
                           <textItem>
                              <text>Order year</text>
                           </textItem>
                        </listColumnTitle>
                        <listColumnTitle>
                           <textItem>
                              <text>Order month</text>
                           </textItem>
                        </listColumnTitle>
                        <listColumnTitle>
                           <textItem>
                              <text>Order method</text>
                           </textItem>
                        </listColumnTitle>
                        <listColumnTitle>
                           <textItem>
                              <text>Quantity</text>
                           </textItem>
                        </listColumnTitle>
                     </listColumnTitles>
                     <listColumns>
                        <listColumn>
                           <textItem>
                              <queryItemRef refItem="Order year"/>
                           </textItem>
                        </listColumn>
                        <listColumn>
                           <textItem>
                              <queryItemRef refItem="Order month"/>
                           </textItem>
                        </listColumn>
                        <listColumn refLevel="Order method">
                           <textItem>
                              <queryItemRef refItem="Order method"/>
                           </textItem>
                        </listColumn>
                        <listColumn>
                           <textItem>
                              <queryItemRef refItem="Quantity"/>
                           </textItem>
                        </listColumn>
                     </listColumns>
                     <style>
                        <CSS value="border-collapse:collapse"/>
                     </style>
                     <XMLAttribute name="RS_ListGroupInfo" value="Order method"/>
                     <groupFooter refLevel="Order method">
                        <rowCells>
                           <rowCell class="inlineFooter" colSpan="3">
                              <textItem>
                                 <text>Subtotal for </text>
                              </textItem>
                              <textItem>
                                 <queryItemRef refItem="Order method"/>
                              </textItem>
                              <style>
                                 <CSS value="text-align:right"/>
                              </style>
                           </rowCell>
                           <rowCell class="inlineSummary">
                              <style>
                                 <CSS value="text-align:right"/>
                              </style>
                              <textItem>
                                 <queryItemRef refItem="SubQuantity"/>
                              </textItem>
                           </rowCell>
                        </rowCells>
                     </groupFooter>
                     <overallFooter>
                        <rowCells>
                           <rowCell class="inlineFooter" colSpan="3">
                              <textItem>
                                 <text>Summary</text>
                              </textItem>
                           </rowCell>
                           <rowCell class="inlineSummary">
                              <style>
                                 <CSS value="text-align:right"/>
                              </style>
                              <textItem>
                                 <queryItemRef refItem="Quantity"/>
                              </textItem>
                           </rowCell>
                        </rowCells>
                     </overallFooter>
                  </list>
               </pageBody>
            </page>
         </pageSet>
      </layout>
   </layoutList>
   <modelConnection name="/content/package[@name='GO Sales and Retailers']/model[@name='model']"/>
   <querySet xml:lang="en-us">
      <BIQuery name="Query1">
         <cube>
            <factList>
               <item refItem="Order year" aggregate="none"/>
               <item refItem="Order month" aggregate="none"/>
               <item refItem="Quantity" aggregate="total"/>
               <item aggregate="none" refItem="SubQuantity"/>
            </factList>
            <dimension name="Order method">
               <level name="Order method">
                  <item key="true" refItem="Order method" aggregate="none"/>
               </level>
            </dimension>
         </cube>
         <tabularModel>
            <tabularModel name="Tabular Model1">
               <filter>
                  <condition>[Order year]=2004 and [Order month] between 1 and 3</condition>
               </filter>
               <dataItem name="Order year" aggregate="none">
                  <expression>[gosales_goretailers].[Orders].[Order year]</expression>
               </dataItem>
               <dataItem name="Order method" aggregate="none">
                  <expression>[gosales_goretailers].[Orders].[Order method]</expression>
               </dataItem>
               <dataItem name="Minimum month" aggregate="none">
                  <expression>minimum([gosales_goretailers].[Orders].[Order month] for [Order method])</expression>
               </dataItem>
               <dataItem name="Order month" aggregate="none">
                  <expression>[gosales_goretailers].[Orders].[Order month]</expression>
               </dataItem>
               <dataItem name="Quantity" aggregate="total">
                  <expression>[gosales_goretailers].[Orders].[Quantity]</expression>
               </dataItem>
            </tabularModel>
            <tabularModel name="Tabular Model2">
               <dataItem name="Order year" aggregate="none">
                  <expression>[gosales_goretailers].[Orders].[Order year]</expression>
               </dataItem>
               <dataItem name="Order month" aggregate="none">
                  <expression>[gosales_goretailers].[Orders].[Order month]</expression>
               </dataItem>
               <dataItem name="Order method" aggregate="none">
                  <expression>[gosales_goretailers].[Orders].[Order method]</expression>
               </dataItem>
               <dataItem name="Quantity" aggregate="total">
                  <expression>[gosales_goretailers].[Orders].[Quantity]</expression>
               </dataItem>
            </tabularModel>
            <filter>
               <condition>
[Tabular Model1].[Order year]=[Tabular Model2].[Order year] and [Tabular Model1].[Order method]= [Tabular Model2].[Order method] and [Tabular Model1].[Minimum month]=[Tabular Model2].[Order month]</condition>
            </filter>
            <dataItem name="Order year">
               <expression>[Tabular Model1].[Order year]</expression>
            </dataItem>
            <dataItem name="Order month">
               <expression>[Tabular Model1].[Order month]</expression>
            </dataItem>
            <dataItem name="Order method">
               <expression>[Tabular Model1].[Order method]</expression>
            </dataItem>
            <dataItem name="Quantity">
               <expression>[Tabular Model1].[Quantity]</expression>
            </dataItem>
            <dataItem name="SubQuantity">
               <expression>[Tabular Model2].[Quantity]</expression>
            </dataItem>
         </tabularModel>
      </BIQuery>
   </querySet>
</report>

[Darek]

I've forgot to mention that the sample is based on GO Sales and Retailers package.