Re: return a value using MDX

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

I changed the where and added the WITH set

SET @sql = 'SELECT *
FROM OPENQUERY(OLAP,
''WITH
set [TimeRange] AS ''''[Time].[All Time].[' + Convert(Varchar(4),
Year(@startdate)) + '].[' + DateName(m, @startdate) + ']:[Time].[All
Time].[' + Convert(Varchar(4),
Year(@enddate)) + '].[' + DateName(m, @enddate) + ']''''
Select
({[Measures].[AverageAdPrice]}) on columns
From [Total Revenue]
Where ([TimeRange] , [Sales Region].[All Sales Region].[' +
@region + '])

'')'
EXEC (@sql)

but I am getting a syntax error with the [TimeRange].

This is my @sql results:

SELECT *
FROM OPENQUERY(OLAP,
'WITH
set [TimeRange] AS ''[Time].[All Time].[2006].[May]:[Time].[All
Time].[2006].[May]''
Select
({[Measures].[AverageAdPrice]}) on columns
From [Total Revenue]
Where ([TimeRange] , [Sales Region].[All Sales
Region].[Georgia])

')


"Milind" wrote:

No I am not a MVP. But I am glad I could help you solve your problem. I
learnt mdx by reading articles on the net and trying out queries in
foodmart. There are some good books like Fast track MDX which I have
heard are good.

Milind

SAM wrote:
Nevermind, I got it. Thanks so much for your help. This was a tough one and
it seem so simple at first. Are you already MVP status?

"SAM" wrote:

Thanks, yes that worked!!! Wow, this was difficult this make happen. I have
this book for MDX but it doesn't help as much as I thought. Can you suggest
any books and resources? Because I know I will have to work with more MDX and
stored procedures together and it would help to have some references.

I created a stored procedure to this query. When I execute the sp with this
parameters, it is looking for @sql as well. I thought it is set within the
procedure. It is when I run within query analyzer but not when I exec the
stored procedure.

"Milind" wrote:

Try this

DECLARE @sql varchar(8000)
Declare @time datetime
Declare @region varchar(2000)

set @time = '2006-05-01 00:00:00'
set @region = 'Georgia'


SET @sql = 'SELECT *
FROM OPENQUERY(OLAP,
''Select
({[Measures].[Avg Revenue]}) on columns
From [Total Revenue]
Where ([Time].[All Time].[' + Convert(Varchar(4),
Year(@time)) + '].[' + Left(DateName(m, @time), 3) + '] , [Sales
Region].[All Sales
Region].[' + @region + '])

'')'

I assumed that your time dimension months are 3 characters in short
name format like Jan, Feb, Mar etc.. That is the reason I did a Left(
...., 3) in the where clause. If your months in the time dimension are
January, February etc. (long names), then remove the Left keyword above
and just keep the datename. Please try this and let me know if it
doesn't work. Also, add a Print @sql before the exec @sql and paste the
output next time too if it doesnt work.

Milind


SAM wrote:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSOLAP'.
[OLE/DB provider returned message: Syntax error in expression, near:
''[Time].[05/01/2006]

"SAM" wrote:

ok finally I was able to get a little farther with the following but still
not quite there yet:

DECLARE @sql varchar(8000)
Declare @time datetime
Declare @region varchar(2000)

set @time = '2006-05-01 00:00:00'
set @region = 'Georgia'

SET @sql = 'SELECT *
FROM OPENQUERY(OLAP,
''Select
({[Measures].[Avg Revenue]}) on columns
From [Total Revenue]
Where [Time].[All Time].member = ''''+ Convert(varchar(10), @time,101) +''''
'')'

Here is the result:

Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSOLAP'.
[OLE/DB provider returned message: Syntax error, expecting end of statement
but found extra text, near: '= '+ Convert(varchar(10), @time,101) +'




']
OLE DB error trace [OLE/DB Provider 'MSOLAP' ICommandPrepare::Prepare
returned 0x80040e14].




.

Relevant Pages

  • Re: problem with stored proc
    ... ALTER PROCEDURE dbo.AddAdminUser ... The syntax error is caused because CREATE USER cannot take a variable as an argument. ... You can work around this by executing the proc using dynamic SQL: ... DECLARE @sql varchar ...
    (microsoft.public.sqlserver.msde)
  • Re: return a value using MDX
    ... DECLARE @sql varchar ... set @region = 'Georgia' ... From [Total Revenue] ... add a Print @sql before the exec @sql and paste the ...
    (microsoft.public.sqlserver.olap)
  • Re: Host Variable
    ... However, a variable in SQL ... Server is done via a DECLARE: ... get a syntax error. ...
    (microsoft.public.sqlserver.programming)
  • Re: Host Variable
    ... How can I declare a host variable in SQL Server? ... The documentation says, it shall begin with "EXEC SQL BEGIN DECLARE SECTION;" ..., but with this I only get a syntax error. ... Many Thanks, Alex ...
    (microsoft.public.sqlserver.programming)
  • Re: Rearrange data in a column, Access 2003
    ... I tried this and am getting a 'syntax error, ... I have learned some about SQL, but not enough to figure out the above error. ... "John W. Vinson" wrote: ... this is high-hazard work: ...
    (microsoft.public.access.queries)