March 3, 2005 at 8:45 am
Hi,
I have a cube with three dimension x, y and z say.
The measure I want to use is a string value (the data has lots of n/a's and *'s). Does anyone know of an easy way I can implement this so when I crosstab x v y I can return the values and n/a's and *'s.
I have tried changing the data type of the measure to varchar and aggregation method to min, but this doesn't work. The cube complains that the "datatype is not valid".
I have also tried creating a fourth dimension with just the values in, I then created a member property for each value. However, when I try to create a calculated measure of the member property, it simply shows #err in the cube.
What am I doing wrong? How can I implement this design?
Chris
March 4, 2005 at 1:18 pm
Try taking a look at "Semi-Additive Measures" In the Microsoft Knowledge Base and on one of the popular search engines. An example is below:
http://www.databasejournal.com/features/mssql/article.php/3445761
March 6, 2005 at 7:37 pm
Not knowing what the range of possible values are whenthe measure is numeric (ie can they go negative), the following might work for you.
If you can change you string measure to numeric (say in a view) and set all of the "n/a's" to null and "*'s" to say -1, then you can add this as a standard measure. Then create a calculated measure based on this measure, use one or more IIF statements (like IIF([measures].[bob] = -1, "*", IIF(ISNULL([measures].[bob]) = TRUE, "n/a", CString([measures].[bob]))
This should then let you have the numeric and non-numeric measure values.
NB This is very likely to cause issues when rolling up (ie if the values are -1, -1, 5, null, then the rolled up value for the parent (using sum) is 3, which is what will be displayed).
Steve.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply