| toulon83 |
Posted - 07/28/2005 : 05:44:26
Symptom:
Following error message when performing a drillthrough with Report Portal on a filtered Analysis Services cube
"Error Data Source Provider Error. Statement(s) could not be prepared, 42000, the column prefix "dbo.DIM_FLEET" does not match with a table name or alias name used in the query., 42000"
Sequence of events:
step 1 - i have created a cube RELIABCL390CUBE
step 2 - in Analysis services, i have added a Source Table filter as follows
"dbo"."DIM_FLEET"."Fleet_Name" = 'CL390
where DIM_FLEET is my dimension for the fleet names, here i want to limit my cube's data to hold only record for fleet "CL390"
step 3 - The cube processes happily in Analysis Service to process only records on fleet "CL390"
step 4 - When i come to develop my OLAP report on reportportal, i will only be able to drillthrough, if i include the DIM_FLEET as a filter and select "CL390"
on the portal!
If i try to drillthrough without adding the DIM_FLEET dim as a filter i get the error message seen above.
I believe this is because the MDX statement built by report portal doesn't include the DIM_FLEET in the statement (when the user doesn't filter on that dimension in reportportal), whereas the Analysis Services cube includes this DIM_FLEET as its filter by default.
The same problem happens if the filter is placed on the drillthrough window in Analysis Services instead of the Source Table filter.
You suggested in the past we use the following code
Drillthrough maxrows 2500 select {(" + sTupple + ")} on columns From [" +
sCube + "]"
But none of our users program in MDX. And we don't intend to teach them.
One "workaround would be base the Analysis cube on a SQL Server view and to implement the filtering there instead of inside Analysis Services. But this defeats the object of building the cube on our star schemas and adds some complexity to the whole process.
Any ideas ?
Rgds
Frederic |
Report Portal
