Reg -MDX in Query issue

Hi,
I am using the following query in reporting services
="With Member [Measures].[Time] as '[TimeDim].CurrentMember.UniqueName'
MEMBER [Measures].[DisplayTime] AS '[TimeDim].CurrentMember.Name' SELECT
{[Measures].[Time],[Measures].[DisplayTime]} on columns, {Descendants({" &
Parameters!Year.value & "},[TimeDim].[Actual Date],BEFORE)} on rows from
ICMDestinationService"
I have given even spaces between the Query.
I am getting the following error
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot set the command text for data set 'Time_Dim'.
(rsErrorSettingCommandText) Get Online Help
Error during processing of the CommandText expression of dataset â'Time_Dimâ'.
(rsQueryCommandTextProcessingError)
Any ideas or solutionVerify the MDX runs in the MDX Sample Application first.
Also, make sure you are using Generic Query Designer - 3rd or 4th button to
the right of dataset dropdown in data window
Finally, be sure there are no carriage returns in the query.
If still having problems, see advice offered in
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Yalaman" <Yalaman@.discussions.microsoft.com> wrote in message
news:4BADB3C5-20E1-495C-B1A0-8914E30E34DE@.microsoft.com...
> Hi,
> I am using the following query in reporting services
> ="With Member [Measures].[Time] as '[TimeDim].CurrentMember.UniqueName'
> MEMBER [Measures].[DisplayTime] AS '[TimeDim].CurrentMember.Name' SELECT
> {[Measures].[Time],[Measures].[DisplayTime]} on columns, {Descendants({" &
> Parameters!Year.value & "},[TimeDim].[Actual Date],BEFORE)} on rows from
> ICMDestinationService"
> I have given even spaces between the Query.
>
> I am getting the following error
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot set the command text for data set 'Time_Dim'.
> (rsErrorSettingCommandText) Get Online Help
> Error during processing of the CommandText expression of dataset 'Time_Dim'.
> (rsQueryCommandTextProcessingError)
> Any ideas or solution
>

Reg - Passing parameters to MDX Queries

Hi,
I am trying to use parameters in MDX Queries From one data set to other data
Set I created a data set which gives me Time values.
I am using this as parameter value as I do in SQL Queries.
Ex -
SELECT {[Measures].[Unit Sales]} on columns,
ORDER([Promotion Media].[Media Type].members,[Measures].[Unit Sales],DESC)
on rows FROM SALES where @.Time
When run It does'nt populate the value for the @.Time parameter
When i preview the report it populates the parameter values but when the
report view button is clicked it fails to build the Query because it cannot
populate the string.
Is there any other way to use the parameters and pass the parameters in MDX
Queries?
if possible with Examples
Thanks,Hi!
In MDX, this is not so simple. Checkt the article at
http://www.databasejournal.com/features/mssql/article.php/10894_3386441_1
for the procedure.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"Yalaman" <Yalaman@.discussions.microsoft.com> wrote in message
news:413ED68C-5AD5-4143-B079-1ADDE5DB6EF2@.microsoft.com...
> Hi,
> I am trying to use parameters in MDX Queries From one data set to other
data
> Set I created a data set which gives me Time values.
> I am using this as parameter value as I do in SQL Queries.
> Ex -
> SELECT {[Measures].[Unit Sales]} on columns,
> ORDER([Promotion Media].[Media Type].members,[Measures].[Unit Sales],DESC)
> on rows FROM SALES where @.Time
> When run It does'nt populate the value for the @.Time parameter
> When i preview the report it populates the parameter values but when the
> report view button is clicked it fails to build the Query because it
cannot
> populate the string.
>
> Is there any other way to use the parameters and pass the parameters in
MDX
> Queries?
> if possible with Examples
> Thanks,
>
>
>
>
>|||The OleDB provider for AS 2000 does not support parameterized MDX queries.
This MSDN article explains how to achieve parameterized MDX in RS 2000:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp
In addition, you may want to download this sample:
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-9c83-c6801f0576ff&DisplayLang=en
BTW: RS 2005 Beta 2 contains graphical and text-based query designers for
MDX and DMX. They also support single-value parameters at this point.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:e6eUiDqrEHA.1964@.TK2MSFTNGP12.phx.gbl...
> Hi!
> In MDX, this is not so simple. Checkt the article at
> http://www.databasejournal.com/features/mssql/article.php/10894_3386441_1
> for the procedure.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "Yalaman" <Yalaman@.discussions.microsoft.com> wrote in message
> news:413ED68C-5AD5-4143-B079-1ADDE5DB6EF2@.microsoft.com...
> > Hi,
> >
> > I am trying to use parameters in MDX Queries From one data set to other
> data
> > Set I created a data set which gives me Time values.
> >
> > I am using this as parameter value as I do in SQL Queries.
> >
> > Ex -
> > SELECT {[Measures].[Unit Sales]} on columns,
> > ORDER([Promotion Media].[Media Type].members,[Measures].[Unit
Sales],DESC)
> > on rows FROM SALES where @.Time
> >
> > When run It does'nt populate the value for the @.Time parameter
> >
> > When i preview the report it populates the parameter values but when the
> > report view button is clicked it fails to build the Query because it
> cannot
> > populate the string.
> >
> >
> > Is there any other way to use the parameters and pass the parameters in
> MDX
> > Queries?
> > if possible with Examples
> >
> > Thanks,
> >
> >
> >
> >
> >
> >
> >
> >
> >
>

Refresh calculation when working on imported as db

Hi,
I created a new solution and imported an AS db to it so I can avoid to work on the database itself.
Now, when I change an MDX formula in a calculation, I cannot see the change effect in the browser without re-processing the cube.

What Am I missing? There sure is a way to see the effect of a calculation change by using some kind of refresh? I tried re-connect and refresh and save, nothing works.

Thanks,
Philippe

The BI project you imported is offline, changes to calculations are only saved to the local .cube file (while the cube browser gets the data from the live server). That's why you need to re-deploy (not necessarily with processing though) on the server. For editing calculations and quickly seeing the effects, it's easier to work in online mode (from Visual Studio, File -> Open -> Analysis Services Database).

Adrian Dumitrascu

|||

Adrian Dumitrascu wrote:

The BI project you imported is offline, changes to calculations are only saved to the local .cube file (while the cube browser gets the data from the live server). That's why you need to re-deploy (not necessarily with processing though) on the server. For editing calculations and quickly seeing the effects, it's easier to work in online mode (from Visual Studio, File -> Open -> Analysis Services Database).

Adrian Dumitrascu

Yes Offline you need to deploy before you see the changes, in online mode you need to SAVE to see the changes.

That requires some tricky organization when working as a team...

Also, tough not to break something working when changing a formula.
Lastly Sourcesafe becomes hard to use like that.
SourceSafe does a poor job anyways with cubes. When you delete an object like cube or dataview you cannot get it back from the history. It shows it in the project exlporer but says object has been deleted and is invalid...

I sure yet have to master VSS however I suspect that there is room for improvement in this working model.

Philippe

Reference axis(2) members in MDX

Hello everybody.

I've got a little problem: I want to reference axis(2) members in a calculated member, so I can use multiple selection.

I've tried:

with member [Measures].[Test] as 'settostr(axis(2))'

select {[Measures].[Test]} on 0,

{[Dim Sales Territory].[Sales Territory Region].members} on 1

from [Adventure Works DW]

where ([Dim Geography].[Country Region Code].&[US],[Dim Geography].[Country Region Code].&[CA])

However, I get an error.

Is there any other way?

Thanks a lot,

Santi

The Axis function cannot be used on the filter axis.The Axis function cannot be used on the filter axis.

http://msdn2.microsoft.com/en-us/library/ms145531.aspx

your shuldn't use Axis function if you whant to use multiple filter

|||

Thanks Vladimir.

Then, assuming I have several members on the filter axis, how can I find out what those members are?

Is it at all possible?

The thing is, I'm trying to enable multiple selection in a dimension, and the selected members go directly to the where clause of the query. Since I have some calculated measures, I can′t reference those members using currentmember.

Thanks again for your answer.

|||What the client do you use? Do you have a full control on it.|||

You could use the Existing function:

>>

with member [Measures].[Test] as

settostr(Existing [Geography].[Country].[Country].Members)

select {[Measures].[Test]} on 0

from [Adventure Works]

where {[Geography].[Country].&[United States],

[Geography].[Country].&[Canada]}

-

Test
{[Geography].[Country].&[Canada],[Geography].[Country].&[United States]}

>>

This technique is discussed in detail here:

http://sqljunkies.com/WebLog/mosha/archive/2005/11/18/multiselect_friendly_mdx.aspx

>>

Writing multiselect friendly MDX calculations

>>

|||

Thanks a lot Deepak!

Didn't know about Exist and Existing at all.

I've also read Mosha's blog entry, and it explains exactly the internals of what I'm trying to acomplish.