This discussion is archived
5 Replies Latest reply: Aug 22, 2012 12:19 PM by Stuart Game RSS

FR Change text value based on another member

704996 Newbie
Currently Being Moderated
Hi,

I using FR 11.1.1.3

I'm trying to change the value of a data cell based on the value of the time dimension, so below is what I'm trying to do

Change value to Quarter1 - 2012 if current point of view for time2 is Quarter1 and Current Year is 2012

Change value to Quarter2 - Current Year if current point of view for time is Quarter2

and so on

Quarter1 and Current Year are coming from different dimensions.

Please let me know if this is possible to do in FR

I can easily do this in IR.

Thanks
  • 1. Re: FR Change text value based on another member
    Stuart Game Journeyer
    Currently Being Moderated
    Hi VS

    Where are you retrieving the data from, is it Essbase or HFM?

    I'm not sure that I am understanding your issue properly as if the POV is showing time2 = Qtr1 and current year = 2012 and those are the members that you want then for Essbase and HFM it should just retrieve that data in the grid

    Is this just a part of your report, or a cell outside the main retrieval grid area?

    Regards
    Stuart
  • 2. Re: FR Change text value based on another member
    704996 Newbie
    Currently Being Moderated
    Stuart

    Sorry for confusing you, but I'm retrieving from Essbase and you're right that Qtr1 and 2012 are POV.

    I'm showing data for multiple quarters so if POV is quarter 2 and 2012 I will be showing something like

    Quarter2 -2012 ----> is kind of sub heading for this section

    data

    Quarter3 -2012

    data

    Quarter4 -2012

    data


    Quarter1 -2013

    data

    so I was trying to use IFthen statement but it didn't work, so may be I should use conditional formatting. Please let me know if I'm clear this time.

    Thanks for your time.

    VS

    Edited by: vs on Aug 15, 2012 12:48 PM

    Edited by: vs on Aug 15, 2012 12:48 PM
  • 3. Re: FR Change text value based on another member
    Stuart Game Journeyer
    Currently Being Moderated
    Hi VS

    No problem it is always a bit open to interpretation when someone posts in these forums :-)

    So it sounds like you want to use Text based headings rather than data. Have you looked at the text functions rather than tring to do this with formulas. i think you might have more success using text rows and referencing the rows containing the member names, e.g. if you have two dimensions Period and Year (both in rows) then make use of the following function:

    Generic syntax:
    <<MemberName("GridName", Row/Col/Page, "DimName")>>

    Example:
    <<MemberName("Grid1)", 2, "Period")>> - <<MemberName("Grid1)", 2, "Year")>>
    should return the name of the member from the Period dimension from row 2 and the member of the Year dimension from row 2, e.g. Qtr1 - FY12


    There is a practically identical function MemberAlias too if it is the alias you want rather than the member name. Have a look in FR studio by inserting a text row / column and then trying to insert function. There are also 2 that you can use that are no longer show in FR studio or the studio user guide but do work (or at least did up until v11.1.1.x) and that is POVMember/POVName (can't remember now!) and POVAlias used to get the member/alias from the POV for a partcular dimension, syntax for that is:

    <<POVAlias("Grid1", "DimName")>>


    Hope this helps
    Stuart
  • 4. Re: FR Change text value based on another member
    704996 Newbie
    Currently Being Moderated
    Stuart thanks for the reply and sorry for responding so late. I'm already doing what you suggested my problem is how do I change these values conditionally.

    What I mean is if POV is Quarter1

    headings will be

    FY CY, Quarter1 -CY, Quarter2 -CY, Quarter3 -CY,Quarter4 -CY ( each heading is in a separate cell)

    if POV is Quarter 3

    headings will be

    RofY CY, Quarter1 -NY, Quarter2 -NY,Quarter3 -CY,Quarter4 -CY

    Thanks again.

    VS
  • 5. Re: FR Change text value based on another member
    Stuart Game Journeyer
    Currently Being Moderated
    Hi VS

    I'm still not sure that I am getting this. Is the issue that you cannot get a variable member selection for the report based on the POV? Surely it is more than just the heading that needs to change it is what you are retrieving as well that is not correct???

    In case it is this what about the following (I'm ignoring the Full Year / rest of year bit as I don't know how your database is structured).

    1. Create your report with 8 columns (Period and Year as dimensions on the columns).
    2. First four columns: Use the POV member for Year with each Qtr in turn
    3. Second four columns: Use Relative member function with POV member for Year and offset by +1 @LEVMBRS(Year,0)
    4. You will then need a formula row with an IfThen statement that returns a 1 or 0 based on the POV member for Period (for this I often add a TP-Index member to my database, e.g. If Jan =1, If Feb =2, you could have similar for the Qtr)
    5. You suppress the columns you don't want based on the 1s and 0s in the formula row (and hide the formula row of course)

    Does that make sense? Is that anything like what you are looking for?

    Regards
    Stuart

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points