Customer Sales based on YTM dimension

Hello,

We have a cube that has customer sales data for last 5 years. Time Dimension displays the YTM hierarchy.

=> Selecting "month" is a parameter for the user on a Reporting Service report. Once he selects a month from YTM hierarchy - how to get list of "only those customers" whose sales have been continuously below say 80,000 dollars, beginning the month he selects as a "start month" until "next 6 months".

Multiple selection - not allowed. Only one month can be selected by user at any time.

Any help highly appreciated.

Thanks,

RajShri

Here's an Adventure Works example, which lists all customers with < $1000 in sales for each of 6 months, starting with the selected month (here, Jan. 2004). Note that this includes customers with no sales as well:

>>

With

Member [Measures].[Max6MonthSales] as

Max(LastPeriods(-6,

OpeningPeriod([Date].[Calendar].[Month])),

[Measures].[Internet Sales Amount])

Set [LowSalesCustomers] as

Filter([Customer].[Customer Geography].[Full Name].Members,

[Measures].[Max6MonthSales] < 1000)

select {[Measures].[Internet Sales Amount],

[Measures].[Max6MonthSales]} on 0,

[LowSalesCustomers] on 1

from [Adventure Works]

where [Date].[Calendar].[Month].&[2004]&[1]

>>

sql

Cursors with UNION in select doesn't work?

Hi,
I'm trying to open a cursor by selecting datas from an UNION of two tables.
I keep getting the error "incorrect syntax near the keyword 'UNION'".
Here my code...
DECLARE cArticles CURSOR FAST_FORWARD
FOR
select id from vw_articles where isFrontPage = 1 and categoryId = @.cId order
by frontPageDate desc
UNION
select id from vw_articles where isFrontPage = 1 and categoryId in (select
fk_tbl_category_child_id from tbl_subCategories where
fk_tbl_category_parent_id = @.cId) order by frontPageDate desc
OPEN cArticles
...
I tried with parenthesis, temp table... didn't work as well.
Any idea of how I can open a cursor with a select in two tables?
Thanks
StephaneThe problem is not the cursor. The problem is that you have order by within
your union. You can only apply order by on the whole set, not on individual
elements of the union.
"Stephane" <Stephane@.discussions.microsoft.com> wrote in message
news:2847989A-9ECA-4500-84F2-82BE1CA089DC@.microsoft.com...
> Hi,
> I'm trying to open a cursor by selecting datas from an UNION of two
> tables.
> I keep getting the error "incorrect syntax near the keyword 'UNION'".
> Here my code...
> DECLARE cArticles CURSOR FAST_FORWARD
> FOR
> select id from vw_articles where isFrontPage = 1 and categoryId = @.cId
> order
> by frontPageDate desc
> UNION
> select id from vw_articles where isFrontPage = 1 and categoryId in (select
> fk_tbl_category_child_id from tbl_subCategories where
> fk_tbl_category_parent_id = @.cId) order by frontPageDate desc
> OPEN cArticles
> ...
> I tried with parenthesis, temp table... didn't work as well.
> Any idea of how I can open a cursor with a select in two tables?
> Thanks
> Stephane
>|||Great! It works!
Thanks a lot
Stephane
"Aaron Bertrand [SQL Server MVP]" wrote:

> The problem is not the cursor. The problem is that you have order by with
in
> your union. You can only apply order by on the whole set, not on individu
al
> elements of the union.
>
>
> "Stephane" <Stephane@.discussions.microsoft.com> wrote in message
> news:2847989A-9ECA-4500-84F2-82BE1CA089DC@.microsoft.com...
>
>|||What about:
DECLARE cArticles CURSOR FAST_FORWARD
FOR
SELECT * FROM
(
select id from vw_articles where isFrontPage = 1 and categoryId = @.cId
order
by frontPageDate desc
UNION
select id from vw_articles where isFrontPage = 1 and categoryId in
(select
fk_tbl_category_child_id from tbl_subCategories where
fk_tbl_category_parent_id = @.cId) order by frontPageDate desc
) SubQuery
OPEN cArticles
HTH, Jens Suessmeyer.