This discussion is archived
9 Replies Latest reply: Aug 10, 2010 5:27 PM by 764399 RSS

Substitution variables in Report scripts in 9.3.1

629017 Newbie
Currently Being Moderated
I want to create a report script where I want to show only the rolling 18 months but it seems it doesn't like if I write (&StartMonth:&EndMth).A calc script will take that but not a report script.
Instead of showing all the months which falls between these 2 sub variables it just shows the StartMonth & EndMth.

How can I accomplish this ? Is hardcoding the months only way to do this ?

Thanks in advance.
  • 1. Re: Substitution variables in Report scripts in 9.3.1
    784838 Newbie
    Currently Being Moderated
    I believe the syntax you've tried is only valid as a mathematical range statement in a calc script of member formula. I suggest you set up the full rolling series of sub vars (StartMo_plus1, etc.) and then call them out individually on your report. I would not call this hard coding since you are still using variables in a re-usable report layout. It is just coding of all needed specific variables.

    Of course, you will need to now maintain all 18 substitution variables just like you would the start and end month variables you are already using.

    Good luck, Darrell
  • 2. Re: Substitution variables in Report scripts in 9.3.1
    GlennS_3 Oracle ACE Director
    Currently Being Moderated
    I have not gotten a range like you tried to work in the past. I've done two things to work around it
    1. add UDAs to members and select the UDAs.
    2. Create a ssingle substitution variable that has all 18 periods just as they would appear in a script. Something like "Jan 2010","Feb 2010","Mar 2010" etc. Since the names are in the correct format, the one substitution variable would get inteperted as the list of periods you want
  • 3. Re: Substitution variables in Report scripts in 9.3.1
    784838 Newbie
    Currently Being Moderated
    Just FYI, the approach I suggest with a full set of individual rolling variables will best position you for the same type of reporting flexibility in both Excel and Financial Reporting Studio. Both of those technologies are grid/cell based and won't allow one variable to represent the syntax of the whole string of months (referring to one of the other replies posted to you). I agree that suggestion can work but only in the context of report script code where you must be very careful with your syntax and you'd still have to maintain all 18 months within the sub variable. My strong suggestion is to use multiple sub variables. Just my two cents!

    Darrell
  • 4. Re: Substitution variables in Report scripts in 9.3.1
    GlennS_3 Oracle ACE Director
    Currently Being Moderated
    Darrell,
    With FR it is possible to use range members with a single substitution variable to get a range of members
  • 5. Re: Substitution variables in Report scripts in 9.3.1
    764399 Newbie
    Currently Being Moderated
    Hello Glenn,

    I have the same issue as the user above. I tried to use a substitution variable containing all the 18 rolling months, but I am not able to get the required result set. instead data for Jan2010 gets copied for the 18 months. Can you please help me in this regard.

    Thanks In Advance+
  • 6. Re: Substitution variables in Report scripts in 9.3.1
    784838 Newbie
    Currently Being Moderated
    Glenn,

    Interesting. I would have thought not. In a given grid, can you just place the single subvar in, let's say, a column header cell? ...and then FR just parses that across all columns? I would think there would be trouble with nesting if there were more than one dimension in the column definition. Let me know your thoughts and ideas here.

    Darrell
  • 7. Re: Substitution variables in Report scripts in 9.3.1
    GlennS_3 Oracle ACE Director
    Currently Being Moderated
    What I was referring to was the ability to reference the member name in another column and apply functions to it. IT has been a while since I played with FR, but if I remember correctly, you could use a sub var is a column then reference that column with a prior to get prior value. In the original question, time is in a single dimension, wo it would be possible for him to easily go back 18 periods.
  • 8. Re: Substitution variables in Report scripts in 9.3.1
    764399 Newbie
    Currently Being Moderated
    Glenn\Darrell

    I am trying to use substitution variables for rolling - 18 months From Jul 2010 to Dec 2011 on Report Scripts...can you please help me in this regard.

    Thanks+
  • 9. Re: Substitution variables in Report scripts in 9.3.1
    764399 Newbie
    Currently Being Moderated
    Thanks All, I found a work around using {ORDER} and {FIXCOLUMNS}

Legend

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