views:

621

answers:

1

I currently have a query that looks like this:

SELECT
NON EMPTY ([Measures].[TOTAL]) ON 0,
NON EMPTY (([ENROLL DATE].[CALENDAR].[YEAR].[" + Parameters!EnrollDate.Value + "] * [DIM ENROLLMENT].[ENROLLMENT].[ENROLLMENT PROG].ALLMEMBERS)) ON 1

FROM (SELECT (([DIM ENROLLMENT].[ENROLLMENT].[TERMINATION REASON].[Still Enrolled])) ON 0 FROM [NapaCHI]);

The bold section comes from the parameters section where I manually specified the parameters with Label = 2006 and Value = 2006. I have tried just about every acceptable syntax and read about every article about this and cannot seem to get it to work whether I define the values myself or use a separate MDX statement that selects distinct non-empty years and (NULL) for the aggregate.

Help please, it's driving me insane! Thanks.

A: 

Your expression looks incomplete, you would need to start it with and = sign and quote it propertly. Something like the following

="SELECT
NON EMPTY ([Measures].[TOTAL]) ON 0, 
NON EMPTY (([ENROLL DATE].[CALENDAR].[YEAR].[" + Parameters!EnrollDate.Value + "] * [DIM ENROLLMENT].[ENROLLMENT].[ENROLLMENT PROG].ALLMEMBERS)) ON 1 

FROM (SELECT (([DIM ENROLLMENT].[ENROLLMENT].[TERMINATION REASON].[Still Enrolled])) ON 0 FROM [NapaCHI]);"

You may also have issues with datatypes, you might want to either use the .Label property of the parameter or explicitly convert the value to a string. If none of this works it would be helpful to know what error you are getting.

Darren Gosbell
I actually don't get any error, the query just doesn't return anything. I think you may be on the right track with the datatypes though but I'm not sure what it would be expecting there.
ajdams
It could be data types then. If your parameter passes in a full date, then it could expand to [Enroll Date].[Calendar].[Year].[01-01-2009] instead of semething like [Enroll Date].[Calendar].[Year].[2009] and this would result in an empty row axis.
Darren Gosbell