13 Replies Latest reply on Nov 29, 2015 3:46 PM by Sam999

    Column selector through Presentation variable in dashboard adds up data

    Sam999

      Hi,

       

      Implemented the dynamic column selector for a column in Dashboard  through variable prompt but the implemented dashboard column selector adds up data and displays the sum of measures for all the rows displayed. Just FYI, the RPT is built with Essbase as the source.

       

      (A)    Analysis without Column selector-                                                                            (B)  Analysis in Dashboard with column selector"Cost Center" 

      SP1.jpgSP2.jpg

      PS-The Measures in each row in (B) is  Sum of Measures of Central + North South + West +Canada of (A) above

       

      The Columns prompt is defined as -

      CASE

      WHEN '@{POV_CostCenter}{CostCenter}' = 'Region' THEN "CostCenter"."Gen3,CostCenter"

      WHEN '@{POV_CostCenter}{CostCenter}' = 'GM Office/ Territory' THEN "CostCenter"."Gen4,CostCenter"

      END

       

      Capture3.JPG

       

       

      Can you please help.

       

      Thanks!

        • 1. Re: Column selector through Presentation variable in dashboard adds up data
          Gianni Ceresa

          Hi,

          Same behaviour with the "GM Office/Territory" value?

          What does the physical MDX sent to Essbase looks like? A theory could be that if the case when is pushed down to essbase but can't be managed correctly it's why you end up with the total of dimension (or something close to it).

          • 2. Re: Column selector through Presentation variable in dashboard adds up data
            Sam999

            Hi Gianni,

            Same behavior with "GM Office/Territory".

            Observed behavior- The Analysis aggregates all the cost center data and does not depends on the columns value passed. Any other way to implement column selector in a dashboard is welcome too.

             

            The querry is -

             

            SELECT

               0 s_0,

               "SPVeritev - Rpt"."Account"."Gen4,Account" s_1,

               "SPVeritev - Rpt"."Scenario"."Gen2,Scenario" s_2,

               "SPVeritev - Rpt"."Segment"."Gen3,Segment" s_3,

               "SPVeritev - Rpt"."YearTotal"."Gen5,YearTotal" s_4,

               CASE

            WHEN 'CostCenter' ='Region' THEN "SPVeritev - Rpt"."CostCenter"."Gen3,CostCenter"

            WHEN 'CostCenter' ='GM Office/ Territory' THEN "SPVeritev - Rpt"."CostCenter"."Gen4,CostCenter"

            END s_5,

               SORTKEY("SPVeritev - Rpt"."Account"."Gen4,Account") s_6,

               SORTKEY("SPVeritev - Rpt"."Scenario"."Gen2,Scenario") s_7,

               SORTKEY("SPVeritev - Rpt"."Segment"."Gen3,Segment") s_8,

               SORTKEY("SPVeritev - Rpt"."YearTotal"."Gen5,YearTotal") s_9,

               "SPVeritev - Rpt"."Rpt"."FY15" s_10

            FROM "SPVeritev - Rpt"

            WHERE

            (("Account"."Gen4,Account" = 'Net Sales') AND ("Scenario"."Gen2,Scenario" = 'Actuals'))

            ORDER BY 1, 10 ASC NULLS LAST, 9 ASC NULLS LAST, 7 ASC NULLS LAST, 6 ASC NULLS LAST, 8 ASC NULLS LAST

            FETCH FIRST 65001 ROWS ONLY

            • 3. Re: Column selector through Presentation variable in dashboard adds up data
              Gianni Ceresa

              Hi,

              The one you posted is the LSQL (logical SQL) and not the physical SQL / MDX.

               

              But in your LSQL there is already a piece of the answer I guess:

              CASE

              WHEN 'CostCenter' ='Region' THEN "SPVeritev - Rpt"."CostCenter"."Gen3,CostCenter"

              WHEN 'CostCenter' ='GM Office/ Territory' THEN "SPVeritev - Rpt"."CostCenter"."Gen4,CostCenter"

              END

              This CASE WHEN doesn't return any column, just a NULL value. So if you don't set a value (generation) for your CostCenter dimension Essbase return you the value of the Gen1 = the total.

              Isn't that the reason of what you see on screen?

               

              Don't you have a spelling error in the name of the presentation variable? Is it correctly set by the prompt?

              • 4. Re: Column selector through Presentation variable in dashboard adds up data
                Sam999

                Hi,

                 

                The Physical SQL is as below.I guess I have found the reason for the summing up. In the below SQL, instead of the presentation variable {POV_CostCenter}, the Dimension member is evaluated with the user option and thus the condition returns a null value.

                 

                I am bit confused though on how to make the presentation variable evaluated against the user prompt value('Region' or 'GM Office/Territory') and display the cost center members(GEN3 members or GEN 4 members) .

                 

                SET VARIABLE QUERY_SRC_CD='Report',SAW_DASHBOARD='/users/shakti.panda/vD1',SAW_DASHBOARD_PG='page 1',SAW_SRC_PATH='/users/shakti.panda/vT1';SELECT

                   0 s_0,

                   "SPVeritev - Rpt"."Account"."Gen4,Account" s_1,

                   "SPVeritev - Rpt"."Scenario"."Gen2,Scenario" s_2,

                   "SPVeritev - Rpt"."Segment"."Gen3,Segment" s_3,

                   "SPVeritev - Rpt"."YearTotal"."Gen5,YearTotal" s_4,

                   CASE

                when 'CostCenter' ='Region' THEN "SPVeritev - Rpt"."CostCenter"."Gen3,CostCenter"

                when 'CostCenter' ='GM Office/ Territory' THEN "SPVeritev - Rpt"."CostCenter"."Gen4,CostCenter"

                END s_5,

                   SORTKEY("SPVeritev - Rpt"."Account"."Gen4,Account") s_6,

                   SORTKEY("SPVeritev - Rpt"."Scenario"."Gen2,Scenario") s_7,

                   SORTKEY("SPVeritev - Rpt"."Segment"."Gen3,Segment") s_8,

                   SORTKEY("SPVeritev - Rpt"."YearTotal"."Gen5,YearTotal") s_9,

                   "SPVeritev - Rpt"."Rpt"."FY15" s_10

                FROM "SPVeritev - Rpt"

                WHERE

                (("Account"."Gen4,Account" = 'Net Sales') AND ("Scenario"."Gen2,Scenario" = 'Actuals'))

                ORDER BY 1, 9 ASC NULLS LAST, 10 ASC NULLS LAST, 7 ASC NULLS LAST, 8 ASC NULLS LAST, 6 ASC NULLS LAST

                FETCH FIRST 65001 ROWS ONLY

                • 5. Re: Column selector through Presentation variable in dashboard adds up data
                  Gianni Ceresa

                  Hi,

                  Sorry but this is the LSQL and not the physical SQL. No need to look for the physical SQL anymore because , as posted in my previous post 1h ago, your issue is in your CASE WHEN.

                   

                  So same questions for you (as asked 1h ago) :

                  Don't you have a spelling error in the name of the presentation variable? Is it correctly set by the prompt?


                  Right now it is using the default value you set in your formula, so it's like the presentation variable doesn't exist or has no value.

                  • 6. Re: Column selector through Presentation variable in dashboard adds up data
                    Pedro F

                    Hi Sam,

                     

                    Only saw now that you replied in the old topic. However, Gianni already guided you on the right direction, it seems like the value of your dashboard prompt isn't getting picked up and you are always getting the default value (CostCenter) assumed in the formula.

                     

                    Just check your dashboard prompt or put here an image of the variable prompt you created.

                    • 7. Re: Column selector through Presentation variable in dashboard adds up data
                      Sam999

                      Hi Pedro,

                       

                      PFB the variable prompt screenshot-

                       

                      R.JPG

                      • 8. Re: Column selector through Presentation variable in dashboard adds up data
                        Pedro F

                        That's weird since the name of the pv you have in the formula matches the name you defined in the prompt.

                         

                        Have you tried creating a simple report and see which values gets returned with that pv? If not, create a simple report with a column that has the formula: '@{POV_CostCenter}{CostCenter}'. Put it in the same dashboard page and see if a value gets returned.

                        • 9. Re: Column selector through Presentation variable in dashboard adds up data
                          Sam999

                          Hi,

                           

                          Formula in Column-

                           

                          ColumnfJPG.JPG

                          Result in Dashboard-

                           

                          DAshboard.JPG

                          • 10. Re: Column selector through Presentation variable in dashboard adds up data
                            Pedro F

                            Hi Sam,

                             

                            To be honest, it's a bit weird that it isn't working, the presentation variable seems to be correctly set up. Maybe Gianni Ceresa has some idea about this?

                            • 11. Re: Column selector through Presentation variable in dashboard adds up data
                              Gianni Ceresa

                              It looks like the presentation variable works and receive the value correctly.

                              The source being Essbase can't have an impact as the issue happen in the LSQL, so before to be resolved to physical SQL/MDX.

                              Just tested one more time in 11.1.1.7.140527 and it really works (as we all expect), what is your OBIEE?

                               

                              Can't imagine anything else than just test some other options ... like using a column prompt setting a presentation variable and see if it get passed, use a numeric value instead of text etc.

                              • 12. Re: Column selector through Presentation variable in dashboard adds up data
                                Sam999

                                Hi Gianni Ceresa  & Pedro F,

                                 

                                The OBIEE Version in use is 11.1.1.7.0. Does it looks like a bug and can be fixed with a patch ?

                                 

                                Thanks

                                • 13. Re: Column selector through Presentation variable in dashboard adds up data
                                  Sam999

                                  Gianni Ceresa Pedro F

                                   

                                  I tried passing a value through a condition(When 1 =2 Then Condition1 Else Condition 2 ) to identify the issue at hand and identified from the below LSQL generated that  OBIEE does not create any SORTKEY column for the condition at hand and thus only the aggregated data of cost center get displayed.

                                   

                                  A) Analysis with default column value                                                                                                                  B) Analysis with condition in Column Formula

                                   

                                  SP3_VR.jpg                                       SP1_VR.jpg 

                                  SP4_VR.jpg                                                                                   SP2_VR.jpg

                                  SP5_VR.jpg

                                   

                                  Can you please suggest the workaround for this or does it looks like a bug which can be fixed with a patch.

                                   

                                  Thanks