CODEWITHSUNDEEP
ssasmdxolap
whytheq
whytheq

Reputation: 35557

Change Dates to numbers

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];

Upvotes: 0

Views: 38

Answers (1)

FrankPl
FrankPl

Reputation: 13315

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.

Upvotes: 1

Related Questions