StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMDX Query use most recent value
text
Body
copied!<p>Hi<br> I am no MDX Expert, so I need help, say I have a cube with the following data (simplified)<br> Month EE CoCode Value </p> <pre><code>jan A 1 100 feb A 1 200 Mar A 2 400 Jan B 1 150 Feb B 1 100 Jan C 2 100 Mar C 2 650 </code></pre> <p>Month, EE and CoCode are all separate dimensions<br> EE A changed cocode in march, so I want to report on his total YTD<br> Result in report I want is </p> <pre><code>EE CoCode Total C 2 750 A 2 700 B 1 250 </code></pre> <p>Not sure if solution is in<br> the MDX(using currentmember or something for CompanyCode),<br> the cube(create new attribute in CompanyCode Dimennsion for currentCoCode)<br> or in grouping in the report.<br> Thanks paul<br> Actual MDX I am using uses parameters and is a bit more complicated as follows </p> <pre><code>="SELECT NON EMPTY { [Measures].[ValueCode] } ON COLUMNS, topcount(NONEMPTY ([Dim E Eno].[EE No].[EE No].ALLMEMBERS * [Dim E Eno].[Sur Name].[Sur Name].ALLMEMBERS * [Dim E Eno].[First Name].[First Name].ALLMEMBERS * [DIM Cost Centres].[Cost Centre].[Cost Centre].ALLMEMBERS * [DIM Location Codes].[Location Code].[Location Code].ALLMEMBERS * [DIM Grades].[Grade Code].[Grade Code].ALLMEMBERS * [DIM Company Codes].[Company Code].[Company Code].ALLMEMBERS * [Dim Codes].[Description].[Description].ALLMEMBERS * [Dim Codes].[Code].[Code].ALLMEMBERS * [Dim Codes].[Long Description].[Long Description].ALLMEMBERS ),"+Parameters!P_TopN.Value+",[Measures].[ValueCode] ) ON ROWS FROM ( SELECT ( -{ [DIM Cost Centres].[Cost Centre].&[9006890557], [DIM Cost Centres].[Cost Centre].&[NA18350157], [DIM Cost Centres].[Cost Centre].&[NA18312257], [DIM Cost Centres].[Cost Centre].&[WB18350357], [DIM Cost Centres].[Cost Centre].&[EC18350157], [DIM Cost Centres].[Cost Centre].&[HSS8800257] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DIMCompanyCodesCompanyCode, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimDatesYear, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Dim Codes].[Code].&["+Parameters!P_Reports.Value+"] } ) ON COLUMNS FROM [DW Datatrieve])))) WHERE ( IIF( STRTOSET(@DimDatesYear, CONSTRAINED).Count = 1, STRTOSET(@DimDatesYear, CONSTRAINED), [Dim Dates].[Year].currentmember ) ) " </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload