Hi

I have a simple cube that is based on our Tills. We have a
TransactionNo dimension that shows when this took place and how much it
all came to etc.
We also have a ItemNo dimension that lists all items in our stock
table.

We can easily see what was bought by placing the TransactionNo and
ItemNo on the same Axis.

However, I will shortly need to show what customers bought at the same
time when they bought a certain item. IE what else did people who
bought cheese buy.

I am having trouble working out the correct way to approach this.

I have this in MDX to see what was bought for 1 Transaction. I need to
show all Transactions which have a certain Item. But I need to show all
items for that transaction.

SELECT { [Time].[All Time].CHILDREN } ON COLUMNS ,
NON EMPTY { { { [Journal No].[Journal No].&[18651].&[171051] } * {
DESCENDANTS( [Item Number].[All Item Id], [Item Number].[Item Id] ) } }
} ON ROWS
FROM [Tills]
WHERE ( [Measures].[Qty] )

Any ideas?

I have thought about his some more and should probably need to do a sub
query.
IE get the TransactionNo results where the Item exists.

I know this could be done by using WITH SET function but I have no idea
how to construct this statement for what is essentially
Select transID from Table where ItemId = Cheese

Is this the right way to do this?

midi wrote:

There is a good article on basket analysis with AS2k here
http://msdn.microsoft.com/library/de...l=/library/en-
us/dnsql2k/html/distinct2.asp

I also posted the following example a few months ago. It is from the
Adventure Works sample database for AS2k5 and lists people that bought
products based on price and lists the other reasons for which they
bought.

WITH
MEMBER Measures.BuyOnPricePre as Iif(IsEmpty(([Measures].[Internet
Order Count], [Sales Reason].[Sales Reason].[Price]))
Or IsEmpty([Measures].[Internet Order Count]), 0, 1)

MEMBER Measures.BuyOnPrice as
sum(Descendants([Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Full Name])
,Measures.BuyOnPricePre)

MEMBER Measures.CustomerCount as
count(Descendants([Customer].[Customer Geography].CurrentMember,
[Customer].[Customer Geography].[Full Name]))

MEMBER Measures.PcntBoughtOnPrice as measures.BuyOnPrice /
Measures.CustomerCount, FORMAT_STRING= "0.0%"

SELECT
{Measures.BuyOnPrice
,Measures.PcntBoughtOnPrice } ON COLUMNS,
[Sales Reason].[Sales Reasons].members ON ROWS
FROM [Adventure Works]


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

In article <1149499613.847773.7810 (AT) g10g2000cwb (DOT) googlegroups.com>,
alanmidwinter (AT) hotmail (DOT) com says...

Hi Thanks for the reply

This is roughly what I need. I realise that I need to do this. The
issue that I have is that I need to create a SET that is not
straightforward to me.

I need to Get all TransIDs which are relevant for an Item of Cheese.
These are 2 seperate dimensions. So as I mentioned I need to have the
result of this MDX


SELECT { [Time].DEFAULTMEMBER } ON COLUMNS ,

NON EMPTY { DESCENDANTS( [Journal No].[All Journal No], [Journal
No].[Trans Id] ) } ON ROWS

FROM [Tills]

WHERE ( [Item Number].[Item Id].&[Cheese], [Measures].[Qty] )


Then I need the recordset that this produces to be in a new MDX which
Shows the result and all Items on one axis.

My knowledge of MDX is as you can tell not the best so I appreciate any
comments recieved.

Rgds

Alan Midwinter

If you are using AS2k5, you can do something like the following:

NONEMPTY( [Journal No].[Trans Id].Members, ([Item Number].[Item Id].&
[Cheese], [Measures].[Qty]) )


If you are using AS2k, you do not have the NonEmpty function and you
have to use something not as efficient like:


Filter([Journal No].[Trans Id].members, not IsEmpty( ([Item Number].
[Item Id].&[Cheese], [Measures].[Qty]))


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

In article <1149596025.594480.91350 (AT) y43g2000cwc (DOT) googlegroups.com>,
alanmidwinter (AT) hotmail (DOT) com says...