BPC Report Filter on Values Rather than Properties?

[Former Member]

Experts --

I have what I think is a very common issue with OLAP. I would like to hear how others deal with it.

It is easy to filter on properties, but users cannot write to properties and change their values. Users can, however, easily submit a numeric response with the proper dimensionality.

The problem is how can you filter a report on these values? For example, a budget item may be marked with 1 or 2--either approved or not approved. How do I write a report showing all the items that have been approved?

Can I use MDX to do that? Is it efficient? Can I use an evDRE()? Can I use comments and a relational table within Excel to somehow filter the cube's data?

I am interested in hearing how others have tackled this problem.

Thanks...Marv

[Former Member · Accepted solution]

Hi Marv,

it is possible to use MDX in Reports. In the "old" Reports (EVEXP etc.) you are able to use either a MDX Filter Expression by using EVENE or you just add some "MDX" to the Filter Property of the EVEXP Formula.

For example you can use something like:

([ITEM].Currentmember,[FlagCheck],[BUDGET])=1

to check a value.

I think you can also use these formulas in EVDRE by setting the Memberset option of your Item Dimension to something like

Self,ALL AND ([ITEM].Currentmember,[SALESVALUE],[BUDGET])=1

Should be possible, but I only checked to use Property Filter in EVDRE yet.

regards

Jörg

PS.: I Hate these Format Options in the Forum ^^ I am not able to display the "not Equal" synthax

Edited by: Jörg Finster on Sep 12, 2008 9:52 AM

Edited by: Jörg Finster on Sep 12, 2008 9:56 AM

[Former Member]

Jorg --

Thanks for sticking with me. Feel like we are becoming old friends...:-)

I am using BPC 5.13.

I have a single row expansion EvDRE().

Given my CurrentView, when I use "SELF,ALL" as the Account dimension MemberSet, the EVDRE() returns 6 account rows. One of the accounts (i.e 1 row) has a value of 10.

When I change the MemberSet to "SELF, ALL and ((Account.CurrentMember)=10)" the evDRE compiles, but it returns the sum of all values for the account rollup. I would like it to return just the account with a value of 10.

You can get something like that to work? Your examples use both Entity and Account as dimensions.

Thanks...Marv

[Former Member]

Jorg--

Thanks for sticking with me on this! Sorry if I seem to have trouble following along...

I have a single row expansion evDRE(). I have tried to expansions on both Department (Entity) and Account.

I am trying to type the formula into MemberSet Parameter of the evDRE() expansion.

I added an account called FLAGACCOUNT. I sent in a value of 1 for one department in my current view.

I tried both

SELF, ALL AND ((DEPARTMENT.CURRENTMEMBER,FLAGACCOUNT)=1)
SELF, ALL AND (([DEPARTMENT].CURRENTMEMBER,[FLAGACCOUNT])=1)

I get this error in the EvDRE(): #ERR: Invalid member or flag in <memberset>, item: ALL((Department.CurrentMember![FlagAccount|https://forums.sdn.sap.com/])=1),col#1

In the error message exclamation marks(!) replace the brackets. Are the exclamation marks MDX syntax? Do they help in debugging?

I also tried

SELF, ALL AND ((ACCOUNT.CURRENTMEMBER)=1) compiles and returns the same as SELF
SELF, ALL AND (([ACCOUNT].CURRENTMEMBER)=1) doesn't compile.

I am stumped. You can really get this to work?

Marv

[Former Member]

Hi Marv,

strange...in my EVDRE() it works fine.

Perhaps a version Problem? EVDRE- Reports had some Problems in older ServicePacks.

Which Version are you using?

I am working on BPC 5.1 SP3 and it works fine.

The exclamation mark just points to the invalid part of the Memberset Filter. seems that your Application does not recognize "FlagAccount". Do you have only one "Flagaccount" or do you have Member in other Dimensions with the same ID?

Regards

Jörg

[Former Member]

Jorg --

Thanks for the information on MDX.

I tried your formula in the MEMBERSET Row expansion of an evDre(). I am getting a message "#ERR Invalid member of flag in <memberset>,item:

I have a question. In your MDX, why did you choose two dimensions ([SalesValue] and [Budget]) as part of the MDX? If I have five dimensions, do I need to list them all, or can I assume the page keys and column keys are included in "current member"?

If I only have ONE row expansion is the syntax simply

SELF, ALL and ([ITEM].Currentmember)=1

[Former Member]

Hi Marv,

i just tested the Formula in EVDRE, you need to use on more Bracket:

SELF, ALL and (([ITEM].Currentmember,[BUDGET],[SALESVALUE])=1)

This way it worked fine for me.

In my Example I had to use more than 1 Dimension in the MDX because I had different Category/Account combinations in columns (Actual vs. Budget vs. Forecast Report).

If you dont specify any dimension, the dimension settings of your current view are used.

So if your Current View settings are other than the Location of your "Flag Account", that you want to use as "porperty"", you have to specify every Dimension different to your CV

Regards

Jörg

Edited by: Jörg Finster on Sep 17, 2008 11:42 AM

[Former Member]

Jorg--

Thanks for getting back.

I am trying to do a simple row expansion on account.

My evDre() memberset chokes on the expression

 SELF, ALL and (([ITEM].CurrentMember)=10) 

The evDre() returns a #ERR

#ERR Invalid member or flag in <memberset> item: ALL and (([ITEM].CurrentMember)=10), col#2

I am trying to keep this VERY simple. My "flag" dimension is account, and I am trying to only return accounts with a value of 10. I only have one row expansion dimension.

I am using BPC 5.1. You can get this to work?

Marv

[Former Member]

Hi Marv,

is a name of a Dimension I use.

Sry, I never told you that...next time I should use a "standard named dimension"

Just change to and try it again.

But This will only work if your Account Dimension is in the row expansion

If not I need more info about the Report you want to do.

(What Dimension is in the Row Expansion, on what member of Account Dimension do you save your "property/Flag" etc.)

If for ex. you have Entity in row Expansion and Flaggaccount is named "myFlag" in the Account Dimension, the Formula should look like this:

SELF, ALL and (([Entity].CurrentMember,[myFlag])=10) 

Regards

Jörg