ABC Analysis on Dimensional DataSource (SAP BW)

[IceTea]

Hi all,

today's Little Problem is around Report Studio and some Kind of ABC Analysis, which is requested by Business department ;o

Given is a SAP BW Datasource with some Kind of Dimensions/Measures:

- Supplier (Dimension, single Level hierarchy)
- PU Volume (Measure)
- Supplier Count (Measure)


All data (after filtering within the query) should be presented like the attached screenshot.

First Topic is to split the suppliers up into three Groups which represent top 80, 15 and 5 percent of total PU Volume.

I already did this with 3 query elements:

A: topPercent ([Supplier];80;[PU Volume])
C: bottomPercent ([Supplier];5;[PU Volume])
B: except ([Supplier];union([A_perc];[C_perc]))


For usage in the crosstabrows for each element i created a query element like this:
aggregate ( currentMeasure within set [A_perc])

So everything runs fine for summing up the measures.

But main-Problem at the Moment is to include the columns which represent the "%".Value of each measure for the A/B/C-Members compared with the "All Suppliers"-Value.

Do you have any hint how to proceed?

Or generally spoken, is this an accountable Approach to solve the reporting Needs or would you do such a Report different?

Thanks in Advance ;o
Ice

[MFGF]

Hi Ice,

Assuming the "All suppliers" row is just the member from the All Suppliers level, your % calcs would be:

[PU Volume] / tuple([All Suppliers member],[PU Volume])

and

[Supplier Count] / tuple([All Suppliers member],[Supplier Count])


Here's an example I wrote with the Great Outdoors Powercube sample package:

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/10.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;PowerPlay&apos;]/package[@name=&apos;great_outdoors_sales_en&apos;]/model[@name=&apos;2010-06-18T15:38:44.109Z&apos;]</modelPath>
<drillBehavior/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItem name="Top80"><expression>topPercent ([great_outdoors_sales_en].[Products].[Products].[Product],80,[Quantity sold])
</expression></dataItem><dataItemMeasure name="Quantity sold"><dmMember><MUN>[great_outdoors_sales_en].[Measures].[Quantity sold]</MUN><itemCaption>Quantity sold</itemCaption></dmMember><dmDimension><DUN>[great_outdoors_sales_en].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="9"/></XMLAttributes></dataItemMeasure><dataItem name="Bottom 5"><expression>bottomPercent ([great_outdoors_sales_en].[Products].[Products].[Product],5,[Quantity sold])</expression></dataItem><dataItem name="Middle 15"><expression>except ([great_outdoors_sales_en].[Products].[Products].[Product],union([Top80],[Bottom 5]))

</expression></dataItem><dataItem name="A-Products"><expression>aggregate(currentMeasure within set [Top80])</expression></dataItem><dataItem name="B-Products"><expression>aggregate(currentMeasure within set [Middle 15])</expression></dataItem><dataItem name="C-Products"><expression>aggregate(currentMeasure within set [Bottom 5])</expression></dataItem><dataItemMeasure name="Revenue"><dmMember><MUN>[great_outdoors_sales_en].[Measures].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[great_outdoors_sales_en].[Measures]</DUN><itemCaption>Measures</itemCaption></dmDimension><XMLAttributes><XMLAttribute output="no" name="RS_dataType" value="9"/></XMLAttributes></dataItemMeasure><dataItemMember name="Products"><dmMember><MUN>[great_outdoors_sales_en].[Products].[Products].[Products]-&gt;:[PC].[@MEMBER].[Product line code]</MUN><itemCaption>Products</itemCaption></dmMember><dmDimension><DUN>[great_outdoors_sales_en].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[great_outdoors_sales_en].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy></dataItemMember><dataItem name="Quantity %"><expression>[Quantity sold] / tuple([Products],[Quantity sold])</expression></dataItem><dataItem name="Revenue %"><expression>[Revenue] / tuple([Products],[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>
<crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents/></crosstabCorner>


<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="xt"/>
</defaultStyles>
<CSS value="border-collapse:collapse"/>
</style>
<crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="A-Products" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="A-Products"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="B-Products" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="B-Products"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="C-Products" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="C-Products"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Products" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity sold" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity %" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Quantity %"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles><dataFormat><percentFormat decimalSize="2"/></dataFormat></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents><factCell><style><dataFormat><currencyFormat currencyCode="GBP"/></dataFormat></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue %" edgeLocation="e8"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Revenue %"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles><dataFormat><percentFormat decimalSize="2"/></dataFormat></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab>
</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 output="no" name="RS_CreateExtendedDataItems" value="true"/><XMLAttribute output="no" name="listSeparator" value=","/><XMLAttribute output="no" name="RS_modelModificationTime" value="2010-06-18T19:38:44.127Z"/></XMLAttributes></report>