AS Cube as DataSet for RS Multi-Value & Range Parameters?

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.

AS Cube as DataSet for RS Multi-Value & Range Parameters?

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.

Arrange Items in Solution Explorer

Can the items, i.e. reports within the SSRS Report Designer Solution Explorer be arranged in a custom manner instead of being sorted alphabetically only?

Hi.

The reports in the solution explorer are ordered alphabetically when you open a solution/project; when you add them to an open solution/project they tend to be added just to the end.

I dont know of any way to change the default ordering, but as workaround you could just add a number prefix to the title of each report (e.g. 01-This report is top.rdl, 02-Another report.rdl etc.) so they are ordered in the way you want.

Hope this helps,

Gary.

|||Hi Gary,

Thanks for the reply.

I really want to avoid changing the report object names. Perhaps I will just leave it for now since it is just a cosmetical issue anyway.

Thanks again.

Kenny

Arrange Items in Solution Explorer

Can the items, i.e. reports within the SSRS Report Designer Solution Explorer be arranged in a custom manner instead of being sorted alphabetically only?

Hi.

The reports in the solution explorer are ordered alphabetically when you open a solution/project; when you add them to an open solution/project they tend to be added just to the end.

I dont know of any way to change the default ordering, but as workaround you could just add a number prefix to the title of each report (e.g. 01-This report is top.rdl, 02-Another report.rdl etc.) so they are ordered in the way you want.

Hope this helps,

Gary.

|||Hi Gary,

Thanks for the reply.

I really want to avoid changing the report object names. Perhaps I will just leave it for now since it is just a cosmetical issue anyway.

Thanks again.

Kenny

Arrange Items in Solution Explorer

Can the items, i.e. reports within the SSRS Report Designer Solution Explorer be arranged in a custom manner instead of being sorted alphabetically only?

Hi.

The reports in the solution explorer are ordered alphabetically when you open a solution/project; when you add them to an open solution/project they tend to be added just to the end.

I dont know of any way to change the default ordering, but as workaround you could just add a number prefix to the title of each report (e.g. 01-This report is top.rdl, 02-Another report.rdl etc.) so they are ordered in the way you want.

Hope this helps,

Gary.

|||Hi Gary,

Thanks for the reply.

I really want to avoid changing the report object names. Perhaps I will just leave it for now since it is just a cosmetical issue anyway.

Thanks again.

Kenny

ARE THERE ANY GOOD SSRS FORUMS ANYWHERE?

I use a lot of the Microsoft forums and they are all responsive and
valuable - except for this one. I never get a question answered here. I
notice about a third of the people do ever get answers.
In addition to this SSRS is a very problematic software system with lots of
quirks.
Does anyone know of any good support forums on SSRS?
Also is there any good books? (I don't like my WROX book).
Thanks,
TThis forum is good for two things. One is an answer from me because this is
where I hang out <g> but eventually I will move on too. This is also where
you get managed newsgroup support (if you have a msdn subscription). If
using that then you are guaranteed an answer in this newsgroup.
Otherwise, the best place to go are the web based forums. That is really
where the action is for RS. This newsgroup is dying.
http://forums.microsoft.com/msdn/default.aspx?forumgroupid=19&siteid=1
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
news:uSBR2H$lGHA.4808@.TK2MSFTNGP05.phx.gbl...
>I use a lot of the Microsoft forums and they are all responsive and
>valuable - except for this one. I never get a question answered here. I
>notice about a third of the people do ever get answers.
> In addition to this SSRS is a very problematic software system with lots
> of quirks.
> Does anyone know of any good support forums on SSRS?
> Also is there any good books? (I don't like my WROX book).
> Thanks,
> T
>|||Is there any "admin" person on this forum who could close this newsgroup and
redirect people to the MSDN forum as linked below?
We should have one forum where everyone goes rather than splitting the
community amongst two places.
"Bruce L-C [MVP]" wrote:
> This forum is good for two things. One is an answer from me because this is
> where I hang out <g> but eventually I will move on too. This is also where
> you get managed newsgroup support (if you have a msdn subscription). If
> using that then you are guaranteed an answer in this newsgroup.
> Otherwise, the best place to go are the web based forums. That is really
> where the action is for RS. This newsgroup is dying.
> http://forums.microsoft.com/msdn/default.aspx?forumgroupid=19&siteid=1
> http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
> news:uSBR2H$lGHA.4808@.TK2MSFTNGP05.phx.gbl...
> >I use a lot of the Microsoft forums and they are all responsive and
> >valuable - except for this one. I never get a question answered here. I
> >notice about a third of the people do ever get answers.
> >
> > In addition to this SSRS is a very problematic software system with lots
> > of quirks.
> >
> > Does anyone know of any good support forums on SSRS?
> >
> > Also is there any good books? (I don't like my WROX book).
> >
> > Thanks,
> > T
> >
>
>|||What do you mean "this is where you get managed newsgroup support (if you
have a msdn subscription)" ? I have an msdn subscription and I get many
questions answers but not all - so there must not be any "guaraantee" unless
I dont know how to enter this newsgroup from the proper channel. I would like
to know how to enter as an msdn subscriber in that case. Thanks Bruce.
"Bruce L-C [MVP]" wrote:
> This forum is good for two things. One is an answer from me because this is
> where I hang out <g> but eventually I will move on too. This is also where
> you get managed newsgroup support (if you have a msdn subscription). If
> using that then you are guaranteed an answer in this newsgroup.
> Otherwise, the best place to go are the web based forums. That is really
> where the action is for RS. This newsgroup is dying.
> http://forums.microsoft.com/msdn/default.aspx?forumgroupid=19&siteid=1
> http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
> news:uSBR2H$lGHA.4808@.TK2MSFTNGP05.phx.gbl...
> >I use a lot of the Microsoft forums and they are all responsive and
> >valuable - except for this one. I never get a question answered here. I
> >notice about a third of the people do ever get answers.
> >
> > In addition to this SSRS is a very problematic software system with lots
> > of quirks.
> >
> > Does anyone know of any good support forums on SSRS?
> >
> > Also is there any good books? (I don't like my WROX book).
> >
> > Thanks,
> > T
> >
>
>|||As far as I know, you have to use the same e-mail address on here that is
registered with your MSDN subscription
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:EE9CBA38-603C-42DA-BC48-AA0D3B59F890@.microsoft.com...
> What do you mean "this is where you get managed newsgroup support (if you
> have a msdn subscription)" ? I have an msdn subscription and I get many
> questions answers but not all - so there must not be any "guaraantee"
> unless
> I dont know how to enter this newsgroup from the proper channel. I would
> like
> to know how to enter as an msdn subscriber in that case. Thanks Bruce.
> "Bruce L-C [MVP]" wrote:
>> This forum is good for two things. One is an answer from me because this
>> is
>> where I hang out <g> but eventually I will move on too. This is also
>> where
>> you get managed newsgroup support (if you have a msdn subscription). If
>> using that then you are guaranteed an answer in this newsgroup.
>> Otherwise, the best place to go are the web based forums. That is really
>> where the action is for RS. This newsgroup is dying.
>> http://forums.microsoft.com/msdn/default.aspx?forumgroupid=19&siteid=1
>> http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
>> news:uSBR2H$lGHA.4808@.TK2MSFTNGP05.phx.gbl...
>> >I use a lot of the Microsoft forums and they are all responsive and
>> >valuable - except for this one. I never get a question answered here.
>> >I
>> >notice about a third of the people do ever get answers.
>> >
>> > In addition to this SSRS is a very problematic software system with
>> > lots
>> > of quirks.
>> >
>> > Does anyone know of any good support forums on SSRS?
>> >
>> > Also is there any good books? (I don't like my WROX book).
>> >
>> > Thanks,
>> > T
>> >
>>|||Thanks Dave - I am not sure that guarantees me a response here though ...
that was my question ... since I havent always received one. However that
isnt meant as a complaint because the people here are pretty good about
answering things they have experienced themselves.
"Dave Frommer" wrote:
> As far as I know, you have to use the same e-mail address on here that is
> registered with your MSDN subscription
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:EE9CBA38-603C-42DA-BC48-AA0D3B59F890@.microsoft.com...
> > What do you mean "this is where you get managed newsgroup support (if you
> > have a msdn subscription)" ? I have an msdn subscription and I get many
> > questions answers but not all - so there must not be any "guaraantee"
> > unless
> > I dont know how to enter this newsgroup from the proper channel. I would
> > like
> > to know how to enter as an msdn subscriber in that case. Thanks Bruce.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> This forum is good for two things. One is an answer from me because this
> >> is
> >> where I hang out <g> but eventually I will move on too. This is also
> >> where
> >> you get managed newsgroup support (if you have a msdn subscription). If
> >> using that then you are guaranteed an answer in this newsgroup.
> >>
> >> Otherwise, the best place to go are the web based forums. That is really
> >> where the action is for RS. This newsgroup is dying.
> >>
> >> http://forums.microsoft.com/msdn/default.aspx?forumgroupid=19&siteid=1
> >>
> >> http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Tina" <tinamseaburn@.nospammeexcite.com> wrote in message
> >> news:uSBR2H$lGHA.4808@.TK2MSFTNGP05.phx.gbl...
> >> >I use a lot of the Microsoft forums and they are all responsive and
> >> >valuable - except for this one. I never get a question answered here.
> >> >I
> >> >notice about a third of the people do ever get answers.
> >> >
> >> > In addition to this SSRS is a very problematic software system with
> >> > lots
> >> > of quirks.
> >> >
> >> > Does anyone know of any good support forums on SSRS?
> >> >
> >> > Also is there any good books? (I don't like my WROX book).
> >> >
> >> > Thanks,
> >> > T
> >> >
> >>
> >>
> >>
>
>|||Dave Frommer wrote:
> As far as I know, you have to use the same e-mail address on here
> that is registered with your MSDN subscription
Even if that's true (and I'll need some convincing), there's no way in hell
I'd ever post anything anywhere with my real email address. I get quite
enough spam and junk in my email as it is, without wanting to invite more
from anyone that's capable of reading email messages from Microsoft's news
servers...
--
(O)enone|||Actually here are the instructions. Also allows you to register a
"nospam" alias e-mail account.
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx