7 Replies Latest reply: Aug 20, 2013 7:57 AM by marco RSS

    obiee 11.1.1.6.6 - analysis displays two dates instead of one prompted

    marco

      Hi all,

      my analysis somehow displays two dates instead of one prompted.

      How can it be?

      Tell me please what additional information should I include for you to help me.

      obiee 11.1.1.6.6

       

      Upd: prompt screenshots

      http://marchello.ccx-grads.org/img/BI_prompt_001.png

      http://marchello.ccx-grads.org/img/BI_prompt_002.png

        • 1. Re: obiee 11.1.1.6.6 - analysis displays two dates instead of one prompted
          Christian Berg

          As stated on communities.oracle.com some screenshots of the actual analysis would be nice. The prompt def. doesn't help since you're claiming a problem when applying the prompt to an analysis which sounds more like a filtering / data issue related to the analysis object and/or underlying structure rather than the prompt object...

          • 2. Re: obiee 11.1.1.6.6 - analysis displays two dates instead of one prompted
            marco

            Here are screenshots of the actual analysis:

             

            http://marchello.ccx-grads.org/img/BI_prompt_S_001a.png

            http://marchello.ccx-grads.org/img/BI_prompt_S_002a.png

             

            As you can see on the first screenshot, the analysis displays correct date 14.08.2013, but when I scroll down it displays needless date 01.02.2013...

            • 3. Re: obiee 11.1.1.6.6 - analysis displays two dates instead of one prompted
              marco

              One pal says it is nice to see sql request generated by OBIEE, hope I can do it tomorrow using our dba's help...

              • 4. Re: obiee 11.1.1.6.6 - analysis displays two dates instead of one prompted
                marco

                This is from "Administration" -> "Manage Sessions" section of obiee

                 

                1) everything seems fine, WHERE clause shows correct date

                 

                SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/VB/8.RB-213.BI.1';SELECT

                   0 s_0,

                   "VB"."VBIREP8"."AASYSDATE" s_1,

                   "VB"."VBIREP8"."ADAY" s_2,

                   "VB"."VBIREP8"."AMONTH" s_3,

                   "VB"."VBIREP8"."ARCDATE" s_4,

                   "VB"."VBIREP8"."AYEAR" s_5,

                   "VB"."VBIREP8"."CONTRAGENTID" s_6,

                   "VB"."VBIREP8"."CONTRAGENTNAME" s_7,

                   "VB"."VBIREP8"."CREDITS" s_8,

                   "VB"."VBIREP8"."DEPOSITARY" s_9,

                   "VB"."VBIREP8"."DEPOSITS" s_10,

                   "VB"."VBIREP8"."IDENTIFYCODE" s_11,

                   "VB"."VBIREP8"."OVERDAY30" s_12,

                   "VB"."VBIREP8"."RESTRUCTURED" s_13,

                   "VB"."VBIREP8"."SEGMENT" s_14,

                   "VB"."VBIREP8"."STATUS" s_15,

                   "VB"."VBIREP8"."TOBO" s_16

                FROM "VB"

                WHERE

                (CAST("VBIREP8"."ARCDATE" AS DATE) = date '2013-08-15')

                ORDER BY 1, 3 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 5 ASC NULLS LAST, 7 ASC NULLS LAST, 8 ASC NULLS LAST, 12 ASC NULLS LAST, 15 ASC NULLS LAST, 16 ASC NULLS LAST, 9 ASC NULLS LAST, 14 ASC NULLS LAST, 13 ASC NULLS LAST, 11 ASC NULLS LAST, 10 ASC NULLS LAST, 17 ASC NULLS LAST, 2 ASC NULLS LAST

                FETCH FIRST 100000001 ROWS ONLY

                 

                2) and now attention, I press "next rows" arrow, my WHERE clause is simply ABSENT! 

                 

                SET VARIABLE QUERY_SRC_CD='Report',SAW_SRC_PATH='/shared/VB/8.RB-213.BI.1';SELECT

                   0 s_0,

                   "VB"."VBIREP8"."AASYSDATE" s_1,

                   "VB"."VBIREP8"."ADAY" s_2,

                   "VB"."VBIREP8"."AMONTH" s_3,

                   "VB"."VBIREP8"."ARCDATE" s_4,

                   "VB"."VBIREP8"."AYEAR" s_5,

                   "VB"."VBIREP8"."CONTRAGENTID" s_6,

                   "VB"."VBIREP8"."CONTRAGENTNAME" s_7,

                   "VB"."VBIREP8"."CREDITS" s_8,

                   "VB"."VBIREP8"."DEPOSITARY" s_9,

                   "VB"."VBIREP8"."DEPOSITS" s_10,

                   "VB"."VBIREP8"."IDENTIFYCODE" s_11,

                   "VB"."VBIREP8"."OVERDAY30" s_12,

                   "VB"."VBIREP8"."RESTRUCTURED" s_13,

                   "VB"."VBIREP8"."SEGMENT" s_14,

                   "VB"."VBIREP8"."STATUS" s_15,

                   "VB"."VBIREP8"."TOBO" s_16

                FROM "VB"

                ORDER BY 1, 3 ASC NULLS LAST, 4 ASC NULLS LAST, 6 ASC NULLS LAST, 5 ASC NULLS LAST, 7 ASC NULLS LAST, 8 ASC NULLS LAST, 12 ASC NULLS LAST, 15 ASC NULLS LAST, 16 ASC NULLS LAST, 9 ASC NULLS LAST, 14 ASC NULLS LAST, 13 ASC NULLS LAST, 11 ASC NULLS LAST, 10 ASC NULLS LAST, 17 ASC NULLS LAST, 2 ASC NULLS LAST

                FETCH FIRST 100000001 ROWS ONLY

                 

                Sure, I receive another date (01.02.2013). Just one thing is strange here - why do I receive only one extra date (I should receive ALL dates in Oracle table when I perform query without any WHERE clause).

                • 5. Re: obiee 11.1.1.6.6 - analysis displays two dates instead of one prompted
                  marco

                  Comment from local reseller company:

                   

                   

                   

                   

                   

                   

                   

                   

                  It is a pity, but analysis's embedded prompt resets filters values after try to view next rows. Now we are in process of investigation.

                   

                   

                  Problem doesn't persist when you add analysis to information board, simply add or display as link. You can use this method untill we finish investigation.

                  • 6. Re: obiee 11.1.1.6.6 - analysis displays two dates instead of one prompted
                    marco

                    Got sql request from our dba:

                     

                    /* f5fa7af9 */WITH SAWITH0 AS

                    (select distinct T272556.TOBO as c1, T272556.STATUS as c2, T272556.SEGMENT as c3,

                    T272556.RESTRUCTURED as c4, T272556.OVERDAY30as c5, T272556.IDENTIFYCODE as c6,

                    T272556.DEPOSITS as c7, T272556.DEPOSITARY as c8, T272556.CREDITS as c9,

                    T272556.CONTRAGENTNAME as c10, T272556.CONTRAGENTID as c11, T272556.AYEAR as c12,

                    T272556.ARCDATE as c13, T272556.AMONTH as c14,T272556.ADAY as c15,

                    T272556.AASYSDATE as c16

                    from VBIREP8 T272556)

                    select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5, D1.c6 as c6, D1.c7 as c7,

                    D1.c8 as c8, D1.c9 as c9,D1.c10 as c10, D1.c11 as c11, D1.c12 as c12, D1.c13 as c13, D1.c14 as c14,

                    D1.c15 as c15, D1.c16 as c16, D1.c17 as c17

                    from (

                    select distinct 0 as c1, D1.c16 as c2, D1.c15 as c3, D1.c14 as c4, D1.c13 as c5, D1.c12 as c6,

                    D1.c11 as c7, D1.c10 as c8, D1.c9as c9, D1.c8 as c10, D1.c7 as c11, D1.c6 as c12, D1.c5 as c13,

                    D1.c4 as c14, D1.c3 as c15, D1.c2 as c16, D1.c1 as c17

                    from SAWITH0 D1 order by c3, c4, c6, c5, c7, c8, c12, c15, c16, c9, c14, c13, c11, c10, c17, c2 ) D1

                    where rownum <= 100000001

                     

                    Could someone please comment why obiee generates this sql statement using nested statements and distinct ?

                     

                    I thought this is because inproper view, but no, here is view source (maybe hint is inproper, but there are no distinct-s) :

                     

                    CREATE OR REPLACE FORCE VIEW ORACLEBI.VBIREP8

                    (RNUM, ADAY, AMONTH, AYEAR, ARCDATE,

                    CONTRAGENTID, CONTRAGENTNAME, IDENTIFYCODE, SEGMENT, STATUS,

                    CREDITS, RESTRUCTURED, OVERDAY30, DEPOSITS, DEPOSITARY,

                    TOBO, ASYSDATE, AASYSDATE)

                    AS

                    select /*+ first_rows(1) */

                    rownum rnum,

                    extract(day from t.arcdate) aday,

                    extract(month from t.arcdate) amonth,

                    extract(year from t.arcdate) ayear,

                    t."ARCDATE",t."CONTRAGENTID",t."CONTRAGENTNAME",t."IDENTIFYCODE",t."SEGMENT",

                    t."STATUS",t."CREDITS",t."RESTRUCTURED",t."OVERDAY30",t."DEPOSITS",t."DEPOSITARY",

                    t."TOBO",t."ASYSDATE",

                    sysdate aasysdate

                    from zoo.BIREP8_RESULT t;

                    • 7. Re: obiee 11.1.1.6.6 - analysis displays two dates instead of one prompted
                      marco

                      I've started to use dashboard to display analysis, now issue with extra date has gone.