I was trying to write this MDX but I am getting errors so maybe I should
create a stored procedure against my CUBE.

I want to return two values : Revenue Avg and Product Price Avg by a
specific time and Sales Region.

can someone provide an example?

My cube provides the total revenue for any date. I thought I could take the

avg(Sales Region.members,Time.members, total revenue amount) AS 'Revenue Avg'

avg(Sales Region.members, Time.members, Product price) AS 'Product Price Avg'

and get a value but I am getting an error message.

Books online shows the syntax for the Avg() function as:

Avg( Set_Expression [ , Numeric_Expression ] )

So you should create a single set from the 2 sets by crossjoining them.
Which would make your expressions something like the following:


avg(Sales Region.members * Time.members
, Measures.[total revenue amount]) AS 'Revenue Avg'

avg( Sales Region.members * Time.members
, Measures.[Product price]) AS 'Product Price Avg'



--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <06BE2A0F-544C-465F-AB74-0DA117FD2953 (AT) microsoft (DOT) com>,
SAM (AT) discussions (DOT) microsoft.com says...

Thanks. I want to use this within a stored procedure and pass variables.

Create Procedure AvgRevenueCalculation (

@salesregion varchar(50)
@time datetime

As
avg(Sales Region.members * Time.members
, Measures.[total revenue amount]) AS 'Revenue Avg'

)

Do you know how to pass the variables in a MDX query?



"Darren Gosbell" wrote:

I created CM to provide me the revenue and product price average but I used a
different formula and it worked.

Now, I am using the following within a stored procedure and want to pass
parameters for the Sales region, time and product but not sure how.


Select

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

from [Total Revenue]

where [Time].[All Time].[2006].[May]

--[Time].[All Time].["+ Parameters!Time.Value +"]]"

"Darren Gosbell" wrote:

If looks like you might be using reporting services, if this is the case
you can pass in parameters by setting the whole query up as an
expression. In this case I usually set up the dataset for the parameters
using an MDX query something like the following that allow me to get the
caption and unique name for the filter members. (I set up the Uname
measure as the value for the parameter.

WITH
MEMEBER Measures.Uname as [Time].
SELECT
{Measures.Uname} ON COLUMNS
, Descendants([Time].[All Time],[Time].[Month]) ON ROWS
FROM [Total Revenue]


You can't really do this sort of stored procedure using AS stored procs,
so I assume that you are talking about a SQL stored proc. In this case
you need to use dynamic sql to build an entire openquery() query. I am
doing the following from memory, so it may not work "as is", but you
should get the general idea.


DECLARE @sql varchar(2000)

SET @sql = 'SELECT *
FROM OPENQUERY(OLAP_SERVER,''Select
({[Measures].[Avg Revenue], [Measures].[Avg Ad Price]}) on columns,
nonemptycrossjoin({[Sales Region].[All Sales Region].children}) on
rows
from [Total Revenue]
where ' + @time + ''')'

EXEC sp_execute @sql


--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <879B6E68-A64D-4F7E-A4DC-29AF30CF5AB7 (AT) microsoft (DOT) com>,
SAM (AT) discussions (DOT) microsoft.com says...

Hello Darren,

Thanks. No we are not using Reporting Services, just Excel Pivottable.

The developer wants to use a stored procedure to return the value of the
revenue average and price average to her UI.

Yes I am using SQL sp in hopes to return the value to her interface.

This is what I have so far:

Declare @time datetime,
@sql varchar(2000)

SELECT * FROM OPENQUERY(OLAP,

'Select

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

from [Total Revenue]

where '+'@time'+'''

--where [Time].[All Time].[2006].[May]'

)

I'm researching the correct syntax to pass the variable in because it is not
accepting it.
"Darren Gosbell" wrote:

Here is a better one but I'm still unsure about the passing and checking the
time and salesregion against AS properly to return the same value that I am
getting.

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



AS

Declare @time datetime,
@sql varchar(2000),
@salesregion varchar(2000)

SELECT * FROM OPENQUERY(OLAP,

'Select

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


from [Total Revenue]

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

"SAM" wrote:

The issue you will be facing is that you cannot pass a parameter or use
a dynamic expression as the second parameter in a call to OPENQUERY().

If you have a look at the sample I sent in my post where I mentioned
reporting services, you will see that I am building the entire call to
openquery including the "SELECT * FROM OPENQUERY(". It leads to some
very nasty string concatenation as you generally have to do a lot of
escaping of quote characters ('), but this is the only way to do a
dynamic call to openquery().

Your other issue is that your MDX is not valid, the where clause in MDX
is a tuple (in AS2k, in AS2k5 you can pass in sets).

Your stored proc should be something like the following. (I put this
together from memory, so I don't know if it is 100% correct, but
hopefully you can see the pattern of what I am trying to do).

Create Procedure dbo.Revenue_Average_Calculation
( @salesregion varchar(2000),
@time datetime
)

AS

Declare @sql varchar(2000),
,@year char(4)
,@month varchar(15)

SET @year = CONVERT(char(4),YEAR(@time))
SET @month = DATENAME(m,@time)

SET @sql = 'SELECT * FROM OPENQUERY(OLAP,

''Select

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


from [Total Revenue]

where ([Time].[All Time].[' + @year + '].[' + @month + ']
, [Sales Region].[All Sales Region].[' + @salesregion + '])'')'

EXEC @sql

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <827A9615-81DE-4B73-AB53-0833BCFCEDFF (AT) microsoft (DOT) com>,
SAM (AT) discussions (DOT) microsoft.com says...