Hardcoded LOV against SAP BW

[Former Member]

Hello,

When using XI 3.1 against SAP BW what is the equivalent of

@Prompt('Rank By (Mandatory, Single)','A',{'MTD','QTD','YTD'},mono,constrained)

That is I want the LOV to display MTD, QTD and YTD.

The above syntax gives me the below error

A database error occured. The database error text is: The MDX query WITH MEMBER [Measures].[3A5C8F88-5138-DF11-A2,11,14,1E,A,CD,0,0] AS ' Net Sales ' MEMBER [Measures].[CE5E8F88-5138-DF11-A2,11,14,1E,A,CD,0,0] AS ' MTD ' SELECT { [Measures].[3A5C8F88-5138-DF11-A2,11,14,1E,A,CD,0,0], [Measures].[CE5E8F88-5138-DF11-A2,11,14,1E,A,CD,0,0] } ON COLUMNS , NON EMPTY CROSSJOIN( CROSSJOIN( [0SOLD_TO].[LEVEL01].MEMBERS, [0SOLD_TO__0INDUSTRY].[LEVEL01].MEMBERS ), [0PLANT].[LEVEL01].MEMBERS ) DIMENSION PROPERTIES MEMBER_CAPTION, [0PLANT].[10SALES_DIST], [0SOLD_TO].[10INDUSTRY] ON ROWS FROM [ZMCSLSAVP/ZZMCSLSAVP_Q0001] failed to execute with the error Invalid MDX command with Sales. (WIS 10901)

Thanks,

Nikhil

Edited by: Nikhil Khasnis on Mar 25, 2010 10:13 PM

To clarify

I am trying to use @Prompt('Rank By (Mandatory, Single)','A',{'MTD','QTD','YTD'},mono,constrained) in the select part of the MDX query.

I have a requirement to let the user select MTD, QTD or YTD and then do ranking on a WebI report based on what is selected. This would work in legacy DBs as the SQL would be like

Select column1, 'MTD' from table

Any work arounds in MDX?

[Former Member · Accepted solution]

Hi,

Your expression is incomplete.

What I have understood is that you want to let the user to choose a YTD, MTD or QTD.

But you have omitted to specify on which time dimension and which measure.

The correct syntax for a period to date is like this:

SUM(YTD([dimension].[member]), [Measures].[measure])

Here is a sample:

@Prompt('Rank By (Mandatory, Single)','A:A',{'MTD':'SUM(MTD([dimension].[member]), [Measures].[measure])','QTD':'SUM(QTD([dimension].[member]), [Measures].[measure])','YTD':'SUM(YTD([dimension].[member]), [Measures].[measure])'},mono,primary_key)

Regards

Didier

[Former Member · Accepted solution]

Hi Didier,

Thanks for your reply.

MTD, QTD and YTD are already calculated in the BEx query. They will be 3 seperate key figures.

Also, the dimension is selectable. The first column can be Buss Unit, Buy Group or Fin Resp. The user can select 1 of the 3 and then select what ranking (MTD/QTD/YTD).

Nikhil

Edited by: Nikhil Khasnis on Mar 26, 2010 3:08 PM

The complete requirement is

1.Select 1 of 3 characteristic (Buss Unit, Buy Group or Fin Resp) to be displayed on the report.

2. Select a ranking method (MTD/QTD/YTD)

Display MTD, QTD and YTD numbers for above selected characteristic (from 1) ranked by measure selected in 2.

[Former Member · Accepted solution]

Hi Nikhil,

Thank you fro the feedback.

So in your case the solution will look like this:

@Prompt('Rank By (Mandatory, Single)','A:A',{'MTD':'[Measures].[Measure1]','QTD':'[Measures].[Measure2]','YTD':'[Measures].[Measure3]'},mono,primary_key)

Measure1, Measure2 and Measure3 are the definition of each CKF related to the usage of the correct time period: YTD, MTD or QTD.

By the way, do you want to also have the dimension ( Buss Unit, Buy Group or Fin Resp) selectable in the universe or is it already dynamically defined in the BEx query. This would have an impact on the expression.

Regards

Didier

[Former Member · Accepted solution]

Hi Didier,

Thanks for your help.

The report should let the user select ie prompt on

1. A characteristic (Buss Unit/Historical group/Fin Resp)

2. A KF element (Net Sales/Tot Rev/Cust Markup/Gross Profit))

3. A ranking (MTD/QTD/YTD)

For the selected characteristic and KF element the report displays MTD, QTD and YTD KFs.

For eg. user select 1. Buss Unit, 2. Net Sales and 3. QTD

the report will display MTD Net Sales, QTD Net Sales and YTD Net Sales per Buss Unit. The Buss Units will be ranked on QTD Net Sales.

In the BEx query I have a CKF for each of the KF and MTD/QTD/YTD combination. So the BEx has a MTD/QTD/YTD KF for each of the 4 elements from point 2 above (Net Sales MTD, Net Sales QTD, Net Sales YTD, Tot Rev MTD, Tot Rev QTD, Tot Rev YTD...and so on).

Nikhil

[Former Member · Accepted solution]

Hi,

The syntax is a little bit long and tricky but anyaway here is the solutuon:

(@Prompt('Rank By (Mandatory, Single)','A:A',{'MTD Net Sales':'[Measures].[KF_MTD_NetSales]','QTD Net Sales':'[Measures].[KF_QTD_NetSales]','YTD Net Sales':'[Measures].[KF_YTD_NetSales]','MTD Tot Rev:'[Measures].[KF_MTD_TotRev]','QTD Tot Rev':'[Measures].[KF_QTD_TotRev]','YTD Tot Rev':'[Measures].[KF_YTD_TotRev]','MTD Cust Markup':'[Measures].[KF_MTD_CustMarkup]','QTD Cust Markup':'[Measures].[KF_QTD_CustMarkup]','YTD Cust Markup':'[Measures].[KF_YTD_CustMarkup]','MTD Gross Profit':'[Measures].[KF_MTD_GrossProfit]','QTD Gross Profit':'[Measures].[KF_QTD_GrossProfit]','YTD Gross Profit':'[Measures].[KF_YTD_Gross Profit]'},mono,primary_key), @Prompt('Dimensions?','A:A',{'Buss Unit':'[BussUnit].currentmember','Historical group':'[HistoricalGroup].currentmember','Fin Resp':'[FinRep].currentmember'},mono,primary_key) )

Of course, yu have to use the correct syntax for eah KF and characteristic.

Regards

Didier

[Former Member · Accepted solution]

AWESOME!!! You da man.

My complete syntax is

<EXPRESSION>

(@Prompt('Rank By (Mandatory, Single)','A:A',{'MTD Net Sales':'[Measures].[AVYY9P5M8DTA6AESXTMP443S6]','QTD Net Sales':'[Measures].[AY3OF147LRJYBGLD5HC7YFQXY]','YTD Net Sales':'[Measures].[B08EKD2SZ5AMGMRXD51QSRE3Q]','MTD Tot Rev':'[Measures].[ACSEXPI9VY58VQRR0W8ZJ7BC6]','QTD Tot Rev':'[Measures].[AEX531GV9BVX0WYB8JYIDIYHY]','YTD Tot Rev':'[Measures].[AH1V8DFGMPML634VG7O17ULNQ]','MTD Cust Markup':'[Measures].[BYC0XOGAX95CRDOWROE49XOO6]','QTD Cust Markup':'[Measures].[C0GR30EWAMW0WJVGZC3N49BTY]','YTD Cust Markup':'[Measures].[C2LH8CDHO0MP1Q216ZT5YKYZQ]','MTD Gross Profit':'[Measures].[BW7ASCHPJVEOM7ICK0OLFM1IE]','QTD Gross Profit':'[Measures].[BPT4CCLXFQ6O6OYNX1K0WN412]','YTD Gross Profit':'[Measures].[BJEXWCQ5BKYNR6EZA2FGDO6JQ]'},mono,primary_key),@Prompt('Dimensions?','A:A',{'Buss Unit':'[0SOLD_TO].currentmember','Historical group':'[ZBUY_GPO].currentmember','Fin Resp':'[ZCFINRESP].currentmember'},mono,primary_key) )

</EXPRESSION>

This is crazy thought for mere mortals like me to remember. Is there a good book or course or something on MDX or how to use it with BO?

Thanks,

Nikhil

Edited by: Nikhil Khasnis on Mar 29, 2010 6:08 PM

<EXPRESSION>
(@Prompt('Rank By (Mandatory, Single)','A:A',{'MTD Net Sales':'[Measures].[AVYY9P5M8DTA6AESXTMP443S6]','QTD Net Sales':'[Measures].[AY3OF147LRJYBGLD5HC7YFQXY]','YTD Net Sales':'[Measures].[B08EKD2SZ5AMGMRXD51QSRE3Q]','MTD Tot Rev':'[Measures].[ACSEXPI9VY58VQRR0W8ZJ7BC6]','QTD Tot Rev':'[Measures].[AEX531GV9BVX0WYB8JYIDIYHY]','YTD Tot Rev':'[Measures].[AH1V8DFGMPML634VG7O17ULNQ]','MTD Cust Markup':'[Measures].[BYC0XOGAX95CRDOWROE49XOO6]','QTD Cust Markup':'[Measures].[C0GR30EWAMW0WJVGZC3N49BTY]','YTD Cust Markup':'[Measures].[C2LH8CDHO0MP1Q216ZT5YKYZQ]','MTD Gross Profit':'[Measures].[BW7ASCHPJVEOM7ICK0OLFM1IE]','QTD Gross Profit':'[Measures].[BPT4CCLXFQ6O6OYNX1K0WN412]','YTD Gross Profit':'[Measures].[BJEXWCQ5BKYNR6EZA2FGDO6JQ]'},mono,primary_key),@Prompt('Dimensions?','A:A',{'Buss Unit':'[0SOLD_TO].currentmember','Historical group':'[ZBUY_GPO].currentmember','Fin Resp':'[ZCFINRESP].currentmember'},mono,primary_key) )
</EXPRESSION>

[Former Member · Accepted solution]

Hi,

Here is a link I used frequently, this is the official Microsoft MDX website: [MDX Function Reference |http://msdn.microsoft.com/en-us/library/ms145970.aspx]

There are also other interesting forums, blogs or other websites dedicated to MDX:

[http://sqlserverpedia.com/wiki/Main_Page|http://sqlserverpedia.com/wiki/Main_Page]

[http://www.georgehernandez.com/h/xComputers/Databases/MD/MDXFunctions.asp]

[http://www.sqlserveranalysisservices.com/default.htm]

[http://tomislavpiasevoli.spaces.live.com/]

Here is a extract of websites I am used to use.

Regards

Didier

[Former Member]

Thanks didier,

Its working fine.there was an extra ( at the start which was causing problem.

regards

praveen

[Former Member]

Hi,

The syntax you wrote is missing information such as time dimension and measure.

Here is a sample based on MSAS:

<EXPRESSION>SUM(@Prompt('Choose period?','A:N',{'Year':'YTD','Quarter':'QTD','Month':'MTD','Week':'WTD'},mono,primary_key)([Date].[Calendar].CurrentMember), [Measures].[Internet Sales Amount])</EXPRESSION>

Here is a sample based on SAP BW:

<EXPRESSION>SUM(@Prompt('Choose period?','A:N',{'Year':'YTD','Quarter':'QTD','Month':'MTD','Week':'WTD'},mono,primary_key)([0CALMONTH].CurrentMember), [Measures].[0D_OORQTYBM])</EXPRESSION>

Regards,

Didier

[Former Member]

We are trying to give the end user option of selecting one KF from each Set 1 and Set 2. We are using the below logic for creation of 2 two prompts.

<EXPRESSION>

(@Prompt('Select SET 1 KF','A:A',{'Billing Qty':'[Measures].[4HF0ZFS0SOCJ87WCZAWVD8TUM]','Billing Qty SU':'[Measures].[4HF11PA9ZUPAVE9UWBR6237DQ]','Gross weight':'[Measures].[4HF11PHYITB0E0TB25TIC563I]','Net Value':'[Measures].[4HF11PPN1RWPWNCR7ZVUM74TA]'},mono,primary_key)

</EXPRESSION>

<EXPRESSION>

(@Prompt('Select SET 1 KF','A:A',{'Billing Qty':'[Measures].[4HF0ZFS0SOCJ87WCZAWVD8TUM]','Billing Qty SU':'[Measures].[4HF11PA9ZUPAVE9UWBR6237DQ]'},mono,primary_key)

</EXPRESSION>

We are getting the follwing error while running it from WebI report.

A database error occured. The database error text is: The MDX query WITH MEMBER [Measures].[60F14E7C-1617-4863-AE,44,CB,4A,49,FF,5C,8B] AS '

([Measures].[4HF11PA9ZUPAVE9UWBR6237DQ]

' MEMBER [Measures].[1BC82661-7867-4934-9F,CF,B5,57,E,60,B,C4] AS '

([Measures].[4HF0ZFS0SOCJ87WCZAWVD8TUM]

' SELECT { [Measures].[60F14E7C-1617-4863-AE,44,CB,4A,49,FF,5C,8B], [Measures].[1BC82661-7867-4934-9F,CF,B5,57,E,60,B,C4] } ON COLUMNS FROM [ZTEST_KEN/ZTEST_KEY] failed to execute with the error Invalid MDX command with '. (WIS 10901)

Please share yuor thoughts on this error.

Thanks

Praveen

[Former Member]

Try

<EXPRESSION>
(@Prompt('Select SET 1 KF','A:A',{'Billing Qty':'[Measures].[4HF0ZFS0SOCJ87WCZAWVD8TUM]','Billing Qty SU':'[Measures].[4HF11PA9ZUPAVE9UWBR6237DQ]'},mono,primary_key), [0SOLD_TO].currentmember)
</EXPRESSION>

Replace

[0SOLD_TO].currentmember

with a characteristic from your universe.

This should enable a prompt where user can select a KF. Then using @userprompt, display the KF in the report.

This is how I did it. Not sure if there is a way to dynamically change the generated MDX query (like how RDBMS SQL can be).