October 24, 2007 at 4:11 am
SELECT NON EMPTY { NONEMPTYCROSSJOIN( { [DATA SOURCE].[Reporting Source Nm].[All].CHILDREN }, { [Measures].[Product Quantity], [Measures].[Net Dollar Amt] },
{PeriodsToDate([DATE].[Calendar Year Month].[200701]:[DATE].[Calendar Year Month].[201006])},
{ [DATA SOURCE].[Source Nm].[All].CHILDREN }, { [DATA MEASURE TYPE].[Data Measure Type Nm].[All].CHILDREN }, { [SALES GEOGRAPHY].[Region Nm].[All].CHILDREN }, { DESCENDANTS( [SALES GEOGRAPHY].[Geography Hierarchy].[All], [SALES GEOGRAPHY].[Geography Hierarchy].[Geo Level1] ) }, 2 ) } ON COLUMNS ,
NON EMPTY { NONEMPTYCROSSJOIN( { [DATA SOURCE].[Source Nm].[All].CHILDREN }, { [DATA MEASURE TYPE].[Data Measure Type Nm].[All].CHILDREN }, { [SALES GEOGRAPHY].[Region Nm].[All].CHILDREN }, { DESCENDANTS( [SALES GEOGRAPHY].[Geography Hierarchy].[All], [SALES GEOGRAPHY].[Geography Hierarchy].[Geo Level1] ) },
{PeriodsToDate([DATE].[Calendar Year Month].[200701]:[DATE].[Calendar Year Month].[201006] )}, { [DATA SOURCE].[Reporting Source Nm].[All].CHILDREN }, { [Measures].[Product Quantity], [Measures].[Net Dollar Amt] }, 4 ) } ON ROWS
FROM [Data Quality]
WHERE ( [DATE].[Calendar Year Month].&[200707] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE
#############
When I run this MDX query I get an error "Query failed:Query(2,2) The PERIODSTODATE function expects a level expression for the argument. A tuple set expression was used."
Moreover I need the filter in the format of "mm/yyyy". how to do this?
November 11, 2007 at 10:06 pm
Debasish,
You've probably worked this out already, but here's a couple of answers.
1. Your use of the PeriodsToDate function is incorrect. You need to specify a level from the start of which you'd like to include periods, then optionally specify the member period to which you'd like to include. If you wanted to show from 200701 to 201006, try removing the PeriodsToDate function altogether. If you wanted to see from the start of the year until June, you could use something along the lines of "PeriodsToDate([Year],[June])" where [June] is the name of the month member you're going to.
2. Why do you need to change the format of the filter? What you'd be doing in this case is changing the key of the calendar month, and in its current format the key is useful for sorting. You could attach a name to the hierarchy and reference the name instead, which might have the format mm/yyyy. One way to do this would be to add a named calculation to the data source view, which creates the string in SQL syntax, then use this calculation as the source for the NameColumn month attribute in your Date dimension. There may be better ways to do this, but this is how I got it to work.
Sam
November 13, 2007 at 8:56 am
I don't expect you'll be able to get this to work:
- You have a number of dimensional attributes that are referenced on both the columns and the rows. You can only reference an attribute (or hierarchy) once. (Remember, the cross product of an attribute with itself is a highly sparse, trivial result set.)
- Sam is quite correct on your use of the PeriodsToDate function. Morover, I'd like to verify that [DATE].[Calendar Year Month].[201006] is a correct reference. (Should it be 200706?). This seems to be in conflict with your filter statement ([DATE].[Calendar Year Month].&[200707]). What periods were you attempting to display?
- I noticed that you're using a name reference on the period range. An ID reference is faster (I know, this is a small dimension. Good form anyways.). Plus that gives you the option of formatting the name however you want.
I'm not sure what reporting tool you're using (i.e. this won't work in Report Services, but I I'd suggest simplifying this query a little:
SELECT NON EMPTY
{ [Measures].[Product Quantity]
, [Measures].[Net Dollar Amt]
} ON Columns
, Non Empty
{ [DATA SOURCE].[Reporting Source Nm].[All].Children
, [DATA SOURCE].[Source Nm].[All].Children
, [DATA MEASURE TYPE].[Data Measure Type Nm].[All].Children
, [SALES GEOGRAPHY].[Region Nm].[All].Children
, [SALES GEOGRAPHY].[Geography Hierarchy].[Geo Level1].Members
, { [DATE].[Calendar Year Month].[200701]:[DATE].[Calendar Year Month].[201006] }
, [DATA SOURCE].[Reporting Source Nm].[All].Children
} ON Rows
FROM [Data Quality]
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE
P.S. This looks like a big pull. How many members are in each attribute?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply