Weighted average in crosstab report

[samanl100]

Can you please check and tell me whether I can get the following result in a cross tab report?

Take the following query

select 1 row1, 1 col, 1 x,2 y from dual
Union all
select 2 row1, 1 col, 1 x,4 y from dual
Union all
select 3 row1, 1 col, 1 x,8 y from dual
Union all
select 1 row1, 2 col, 1 x,2 y from dual
Union all
select 2 row1, 2 col, 1 x,4 y from dual
Union all
select 3 row1, 2 col, 1 x,8 y from dual
Union all
select 1 row1, 3 col, 1 x,2 y from dual
Union all
select 2 row1, 3 col, 1 x,4 y from dual
Union all
select 3 row1, 3 col, 1 x,8 y from dual


get the cell value as sum(x)/sum(y)

Can I get the waited average as displayed in row wavg? NOT just an average as displayed in the row avg, which means it has to be Total(sum(x)) / Total(sum(y)) , not Total (sum(x)/sum(y))/3


Data Item1   1   2   3         
1   0.5   0.5   0.5   0.5      
2   0.25   0.25   0.25   0.25      
3   0.125   0.125   0.125   0.125      
avg   0.29167   0.29167   0.29167         
wavg   0.214285   0.214285   0.214285      

[CognosPaul]

Change the solve order of wavg. By default it does the calculation for each row then aggregates on that. By setting the solve order it will wait until the rows have been aggregated before attempting the calculation.

[samanl100]

I still couldn’t get the problem sorted because I am just a beginner in Cognos. I have created the report below, could you please correct that and send me back? Thanks in advance.

Cheers,
Sam

<report xmlns="http://developer.cognos.com/schemas/report/4.0/" expressionLocale="en-au">
            <modelPath>/content/package[@name='BMR']/model[@name='model']</modelPath>
            <layouts>
               <layout>
                  <reportPages>
                     <page name="Page1">
                        <style>
                           <defaultStyles>
                              <defaultStyle refStyle="pg"/>
                           </defaultStyles>
                        </style>
                        <pageBody>
                           <style>
                              <defaultStyles>
                                 <defaultStyle refStyle="pb"/>
                              </defaultStyles>
                           </style>
                           <contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1">
         
         
         
         <style>
            <CSS value="border-collapse:collapse"/>
            <defaultStyles>
               <defaultStyle refStyle="xt"/>
            </defaultStyles>
         </style>
      <crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="ROW1" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Average(ROW1)" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="COL" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Average(COL)" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabIntersections><crosstabIntersection row="e3" column="e2"><style><CSS value="background-color:red"/></style></crosstabIntersection></crosstabIntersections><defaultMeasure refDataItem="Data Item1"/></crosstab></contents>
                        </pageBody>
                     </page>
                  </reportPages>
               </layout>
            </layouts>
            <classStyles>
               <classStyle name="pd_1" label="Excellent">
                  <CSS value="background-color:#009933; color:#FFFFFF;"/>
               </classStyle>
               <classStyle name="pd_2" label="Very good">
                  <CSS value="background-color:#FFFFFF; color:#009933;"/>
               </classStyle>
               <classStyle name="pd_3" label="Average">
                  <CSS value="background-color:#FFFFFF; color:#CC9900;"/>
               </classStyle>
               <classStyle name="pd_4" label="Below average">
                  <CSS value="background-color:#FFFFFF; color:#990000;"/>
               </classStyle>
               <classStyle name="pd_5" label="Poor">
                  <CSS value="background-color:#990000; color:#FFFFFF;"/>
               </classStyle>
            </classStyles>
         <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/></XMLAttributes><queries><query name="Query1">
         <source>
            
         <sqlQuery name="SQL1" dataSource="BMR_Prod">
         <sqlText>select 1 row1, 1 col, 1 x,2 y from dual
Union all
select 2 row1, 1 col, 1 x,4 y from dual
Union all
select 3 row1, 1 col, 1 x,8 y from dual
Union all
select 1 row1, 2 col, 1 x,2 y from dual
Union all
select 2 row1, 2 col, 1 x,4 y from dual
Union all
select 3 row1, 2 col, 1 x,8 y from dual
Union all
select 1 row1, 3 col, 1 x,2 y from dual
Union all
select 2 row1, 3 col, 1 x,4 y from dual
Union all
select 3 row1, 3 col, 1 x,8 y from dual</sqlText>
      <mdProjectedItems><mdProjectedItem name="ROW1"/><mdProjectedItem name="COL"/><mdProjectedItem name="X"/><mdProjectedItem name="Y"/></mdProjectedItems></sqlQuery></source>
         <selection><dataItem name="ROW1"><expression>[SQL1].[ROW1]</expression></dataItem><dataItem name="COL"><expression>[SQL1].[COL]</expression></dataItem><dataItem name="X" aggregate="total"><expression>[SQL1].[X]</expression></dataItem><dataItem name="Y" aggregate="total"><expression>[SQL1].[Y]</expression></dataItem><dataItem name="Data Item1"><expression>[X]/[Y]</expression></dataItem><dataItem name="Average(ROW1)" solveOrder="1"><expression>average(currentMeasure within detail [ROW1])</expression></dataItem><dataItem name="Average(COL)" solveOrder="1"><expression>average(currentMeasure within detail [COL])</expression></dataItem></selection>
      </query></queries></report>