September 29, 2008 at 2:26 am
hi,
i'm trying to find out the Top10 products on retailers
WITH SET [OrderedSet] AS
ORDER ( [Retailer].[Retailer Name].[Retailer Name].MEMBERS,
[Measures].[Sales Value Gross RRP Excluding Taxin Local Currency] ,
BDESC)
MEMBER [Measures].[Rank] AS
RANK([Retailer].[Retailer Name].CurrentMember,
[OrderedSet])
SELECT
{[Measures].[Rank], [Measures].[Sales Value Gross RRP Excluding Taxin Local Currency]} ON 0,
{HEAD([OrderedSet], 10)} ON 1
FROM
[CubeClientAdmin]
it gives the error:
Error1MdxScript(CubeClientAdmin) (69, 1) Parser: The syntax for 'WITH' is incorrect.00
and i couldn't trace out the error in 'with',
can anyone tell me what is the error in the above code?
Thanks,
Regards
Viji
September 30, 2008 at 5:23 pm
Does this help?
WITH SET [OrderedSet] AS
'ORDER ( [Retailer].[Retailer Name].[Retailer Name].MEMBERS,
[Measures].[Sales Value Gross RRP Excluding Taxin Local Currency] ,
BDESC)'
MEMBER [Measures].[Rank] AS
'RANK([Retailer].[Retailer Name].CurrentMember,
[OrderedSet])'
SELECT
{[Measures].[Rank], [Measures].[Sales Value Gross RRP Excluding Taxin Local Currency]} ON 0,
{HEAD([OrderedSet], 10)} ON 1
FROM
[CubeClientAdmin]
Notice the 'quotes' around the SET and MEMBER calculations
Kind Regards, Will
September 30, 2008 at 9:43 pm
Hi Mr.Will,
thanks.
but i tried with quotes also. Its giving the same error. 🙁
regards
viji
October 2, 2008 at 11:02 pm
Don't see anything wrong with your query. I modified it to work against the Adventure Works and it runs without any issues.
WITH SET [OrderedSet] AS
ORDER ( [Reseller].[Reseller].[Reseller].MEMBERS,
[Measures].[Reseller Sales Amount] ,
BDESC)
MEMBER [Measures].[Rank] AS
RANK([Reseller].[Reseller].CurrentMember,
[OrderedSet])
SELECT
{[Measures].[Rank], [Measures].[Reseller Sales Amount]} ON 0,
{HEAD([OrderedSet], 10)} ON 1
FROM
[Adventure Works]
Just make sure the whole statement is selected when you run the query.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply