Issue with a Crosstab report using Calculated columns

[vissuma]

Hi,

I have Crosstab report with 12 calculated columns using Anlaysis Services Cube as  datasource. Sometimes the report is running fine with correct result set, sometimes it is coming up with '#!ERROR' in all the cells of crosstab, and sometimes it is throwing up an error message as "Unable to perform the requested operation on the data set object. It is not accessible because underlying data has been changed". can anyone had experienced this kind of situation before? Your help is highly appreciated.

Vissuma

[Suraj]

I have seen #!ERROR when the calculation is looking for a value that is not available from the dimension or level that you are pointing to.
Sometimes, it happens when there is a 0 in denominator.
When doing calculations with cube data, always use mdx functions to extract the value of a level instead of just dragging and dropping the level.

[vissuma]

Hi Suraj,

Thanks for your reply. When I run the report some times I used to get the correct result set and sometimes I used the get #!ERROR in the Corsstab cells and sometimes I used to get the error message. My specific question is why the same report is giving different outputs like correct one, #!ERROR in the crosstab cells, and an error message? Is there anything to do with environment or Report?

Vissuma

[Suraj]

That's odd.
Looks like the errors are coming up during run time.
One thing I can suggest is to check solve order of the query items.
It may not help but just an idea that came up.

[vissuma]

Hi Suraj,

Even I gave Solve order for the calculated items but still I am getting the same weird behavior when I run the report.

Thanks,

Vissuma