Split measure in multiple column based on dimension using list

[erwink]

Hi there

I've a very special need and just don't get it .

i have:
a Dimension as CostCategory
a dimension contract
a Measure
I've a DRM solution

with those elements I can create a crosstab

                 Cat1  Cat2  Cat3
------------|------|------|-----|
Contract1 | 25   | 37   |45   |

but now I was asked to deliver a list

Contract  | Cat1 | Cat2 | Cat3 |
-----------------------------------
C1         |  25   | 37    |  45   |

I tried by creating my own query calculation e.g for Cat1
if([Dimensions].[CostCategory].[CostCategoryH].[CostCategory] =[CAT])
then ([ActualCostEUR])
else (null)

but doing that way I got is

Contract  | Cat1 | Cat2 | Cat3 |
-----------------------------------
C1         |  25   |         |        |
C1         |         |  37   |        |
C1         |         |        |  45   |

Any hint how to get it as needed.

Thank you in advance for whatever hint
erwin

[BigChris]

Someone with a bigger brain than mine will probably give you a better answer, but that looks like an aggregation problem to me

[rockytopmark]

Hi there

I've a very special need and just don't get it .

i have:
a Dimension as CostCategory
a dimension contract
a Measure
I've a DRM solution

with those elements I can create a crosstab

                 Cat1  Cat2  Cat3
------------|------|------|-----|
Contract1 | 25   | 37   |45   |

but now I was asked to deliver a list

Contract  | Cat1 | Cat2 | Cat3 |
-----------------------------------
C1         |  25   | 37    |  45   |

I tried by creating my own query calculation e.g for Cat1
if([Dimensions].[CostCategory].[CostCategoryH].[CostCategory] =[CAT])
then ([ActualCostEUR])
else (null)

but doing that way I got is

Contract  | Cat1 | Cat2 | Cat3 |
-----------------------------------
C1         |  25   |         |        |
C1         |         |  37   |        |
C1         |         |        |  45   |

Any hint how to get it as needed.

Thank you in advance for whatever hint
erwin

Try else(0), and make sure those data items are set to aggregate as Sum

Sent from my SM-G950U1 using Tapatalk

[Robl]

With DMR you shouldn't need to do any sort of If statement at all.

Just add a crosstab to the page,
Put contract in the left section, put category in the top and measure as the measure and it will give you what you want.

[erwink]

Thank you @rockytopmark
I had tried Calculated and average and total...but not Summarize.

@Robl: yes that is easy as crosstab. but my customer need it as list to be able to process it further

[New_Guy]

Hi,
Try using tuples for each category. Attached is the sample report. If this doesn't help you have to create a query for each category and join to get them in columns. If you dont mind can you let us know What kind of further processing has to done on this list.
Good luck
New guy.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/12.0/" useStyleVersion="10" expressionLocale="en-us">
<modelPath>/content/folder[@name='ABCD / XYZ / EFG']/folder[@name='SAMPLES']/folder[@name='Models']/package[@name='GO Data Warehouse (analysis)']/model[@name='model']</modelPath>
<drillBehavior modelBasedDrillThru="true"/>
<queries>
<query name="Query1">
<source>
<model/>
</source>
<selection><dataItemMemberProperty name="Product line code"><dmMemberProperty><MPUN>[Sales].[Products].[Products].[Product line].[Product line code]</MPUN><itemCaption>Product line code</itemCaption></dmMemberProperty><dmDimension><DUN>[Sales].[Products]</DUN><itemCaption>Products</itemCaption></dmDimension><dmHierarchy><HUN>[Sales].[Products].[Products]</HUN><itemCaption>Products</itemCaption></dmHierarchy><dmLevel><LUN>[Sales].[Products].[Products].[Product line]</LUN><itemCaption>Product line</itemCaption></dmLevel></dataItemMemberProperty><dataItemCalculatedMeasure name="Data Item1"><expression>tuple(currentMember ([Sales].[Products].[Products]),[Fax],[Unit cost])</expression><dmDimension><DUN>[Sales].[Sales fact]</DUN><itemCaption>Sales fact</itemCaption></dmDimension></dataItemCalculatedMeasure><dataItemMeasure name="Unit cost"><dmMember><MUN>[Sales].[Sales fact].[Unit cost]</MUN><itemCaption>Unit cost</itemCaption></dmMember><dmDimension><DUN>[Sales].[Sales fact]</DUN><itemCaption>Sales fact</itemCaption></dmDimension></dataItemMeasure><dataItemMember name="Fax"><dmMember><MUN>[Sales].[Order method].[Order method].[Order method type]-&gt;[Order method].[601]</MUN><itemCaption>Fax</itemCaption></dmMember><dmDimension><DUN>[Sales].[Order method]</DUN><itemCaption>Order method</itemCaption></dmDimension><dmHierarchy><HUN>[Sales].[Order method].[Order method]</HUN><itemCaption>Order method</itemCaption></dmHierarchy></dataItemMember><dataItemCalculatedMeasure name="Data Item2"><expression>tuple(currentMember ([Sales].[Products].[Products]),[Mail],[Unit cost])</expression><dmDimension><DUN>[Sales].[Sales fact]</DUN><itemCaption>Sales fact</itemCaption></dmDimension></dataItemCalculatedMeasure><dataItemMember name="Mail"><dmMember><MUN>[Sales].[Order method].[Order method].[Order method type]-&gt;[Order method].[603]</MUN><itemCaption>Mail</itemCaption></dmMember><dmDimension><DUN>[Sales].[Order method]</DUN><itemCaption>Order method</itemCaption></dmDimension><dmHierarchy><HUN>[Sales].[Order method].[Order method]</HUN><itemCaption>Order method</itemCaption></dmHierarchy></dataItemMember></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">



<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="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 code"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Product line code"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Data Item1"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Data Item1"/></dataSource></textItem></contents></listColumnBody></listColumn><listColumn><listColumnTitle><style><defaultStyles><defaultStyle refStyle="lt"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Data Item2"/></dataSource></textItem></contents></listColumnTitle><listColumnBody><style><defaultStyles><defaultStyle refStyle="lc"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Data Item2"/></dataSource></textItem></contents></listColumnBody></listColumn></listColumns></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"/><XMLAttribute name="RS_modelModificationTime" value="2012-05-25T17:20:38.837Z" output="no"/></XMLAttributes></report>