3 Replies Latest reply on Apr 2, 2015 1:54 PM by antexity1

    Is there a cleaner way to display dates in a Filter View?

    antexity1

      I have a filter view on my reports. Alot of my reports I am doing a rolling 13 calling variables in RPD.

       

      IE:  Item Locn As Of - Date is between  @{Last_Month_14} and @{Current_Month}


      That example above displays the following in my filter view:


      ie: Item Locn As Of - Date is between TIMESTAMP '2014-03-01 00:00:00' and TIMESTAMP '2015-04-01 00:00:00'



      I would want to business to utilize these filter views in the dashboard, but for them its hard to read.   Are there other ways to get the same result but have them display in an easy format for the business to read.


      Such as:  Item Locn As Of - Date is between 2014-03-01 and 2015-04-01


      Thank you!

        • 1. Re: Is there a cleaner way to display dates in a Filter View?
          Felipe_Idalgo

          Hello,

           

          These date columns are already mapped in your RPD? If so you should check out are configured on the physical layer with the DATE data type instead of TIMESTAMP

           

          If you load these variables with a direct query to the database, you can altarar formatting in your own query

           

          Hope this help

           

          Felipe Idalgo

          • 2. Re: Is there a cleaner way to display dates in a Filter View?
            antexity1

            Thanks for your reply Felipe,

             

            Yes, Item Locn As Of - Date is a Data Column on the physical side with a Data Format of "DATE"


            Under my repository variable's section in the RPD,


            I have this:


            SELECT
                trunc (sysdate, 'D') -1,
                trunc (sysdate, 'D') -85,
                trunc (sysdate, 'D'),
                trunc (sysdate, 'D') -55,
                trunc (sysdate, 'MM'),  <<<<< @{Current_Month}
                add_months (trunc (sysdate, 'MM'), -1),
                add_months (trunc (sysdate, 'MM'), -12),
                add_months (trunc (sysdate, 'MM'), -13), <<<<<<<<@{Last_Month_14}
                sysdate,
                sysdate-7
               
            FROM
                dual;



            We tried all kinds of ways to format it but no luck.


            Here is one we tried:

            SELECT
                trunc (sysdate, 'D') -1,
                trunc (sysdate, 'D') -85,
                trunc (sysdate, 'D'),
                trunc (sysdate, 'D') -55,
                to_char(TRUNC (SYSDATE, 'MM'), 'dd-mon-yyyy'),
                add_months (trunc (sysdate, 'MM'), -1),
                add_months (trunc (sysdate, 'MM'), -12),
                add_months (trunc (sysdate, 'MM'), -13),
                sysdate,
                sysdate-7
               
            FROM
                dual;

             


            When tested in the RPD, its good 01-APR-2015

             

            In OBIEE, it looks good when we make a dummy column and equal it to @{CURRENT_MONTH} but if we make a filter  Item Locn As Of - Date is equal to @{current_month} we get the compatible types error.   This variable is a repository variable not a session variable.


            We even cast the CURRENT_MONTH as Date in Answers:  "Inventory - Item Location"."Date - Item Locn As Of"."Item Locn As Of - Date" BETWEEN  CAST(VALUEOF("current_month") AS DATE) AND  CAST(VALUEOF("current_month") AS DATE)


            Report works but all you see in the filter view is this ""Inventory - Item Location"."Date - Item Locn As Of"."Item Locn As Of - Date" BETWEEN  CAST(VALUEOF("current_month") AS DATE) AND  CAST(VALUEOF("current_month") AS DATE)" no date values,


            Any other ideas?


            Thanks Again

            • 3. Re: Is there a cleaner way to display dates in a Filter View?
              antexity1

              Does anyone have any other idea's or is it just the limitations of Repository variables.

               

              Thank you

              Jon