Change Dates to numbers

Question

This is the script:

SELECT 
  {[Measures].[Internet Order Count]} ON COLUMNS
 ,Descendants
  (
    [Date].[Calendar].[Month].[August 2006]
   ,[Date].[Calendar].[Date]
   ,self
  ) ON ROWS
FROM [Adventure Works];

It returns this:

enter image description here

Can I change the script so that instead of dates it returns integers starting at either 0 or 1 i.e. the first would be 1 the second would be 2 etc.

This adds a counter but I'd like to get rid of the date column:

WITH 
  MEMBER [Measures].[r] AS 
    Rank
    (
      [Date].[Calendar].CurrentMember
     ,[Date].[Calendar].[Month].[August 2006].Children
    ) 
SELECT 
  {
    [Measures].[r]
   ,[Measures].[Internet Order Count]
  } ON COLUMNS
 ,[Date].[Calendar].[Month].[August 2006].Children ON ROWS
FROM [Adventure Works];

@FlorianF although at first sight `MDX` does look like a sql dialect... but its lots more fun. — whytheq, Oct 27 '14 at 23:03

score 1 · Accepted Answer · answered Oct 28 '14 at 20:48

This will be difficult, as what you have on the rows and columns are sets of tuples of members, either physical or calculated ones.

What you could do of course is this:

WITH Member [Date].[Calendar].[1] AS [Date].[Calendar].[Date].&[20060801]
     Member [Date].[Calendar].[2] AS [Date].[Calendar].[Date].&[20060802]
     ...
     Member [Date].[Calendar].[31] AS [Date].[Calendar].[Date].&[20060831]
SELECT 
  {
   [Measures].[Internet Order Count]
  } ON COLUMNS
 ,
 {
 [Date].[Calendar].[1],
 [Date].[Calendar].[2],
 ...
 [Date].[Calendar].[31]
 }
 ON ROWS
FROM [Adventure Works]

This may be feasible in case you generate the query with a tool.

However, in cases like this, I normally would keep the query like your second query, and just ignore the row headers in the client tool.

Change Dates to numbers

1 Answers1