Platform: All SQL '05 Std
I'm struggling with two issues involving the use of AS Cube data as parameters in a SSRS Report.
Background: I've built a report exclusively using an AS cube as it's data source, including the source for multi-value and range parameters. As a note, the cube is complex and at least one of the dimensions is large (~10,000 attribute values). Lastly, the date field being used is a "date time" datatype (more on this in a moment).
The two issues are...
(1) Even after initial caching (both in VS and as deployed to Report Mgr), the entry of all parameter values suffers long delays (~40 seconds) during apparent data refreshing of tyhe small dimensions, and > 60 seconds for the large dimension, in the parameter list. If possible for the large dimension, I'd like to create a separate lookup table for the large dimension, but it does not look straightforward when my data source is the OLAP cube. QUESTION: Is this possible to do, and/or is there a better way?
(2) My date dimension field that records the actual date (ig. yyyy-mm-dd) also contains, as a date-time datatype field, the HH:MM:SS values (hh:mm:ss is all zero's, by the way). In other words, the values look like yyyy-mm-dd hh:mm:ss . Anyway, SSRS is throwing an error on the " : " (colon) symbols. Of course, if this field were being used as an output field, I could just change the field format in SSRS, but it's not output, it's an input parameter, and I don't know how it's possible to alter formatting on such a field. In the VS preview, the following error appers...
"An error occurred during local report processing. An error has occurred during report processing. Query execution failed for data set "FromDimSvcDateFullDate". Range operator (:) operands have different levels; they must be the same"
QUESTIONS: (a) Is it possible, either in SSAS or in SSRS, to alter formatting on the date field to remove the hh:mm:ss info? (b) What other solutions are suggested here?
Note: If I have to go back to my source data and change the data type, I'm afraid that my OLAP report (and others) will no longer function.
Note: Nobody on the SSRS thread is apparently able to provide feedback on this SSAS / SSRS issue.
(1): To isolate whether the delays are occurring in MSAS, the report (or both), you can trace MDX query execution times for SSAS by using SQL Profiler. If some of the MDX parameter queries are taking too long, maybe those could be tuned.
(2): What does the MDX query for the FromDimSvcDateFullDate data set look like - typically, there will be calculated measures defined, like ParameterCaption and ParameterValue? You should then be able to edit the definition appropriately to customize the data returned, using theVBA data coversion functions supported in MDX.
|||The reason you were getting this error:
"An error occurred during local report processing. An error has occurred during report processing. Query execution failed for data set "FromDimSvcDateFullDate". Range operator (:) operands have different levels; they must be the same"
is most likely because you picked different Levels of dates for the FROM and TO in reporting service: Ie: "2004Q1" and "2006". It should have nothing to do with the colons in the dates. Picking 2004-01-01 00:00:00 to 2004-01-03 00:00:00 doesn't render an error.
I found this post when I was looking for a clean way around this or a way to give the user a more friendly message in this case... I'm still looking though :)
|||Thank you for responding.