1

what mdx query logic could i implement for this example to get two rows in result set for hrid = 1 with 1/1/16 as min date(start) for first row where someattribut shows up on column with value 'A' and 1/15/16 as min date(start) for second row where someattribute has value of 'B' and measure.whatevers has its aggregation for whatever data corresponds to that dimension row.

Im trying to just look at january 2016

everything ive tried i seem to get min date values of 1/1/1900 or both rows have value of 1/1/2016 or i get errors since i cant figure it out.

enter image description here

heres my mdx sample:

WITH MEMBER [Measures].[Start] as 
  (
-- min date that the combination of someattribute and hrid have certain 
-- value withing the range of the where clause restriction of january 2016

SELECT { 
[Measures].[Start]
, [Measures].[Whatevers]
} ON COLUMNS
, NON EMPTY { 
[Agent].[HRID].children
* [Agent].[someAtribute].Members
} ON ROWS
FROM [RADM_REPORTING]
WHERE ( 
    [Date].[Date View].[Month].&[201601]
    )
Vernard Sloggett
  • 336
  • 4
  • 10

2 Answers2

0

Here is a potential direction that is more general:

WITH 
  MEMBER [Measures].[Start] AS 
    Min
    (
      (EXISTING 
        [Date].[Date].[Date].MEMBERS)
     ,IIF
      (
        [Measures].[Internet Sales Amount] = 0
       ,NULL
       ,[Date].[Date].CurrentMember.MemberValue
      )
    ) 
SELECT 
  NON EMPTY 
    {
      [Measures].[Start]
     ,[Measures].[Internet Sales Amount]
    } ON COLUMNS
 ,NON EMPTY 
    [Product].[Product Categories].[Product] ON ROWS
FROM [Adventure Works]
WHERE 
  [Date].[Calendar].[Calendar Year].&[2005];

It gives the following:

enter image description here

whytheq
  • 34,466
  • 65
  • 172
  • 267
  • The ITEM function expects a tuple set expression for the 1 argument. A string or numeric expression was used. – Vernard Sloggett Feb 12 '16 at 18:36
  • modifying like so will give me 1900-01-01 on every row WITH MEMBER [Measures].[Start] as {[Date].[Date View].[Date]}.item(0).membervalue – Vernard Sloggett Feb 12 '16 at 19:05
  • none of the products appear twice with a separate attribute value , if the product is analogous to the hrid then some attribute of the same dimension (product categories)would have to change say 'Road-150 Red, 44' had an attribute of price and from 6/25/2005 the price was 10 dollars and from 7/15/2005 the price was 11 dollars and the date range was filtered to show only sales for july 2005, then were looking for a start of 7/1/2015 for $10 and a start of 7/15/2005 for 11 dollars and the price attribute was included in the on rows set. – Vernard Sloggett Apr 14 '16 at 23:33
0

this works, but it feels kind of like a hack or maybe it feels like its not robust, I am not familiar enough with mdx to be able to make that call.

WITH MEMBER [Measures].[Start] as 
 filter([Date].[Date View].[Month].&[201601].children, 
 [Measures].[Whatevers]).item(0).membervalue
Vernard Sloggett
  • 336
  • 4
  • 10
  • this is just hard coded `..[Month].&[201601].` you could really try to generalise via the `EXISTING` key word. – whytheq Feb 13 '16 at 01:12
  • ok - now I have advWrks to test idea - I've amended my answer - apologies for the rubbish I posted previously! – whytheq Feb 13 '16 at 18:24