I have a cube with

Agency ID (Dim)
Date (Dim)

UnitSales (Measures)
AdCost(Measures)
UnitCostPerSale (Calculated from the above 2 measures)

Question: How do I sort the Cube output in the ascending order of the
calculated field (UnitCostPerSale)?

Depending on whether you want all values or only the top or bottom N,
you can use Order() or TopCount()/BottomCount().

[Measures].[AdCost],
[Measures].[UnitCostPerSale]} on columns,
Order([Agency ID].Members,
[Measures].[UnitCostPerSale]) on rows
from SaleCube

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Deepak,
That works great from MDX SampleApplication program. Thanks a lot!
What are the broad steps so that I can have these results go to a
spreadsheet on a daily basis to management.

I know how to connect a cube to a spreadsheet but how do I do this from the
MDX below?
(I am new to Analysis Services!)

Thanks,
Suresh Kumar

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Suresh,

There are probably many options to do this, and others in the group may
have some ideas. The 2 approaches that come to mind would be:

1) A robust and flexible solution would be to use something like SQL
Server Reporting Services. This would enable you to design and schedule
a report based on an MDX query, determine the output format(s) (such as
Excel and PDF), and distribute the reports via email. But this requires
set-up of Reporting Services and a report design environment (Visual
Studio.NET can be used, for example):

http://www.microsoft.com/sql/reporti...o/overview.asp

2) A simple approach would be to schedule a SQL Server DTS package that
exports the results of an MDX query to an external Excel file, using
standard DTS transformations. But with this approach, I'm not sure if
you can customize the Excel report to your requirements.



- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks Deepak!

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote