7 Replies Latest reply on Jul 3, 2015 2:41 PM by Gianni Ceresa

    Format the Repository Variable


      Hi there,


      Please assist I want to format the Repository Variable @{biServer.variables['FINANCIAL_YEAR_DATE']}  as Date in OBIEE 11g using a Static Text.

      I want to display the in this format 2015-03-11.



        • 1. Re: Format the Repository Variable
          Gianni Ceresa

          If your variable is a date you can't define a special format for it, it's a date!

          If you want a special format (like YYYY-MM-DD) and want to always be sure that your variable has that format you need to get the value as formatted text directly instead of a date, so use something like TO_CHAR(your_date, 'yyyy-mm-dd') .

          • 2. Re: Format the Repository Variable

            Thanks for a reply what I not sure of is the syntax to this. Please assist.

            • 3. Re: Format the Repository Variable

              If I understand you correctly, you mean to format this in the repository manager?

              If so, I think this will create problems when I use it on the filter

              • 4. Re: Format the Repository Variable
                Gianni Ceresa

                A repository variable is set by an init block, so open your init block and adapt the query populating the variable. It's normal SQL, so you are supposed to be able to change it easily ...

                And it's impossible to help you without knowing what is the connection pool connected with (Oracle, SQL, DB2 or anything else) and how does the query looks like.

                As said: in Oracle it would be something like TO_CHAR(your_date, 'yyyy-mm-dd')

                • 5. Re: Format the Repository Variable
                  Gianni Ceresa

                  Where are you using the variable? In which place are you trying to format it?
                  Analysis formula, static text or where?


                  If it's in a column of an analysis you simply format your column with the property of the column like any other analysis column, if you access it directly in a text view you can't (=> format it as text in the RPD)

                  • 6. Re: Format the Repository Variable

                    Im using this variable as dashboard prompt and as well as in the static text. Im using Oracle DB as my source.

                    i have an Opening Balance and Movements of Stock in between and a Closing Balance in my report. So Opening balance is < FINANCIAL_YEAR_DATE and Movements of stock is >= FINANCIAL_YEAR_DATE and <= CURRENT_DATE.


                    And I want to dispay this on the report and schedule an Agent and email it to users.



                    • 7. Re: Format the Repository Variable
                      Gianni Ceresa

                      For filtering or setting default value of a prompt you need to keep it as a date. For display the problem with a date is that you can't always easily format it the way you want, so often what you can do is to have 2 variables: one is a date used for calculations, filters and that kind of things, the second is a varchar of the same value formatted in the way you want.