MS SQL Server OLAP - request sample mdx query using Case statement with Foodmart

Author

request sample mdx query using Case statement with Foodmart

Rich

2005-12-28, 8:24 pm

Hello,

I am slaving away with a book on OLAP for sql Server 2000, but the book was
written for Sql Server 7. One of the examples is illustrating the Case
statement but with a database other than Foodmart (database came on CD). I
can't get the Case statement to work - error's out.

Could someone share a real simple mdx query using/employing the Case
statment against the Foodmart database? I need to get the syntax down. Here
is a sample I found on the net, but also not using Foodmart.

WITH MEMBER [Measures].StringSales AS
CASE
WHEN [Measures].[Reseller Sales Amount] >= 40000 THEN "40,000+"
WHEN [Measures].[Reseller Sales Amount] >= 30000 THEN "30,000-40,000"
WHEN [Measures].[Reseller Sales Amount] >= 10000 THEN "10,000-30,000"
WHEN [Measures].[Reseller Sales Amount] < 10000 THEN "< 10,000"
END
SELECT Calendar.[Calendar Year] on 0,
[Geography].[Postal Code].Members on 1
FROM [Adventure Works]
WHERE [Measures].StringSales

Any samples greatly appreciated.

Thanks,
Rich

Deepak Puri

2005-12-29, 3:24 am

Hi Rich,

AFAIK, the Case statement was implemented not in AS 2000, but in AS 2005
- not sure which book you're using.

http://groups.google.com/group/micr....olap/msg/ee230
058e24b1bc5?hl=en&
Newsgroups: microsoft.public.sqlserver.olap
From: George Spofford
Date: 2000/10/02
Subject: Re: CASE

Is not.

"John H. Herrald" wrote:
[color=darkred]
> Hello,

> The Analysis beta's documentation included the CASE mdx statement.
The
> final version's documentation does not (as far as I could tell).

> Could someone please tell me if he or she knows for a fact - Is the
CASE
> statement implemented in Analysis Services final?

> Thanks in advance,

--
George Spofford
DSS Lab
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Rich

2005-12-29, 3:24 am

Thanks. The book is called "Sql Server OLAP Developer's Guide"

by William C. Amo.

ISBN 0-7645-4643-0

Anyway, at least now I get it - no Case Statement in Analysis Server 2000.

Thanks,
Rich

"Deepak Puri" wrote:

> Hi Rich,
>
> AFAIK, the Case statement was implemented not in AS 2000, but in AS 2005
> - not sure which book you're using.
>
> http://groups.google.com/group/micr....olap/msg/ee230
> 058e24b1bc5?hl=en&
> Newsgroups: microsoft.public.sqlserver.olap
> From: George Spofford
> Date: 2000/10/02
> Subject: Re: CASE
>
> Is not.
>
> "John H. Herrald" wrote:
>
>
>
> The
>
>
> CASE
>
>
>
>
> --
> George Spofford
> DSS Lab
> ...
>
>
> - Deepak
>
> Deepak Puri
> Microsoft MVP - SQL Server
>
> *** Sent via Developersdex http://www.droptable.com ***
>

Sponsored Links