4 Replies Latest reply on Sep 1, 2017 7:35 AM by Robert Angel

    Passing Date Parameter in Different Date Tables, in Same Dashboard

    Shams Abbasi

      Hello,

      We are Using OBIEE 11.1.1.7.150120.

      I have a requirement where we have two Different Date Dimensions(ADate, BDate) on the same Dashboard. Date Column from ADate Dim is being used in prompt where user enter values. Now the user wants to pass the Date Values in the prompt to Date value of BDate Dim used in another report on the same Dashboard. I tried creating Presentation Variable and using it in Date of BDate. But when the criteria is set to between in Dates single variable gives two values and my variable filter start giving error. Can i please get knowledge on how i can achieve this scenario.

       

      Thank You.

        • 1. Re: Passing Date Parameter in Different Date Tables, in Same Dashboard
          Epmwise

          I am not really sure if I understood the issue completely. There is an option you might want to explore, which is to use a prompt based on another report/analysis. Which will give you a logical query something like

           

          SELECT    "SUBAREA"." Metrics "."Publish Date" s_1 FROM "SUBAREA"

          WHERE ((" Metrics "."Publish Date" <= ANY (SELECT saw_0 FROM (SELECT MAX(" Metrics "."Publish Date") saw_0, MAX(" Metrics "."Publish Date") - 45 saw_1 FROM "SUBAREA") nqw_1 )) AND  (" Metrics "."Publish Date" >= ANY (SELECT saw_1 FROM (SELECT MAX(" Metrics "."Publish Date") saw_0, MAX(" Metrics "."Publish Date") - 45 saw_1 FROM "SUBAREA") nqw_1 ))) ORDER BY 1 DESC

           

          Hope this helps

          • 2. Re: Passing Date Parameter in Different Date Tables, in Same Dashboard
            Shams Abbasi

            Thank you for your response.

            What i am trying to do is

            Consider i have a Dashboard with two reports in it. Both the reports contain two Different Date Dimensions. Lets say Ordered Date and Delivery Date. In Dashboard prompt i have filter of Ordered Date. Now what i want is to see If i select ordered date for Month of Jan(01/01/17 to 31/01/17). I want to show deliveries for that month also.

            Note: These Dates are just an example and the Date Dims i am using cannot be joined.

            Thank You

            • 3. Re: Passing Date Parameter in Different Date Tables, in Same Dashboard
              Epmwise

              So, hoping that I am on the same page, this might help

               

              Thanks

              • 4. Re: Passing Date Parameter in Different Date Tables, in Same Dashboard
                Robert Angel

                Hi,

                 

                 

                I would create date prompts not based on either of the two dates, one a "From", the other a "To".

                 

                Use the From to set a variable VFromDate, the To, VTODate

                 

                Reference the date variables on filters on your reports using >= {VFromDate}{SomeVeryEarlyDefaultDateHere} and >= {VToDate}{SomeFarFutureDefaultDateHere} on both your ordered and delivery dates.

                 

                Make sure to match your formats, datetime, date in the default part, if in doubt leave it blank and then crib the format by running a query that gets no result and copying the syntax from the sql displayed

                 

                Job done!