4 Replies Latest reply on Jul 16, 2014 10:19 PM by 1009219

    Using a Substitution Variable in a text box when the member isn't used in a grid

    1009219

      Hi,

       

      I'm trying to create a footer in my report to display a substitution variable for the "Period" dimension. The Period dimension is not used in any of the rows or columns in my grid in my report in FR studio. It is left as a POV. I am trying to reference the substitution variable for CurrentMonth, which is independant of what the user selects for the Peiod POV.

       

      For example, the user runs the report and selects YearTotal for the Period dimension. I want to bring back the substitution variable of &CurrentMonth for Period even though it is not selected in the Grid for the report. Is there a way to do that?

       

      The workaround I am using is adding a second Grid and selecting the Substitution variable in the row and referencing the function from the second grid, which is hidden. This is bulky, and I'm hoping for a cleaner solution. The function I'm using from Grid 2 is below:

       

      <<MemberAlias("Grid2", 1, "Period")>>

       

      If I referenced Grid1 for Period, the function would display the user's POV selection, which is not what I want.

       

      Any help would be much appreciated. Thanks so much!!

       

      Tiffany

        • 1. Re: Using a Substitution Variable in a text box when the member isn't used in a grid
          Mr.Bill

          Tiffany,

           

          I do the following which I think may fit you need:

           

          Add the "Period" dimension to the Columns section.

          Make the "Period" dimension the top most member of your column selections.

          Hide that row.

          Set the left most column to "Current Poinf of View for Period"

          Select "Same As Col A" (for example) for the remaining report columns.

          Add a column to the far right and use the &CurrentMonth substitution variable for the "Period" dimension. (The other dimensions in that column should be set, if possible, to retrieve as little data as possible.)

          Hide the new column.

          Change the function in the footer to <<MemberAlias(Grid1, A, "Period")>>

           

          This should allow your user to select their desired period without impacting the hidden column value of &CurrentMonth which will be displayed in your footer.

           

          Bill

           


          • 2. Re: Using a Substitution Variable in a text box when the member isn't used in a grid
            1009219

            Hi Bill,

             

            Thank you for your detailed response. I did consider adding a separate column to the original grid as you suggested, but it ended up being more work than adding a new grid with only 1 dimension in each row and column and then hiding the column. Thank you so much again for your suggestion - it is definitely a great option for a simple report.

             

            Thanks,

            Tiffany

            • 3. Re: Using a Substitution Variable in a text box when the member isn't used in a grid
              A.Farrag

              Hi Tiffany,

               

              If you need to restrict the POV selection for the period dimension so as the user couldn't select any member other than the subistitution variable, do as follows:

              • In workspace, open File -> Preferences -> Financial Reporting -> User point of view -> setup members -> select the DB connection -> select the substitution variable for the Period dimension -> Apply -> Ok
              • In FR studio, open the report -> put Period dimension in POV section in the dimension layout of the grid.
              • Add text box in the footer -> write the function <<POVAlias("Grid name", period dimension name)>>

               

              When the user run the report he has to select the substitution variable and it will be displayed in the text box in the footer. Also you can make the preview of user POV "off" to prevent the POV dialoge box for the user. So, he will have no choice.

               

              Regards,

              Ahmad. 

              1 person found this helpful
              • 4. Re: Using a Substitution Variable in a text box when the member isn't used in a grid
                1009219

                Hi Ahmad,

                 

                Thank you for this helpful response. This feature limits the member selection for the Period dimension for all reports within that Database, which is not what I want. I'm trying to pull a substitution variable for Period and also allow users to select the period dimension they want to run the report on.

                 

                Your steps for limiting members in a POV dimension is helpful though. Thank you for your response!

                 

                Tiffany