This project has moved and is read-only. For the latest updates, please go here.

Error in calculation

Jul 26, 2012 at 5:47 PM

Hello,

I have my hierarchy (eg rentabilite) deployed on line. I create this mesure : ([Measures].[Quantité Réel],[Rentabilité].[Rentabilité].&[269]&[T]&[0])

I expect to have on all lines the values of this position.The result : I have only on that position the value. On the others line the value is null. I don't understand why it doesn't work ?

Is there anyone can help me ?

Thanks for your help.

Jul 26, 2012 at 7:39 PM

Can you post the full MDX query from your PivotTable?

If you have them, can you describe the attribute relationships in the Rentabilite dimension? (It may be an attribute overwrite issue.)

Jul 27, 2012 at 8:24 AM
Edited Jul 27, 2012 at 8:30 AM

This, my MDX query

WITH
MEMBER [Measures].[Cumul Réel] as AGGREGATE(YTD([Exercices].[Hiérarchie Exercices].CurrentMember), [Measures].[Coût Réel])
MEMBER [Measures].[Test] as ([Measures].[Quantité Réel],[Rentabilité].[Rentabilité].&[269]&[T]&[0])
MEMBER [Measures].[Cumul Q] as AGGREGATE(YTD([Exercices].[Hiérarchie Exercices].CurrentMember), [Measures].[Quantité Réel])
SELECT {[Measures].[Test],[Measures].[Coût Réel]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(DrilldownMember({{DrilldownMember({{DrilldownMember({{DrilldownMember({{DrilldownLevel({[Rentabilité].[Hiérarchie Rentabilité].[All]},,,INCLUDE_CALC_MEMBERS)}}, {[Rentabilité].[Hiérarchie Rentabilité].&[0]&[M]&[0]},,,INCLUDE_CALC_MEMBERS)}}, {[Rentabilité].[Hiérarchie Rentabilité].&[0]&[T]&[0],[Rentabilité].[Hiérarchie Rentabilité].&[0]&[C]&[0]},,,INCLUDE_CALC_MEMBERS)}}, {[Rentabilité].[Hiérarchie Rentabilité].&[0]&[S]&[0],[Rentabilité].[Hiérarchie Rentabilité].&[10]&[C]&[0],[Rentabilité].[Hiérarchie Rentabilité].&[270]&[T]&[0],[Rentabilité].[Hiérarchie Rentabilité].&[272]&[T]&[0]},,,INCLUDE_CALC_MEMBERS)}}, {[Rentabilité].[Hiérarchie Rentabilité].&[6]&[S]&[0]},,,INCLUDE_CALC_MEMBERS)) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME,[Rentabilité].[Hiérarchie Rentabilité].[Codes Rentabilité],[Rentabilité].[Hiérarchie Rentabilité].[Hiérarchie Rentabilité] ON ROWS  FROM [Rentabilité personnalisée] WHERE ([Exercices].[Hiérarchie Exercices].[Année].&[2012].&[378691200].&[386553600],[Clients].[Hiérarchie Clients].[All],[Produits].[Hiérarchie Produits].[All]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

After my dimension Rentabilité script :

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <ParentObject>
        <DatabaseID>OlapCosting</DatabaseID>
    </ParentObject>
    <ObjectDefinition>
        <Dimension xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200">
            <ID>Return</ID>
            <Name>Rentabilité</Name>
            <Source xsi:type="DataSourceViewBinding">
                <DataSourceViewID>OlapCostingDb</DataSourceViewID>
            </Source>
            <UnknownMember>Visible</UnknownMember>
            <ErrorConfiguration>
                <KeyNotFound>ReportAndStop</KeyNotFound>
                <KeyDuplicate>ReportAndStop</KeyDuplicate>
                <NullKeyNotAllowed>ReportAndStop</NullKeyNotAllowed>
            </ErrorConfiguration>
            <Language>1036</Language>
            <Collation>French_CI_AS</Collation>
            <UnknownMemberName>Autres</UnknownMemberName>
            <Attributes>
                <Attribute>
                    <ID>Cost Id</ID>
                    <Name>Cost Id</Name>
                    <Usage>Key</Usage>
                    <KeyColumns>
                        <KeyColumn>
                            <NullProcessing>UnknownMember</NullProcessing>
                            <DataType>Integer</DataType>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>CostId</ColumnID>
                            </Source>
                        </KeyColumn>
                        <KeyColumn>
                            <NullProcessing>UnknownMember</NullProcessing>
                            <DataType>WChar</DataType>
                            <DataSize>1</DataSize>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>CostType</ColumnID>
                            </Source>
                        </KeyColumn>
                        <KeyColumn>
                            <NullProcessing>UnknownMember</NullProcessing>
                            <DataType>Integer</DataType>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>ProcId</ColumnID>
                            </Source>
                        </KeyColumn>
                    </KeyColumns>
                    <NameColumn>
                        <DataType>WChar</DataType>
                        <DataSize>211</DataSize>
                        <Source xsi:type="ColumnBinding">
                            <TableID>dbo_axis_Return</TableID>
                            <ColumnID>Label</ColumnID>
                        </Source>
                    </NameColumn>
                    <AttributeRelationships>
                        <AttributeRelationship>
                            <AttributeID>Parent Id</AttributeID>
                            <Name>Hiérarchie Rentabilité</Name>
                        </AttributeRelationship>
                        <AttributeRelationship>
                            <AttributeID>Code</AttributeID>
                            <Name>Codes Rentabilité</Name>
                        </AttributeRelationship>
                        <AttributeRelationship>
                            <AttributeID>Label</AttributeID>
                            <Name>Rentabilité</Name>
                        </AttributeRelationship>
                    </AttributeRelationships>
                    <OrderBy>Key</OrderBy>
                    <AttributeHierarchyVisible>false</AttributeHierarchyVisible>
                </Attribute>
                <Attribute>
                    <ID>Parent Id</ID>
                    <Name>Hiérarchie Rentabilité</Name>
                    <Usage>Parent</Usage>
                    <KeyColumns>
                        <KeyColumn>
                            <DataType>Integer</DataType>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>ParentId</ColumnID>
                            </Source>
                        </KeyColumn>
                        <KeyColumn>
                            <DataType>WChar</DataType>
                            <DataSize>1</DataSize>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>ParentType</ColumnID>
                            </Source>
                        </KeyColumn>
                        <KeyColumn>
                            <DataType>Integer</DataType>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>ParentProcId</ColumnID>
                            </Source>
                        </KeyColumn>
                    </KeyColumns>
                    <NameColumn>
                        <DataType>WChar</DataType>
                        <DataSize>211</DataSize>
                        <Source xsi:type="ColumnBinding">
                            <TableID>dbo_axis_Return</TableID>
                            <ColumnID>Label</ColumnID>
                        </Source>
                    </NameColumn>
                    <OrderBy>Key</OrderBy>
                    <NamingTemplate>Rentabilité</NamingTemplate>
                    <MembersWithData>NonLeafDataHidden</MembersWithData>
                </Attribute>
                <Attribute>
                    <ID>Code</ID>
                    <Name>Codes Rentabilité</Name>
                    <KeyColumns>
                        <KeyColumn>
                            <DataType>Integer</DataType>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>CostId</ColumnID>
                            </Source>
                        </KeyColumn>
                        <KeyColumn>
                            <DataType>WChar</DataType>
                            <DataSize>1</DataSize>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>CostType</ColumnID>
                            </Source>
                        </KeyColumn>
                        <KeyColumn>
                            <DataType>Integer</DataType>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>ProcId</ColumnID>
                            </Source>
                        </KeyColumn>
                    </KeyColumns>
                    <NameColumn>
                        <DataType>WChar</DataType>
                        <DataSize>50</DataSize>
                        <Source xsi:type="ColumnBinding">
                            <TableID>dbo_axis_Return</TableID>
                            <ColumnID>Code</ColumnID>
                        </Source>
                    </NameColumn>
                </Attribute>
                <Attribute>
                    <ID>Label</ID>
                    <Name>Rentabilité</Name>
                    <KeyColumns>
                        <KeyColumn>
                            <DataType>Integer</DataType>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>CostId</ColumnID>
                            </Source>
                        </KeyColumn>
                        <KeyColumn>
                            <DataType>WChar</DataType>
                            <DataSize>1</DataSize>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>CostType</ColumnID>
                            </Source>
                        </KeyColumn>
                        <KeyColumn>
                            <DataType>Integer</DataType>
                            <Source xsi:type="ColumnBinding">
                                <TableID>dbo_axis_Return</TableID>
                                <ColumnID>ProcId</ColumnID>
                            </Source>
                        </KeyColumn>
                    </KeyColumns>
                    <NameColumn>
                        <DataType>WChar</DataType>
                        <DataSize>211</DataSize>
                        <Source xsi:type="ColumnBinding">
                            <TableID>dbo_axis_Return</TableID>
                            <ColumnID>Label</ColumnID>
                        </Source>
                    </NameColumn>
                </Attribute>
            </Attributes>
            <ProactiveCaching>
                <SilenceInterval>-PT1S</SilenceInterval>
                <Latency>-PT1S</Latency>
                <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
                <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
                <Source xsi:type="ProactiveCachingInheritedBinding" />
            </ProactiveCaching>
        </Dimension>
    </ObjectDefinition>
</Create>

Jul 27, 2012 at 4:45 PM

If you change the calculated measure from:
([Measures].[Quantité Réel],[Rentabilité].[Rentabilité].&[269]&[T]&[0])

To:
([Measures].[Quantité Réel],[Rentabilité].[Hiérarchie Rentabilité].&[269]&[T]&[0])

Does that solve the problem?

 

Also, if that calculated measure causes all sorts of extra rows to show up, you might change it to:

IIf(
 IsEmpty([Measures].[Coût Réel])
 ,null
 ,([Measures].[Quantité Réel],[Rentabilité].[Hiérarchie Rentabilité].&[269]&[T]&[0])
)

Marked as answer by furmangg on 6/9/2014 at 12:57 AM
Jul 27, 2012 at 5:18 PM

Greats, now it's OK.

Thanks a lot