I can't show a calculated member

I need to show a calculated member in a report.
Reports works fine with calculated measures, but when you try to use a calculated member then raises an error which says The following system error ocurred. Invalid procedure call or argument.
Any ideas?
Can I use calculated members?
I'm using RS 2005 April CTP.
Thanks in advance.I would very much recommend moving to the June CTP. Many things are more stable, including Analysis Services. There is no reason why calculated members shouldn't work.|||Using the Sept CTP, when I create a data source that includes a hierarchy that includes a calculated member, I get an error message approximately "Encountered a set that cannot include a calculated member".

So it seems, there is a real issue here not solved by recent releases.|||

I also am having difficulties with calculated members using the Eval version of AS2005 and RS2005.

1) I am sure that everyone knows about this error: Add a calculated member (in BI Management Studio or RS), choose a parent heirarchy other than Measures, choose a parent member. If the dimension and hierarchy were both called Stock then the parent hierarchy would be Stock.Stock and the parent member would be [Stock].[Stock]. An error is given when the script/query is run. Changing to text mode shows that the designer form has placed an extra level in the WITH/CREATE MEMBER statement, ie [Stock].[Stock].[Stock].[Stock].[Calculated Member Name]. I worked out that you could work around this by deleting one of the repeated levels, BUT in RS, some extra problems arise: 1a) I can't change back to form mode because my query will be lost and, 1b) If I try to add this calculated member to my set of displayed members, it simply won't show.

2) I attempted to get around the problems described above by creating the same calculated member in the source cube. When I try to include it I get the same error about "Encoutered a set that cannot contain calculated members". This error also occurs if I paste the same RS built query into SQL Mgmt Studio. Is it possible to use calculated members that are not Measures in RS?

3) I have another problem which is related to the fact that RS can't accept anything but Measures for the columns of the query. My fact table has only one value column and hence my cube has only one Measure. Is there any way of making the RS query return multiple value columns if the source is a cube with only one Measure?

Anybody got any ideas?

Aranda

|||We submitted an issue with MS about this and were told that this was by design. If I remember correctly, it had something to do with how RS uses a subcube and calc members not being supported in subcubes.|||

On your point #3, our cubes are constructed the same way.

I was able to solve this (sort of) by creating calculated members on the measures dimension that picked up data from other dimensions -- that is, I used calculated members to pivot the data. It's quite tedious, but effective. However there are some limitations I found:

a. those calculated members don't rollup to higher levels very well

b. I frequently encountered an unsecified "query error" when running the data. the e rror appears to be data-dependant but I haven't been able to track it down.

My bottom line is that RS really can't be used effectively with AS because of the following limitations:

1. Only measures as columns and pivoting measures are unstable

2. You really can't effectively use a cube that has any calculated members defined in any dimiension other than measures. So If you put in a CM on a dimension for some other use, you have contaminated that dimension and it can't be used in RS. Further, RS's reliance on subqueries renders CM's useless.

So these two facts tell me that microsoft has broken its promise (?) that AS would be a general platform whose results can be accessed by a multitude of applications. If you want to use AS with RS, you have to get rid of all Calculated members and calculated cells (except on measures dimension). (btw calculated cells fly below the radar on RS -- so it won't mask them out -- but the calculations don't work if you're using subqueries). Combine this with the fact that MS has eliminated the ability to create a calculated member in a query that has a solve order lower than one in the cube and have those calculations executed inthe proper sequence. So really, MS is telling us that calculated members do not belong in cubes -- only at the query level. what a giant take-away that is.

|||

Thank's Stan H for your useful reply, but it looks like the situation is as I feared. My bottom line is pretty much the same as yours - it really is a massive take-away because calculated members are pretty much what makes cubes worthwile for me.

I will give pivoting measures a try but it doesn't sound too hopeful.

|||

The 'A set has been encountered that cannot contain calculated members' error comes when you try to slice by a set which contains a calculated member in the RS MDX query designer (I've not had any problems putting calculated members on rows in my queries in the RTM version), and as has already been said it's to do with the fact that RS uses subcubes to do its filtering. You can get round this limitation by editing the MDX directly, though, and although you then lose the ability to use the query designer you can at least get your queries to run...

Here's an example in Adventure Works. Say I've got a hierarchy created by the Time Intelligence Wizard on the Date dimension called [Calendar Date Calculations], with one calculated member on it called [Year to Date]. If you try to slice by this member you get the aforementioned error; switching to view the MDX, you see something like this:

SELECT NON EMPTY { [Measures].[Internet Order Count] }
ON COLUMNS,
NON EMPTY { ([Date].[Calendar].[Calendar Semester].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM
( SELECT ( { [Date].[Calendar Date Calculations].[Year to Date] } ) ON COLUMNS FROM [Adventure Works]) WHERE ( [Date].[Calendar Date Calculations].[Year to Date] )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

To get the desired effect, you need to edit the query to replace the subcube filter with a standard WHERE clause filter:

SELECT NON EMPTY { [Measures].[Internet Order Count] }
ON COLUMNS,
NON EMPTY { ([Date].[Calendar].[Calendar Semester].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [Adventure Works]
WHERE ( [Date].[Calendar Date Calculations].[Year to Date] )
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

In my experience you can almost always write MDX to make RS do what you want, but I 100% agree that RS shouldn't expect you to do this and the MDX query designer should be able to handle this particular scenario.

HTH,

Chris

|||

Thanks Chris, that was quite helpful.

I wish there was a way to instruct the query builder to avoid subqueries. I am finding their limitations to overwhelm their value.