Skip to Content
author's profile photo Former Member
Former Member

BPC Report Filter on Values Rather than Properties?

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 12, 2008 at 07:49 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 15, 2008 at 05:21 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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

  • author's profile photo Former Member
    Former Member
    Posted on Sep 18, 2008 at 05:28 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Sep 19, 2008 at 06:15 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.