I have the following MDX query that is working as a sample

SELECT * FROM OPENQUERY(OLAP,

'Select

({[Measures].[Avg Revenue], [Measures].[Avg Ad Price]})
on columns,
nonemptycrossjoin({[Time].children},{[Sales Region].[All Sales
Region].children}) on rows

from [Total Revenue]'

)

How can I pass in a variable to return the value? Meaning, I want to pass in
a Time frame or a Sales Region to get my value which is the avg revenue for
that particular region and time period.

I'm not sure how to declare a variable and pass it in and return it to the
application.

Try this, In your stored procedure write the query as dynamic sql and
then pass the necessary parameters.

declare @strSQL varchar(8000)
declare @strFromDate datetime
declare @strToDate datetime

set @strFromDate = '2006-01-01 00:00:00'
set @strToDate = '2006-06-01 00:00:00'

set @strSQL = 'SELECT * FROM OPENQUERY(OLAP,
''With set [TimeRange] as ''''[Time].[<date level of your time
dimension>].&[' + Convert(Varchar(10), @strFromDate, 101) +
']:[Time].[<date level of your time dimension>].&[' +
Convert(Varchar(10), @strEndDate, 101) + ']''''
Select
({[Measures].[Avg Revenue], [Measures].[Avg Ad Price]})
on columns,
nonemptycrossjoin({[Time].children},{[Sales Region].[All Sales
Region].children}) on rows

from [Total Revenue]'

)'')'
exec @strSQL

HTH

Milind


SAM wrote:

Thanks, I'm trying this now.

This is what I had originally and could not get the parameters to pass:

Create Proc Revenue_Average_Calculation
(
Declare
@salesregion varchar(2000),
@time datetime

set @time = '5/2006'
set @salesregion = 'Georgia'

--AS

SELECT * FROM OPENQUERY(OLAP,

'Select

({[Measures].[Avg Revenue]})
on columns


from [Total Revenue]

where ([Time].[All Time].member = @time
, [Sales Region].[All Sales Region].member = @salesregion)'

"Milind" wrote:

I got this error message:

(OLAP,
'With set [TimeRange] as ''[Time].[All Time].&[05/01/2006]:[Time].[All
Time].&[05/30/2006]''

Select
({[Measures].[Avg Revenue], [Measures].[Avg Ad Price]})
on columns,
nonemptycrossjoin({[Time].children},{[Sales Region].[All Sales
Region].children}) on rows

from [Total Revenue]' is not a valid identifier.

"Milind" wrote:

Comment out exec @strQuery
and add this line before that
Print @strQuery. Please paste the output that you get in query
analyzer. I think you are missing a single quotes somewhere.

Milind


SAM wrote:

Thanks.

The output is the following:

SELECT * FROM OPENQUERY(OLAP,'With SET [Date] AS
'{[Time].[All Time.&[05/01/2006]}'


Select
({[Measures].[Avg Revenue]}) on columns,
({[Sales Region].[All Sales Region].children}) on rows

from [Total Revenue]


"Milind" wrote:

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].[2006].[May] , [Sales Region].[All Sales
Region].[Georgia])

'')'

I would like to pass the @time and @region to work. It works if I put in the
exact filters for time and region but I need to pass these in from the UI.
I have tried everything that I have found but nothing is working. Not a lot
of examples to work with to illustrate how to pass parameters with MDX and
stored procedures together.
"Milind" 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].

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:

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: