5 Replies Latest reply on Aug 26, 2016 1:30 PM by Monwabisa

    Drill down on dynamic column fails

    Monwabisa

      Hi Gurus

       

      I have 2 dynamic columns on my report that are based on the Chart category attribute and Chart Series Attribute  variable prompts.

       

      The columns change dynamically based on the prompt value selected.

       

      Problem:

       

      When I drill down on  No. of Work Orders, the detailed report does not pick up the filters as it would on the normal circumstances. it returns all results instead.

       

      2016-08-23_1205.png

       

      Example

       

      Summary report has 3 records and I drill down the 1st record and expect 2 records on the detailed report.

       

       

      The detailed report return all results instead of 2 records.

       

      2016-08-23_1214.png

       

      Column expressions for the summary report:

       

      Category Attributes:

      CASE '@{varCategoryAttribute}'  

      WHEN 'Supervisor Department' THEN "Supervisor"."Supervisor Department" 

      WHEN 'Manager' THEN "Supervisor"."Manager Position"

      WHEN 'Supervisor' THEN "Supervisor"."Supervisor"

      WHEN 'Work Order Location' THEN "Work Order Location"."Work Order Location"

      WHEN 'Functional Location' THEN "Work Order Location"."Functional Location"

      WHEN 'Report Location' THEN "Work Order Location"."Report Location"

      WHEN 'Asset Number' THEN "Asset"."Asset Number" END

       

      Series Attribute:

       

      CASE '@{varChartAttribute}' 

      WHEN 'Reported Week' THEN "Date (Report Date)"."Week No" 

      WHEN 'Work Event' THEN "Work Order Event"."Work Order Event" 

      WHEN 'Status Category' THEN "Work Order Status"."Work Order Status Category"

      WHEN 'Work Order Status' THEN  "Work Order Status"."Work Order Status" 

      WHEN 'Call Out'  THEN "Call Out"."Call Out Description" 

      WHEN 'Overdue' THEN "Work Order Details"."Overdue" 

      END

       

      I've made the 2 columns to be is prompted on the detailed report in hope that it would pick up on the filters.

      2016-08-23_1222.png

       

      I hope I'm clear, Please assist.

        • 1. Re: Drill down on dynamic column fails
          Pedro F

          Hi,

           

          Not really sure the presentation_variable is going to be picked up in the detailed analysis (it looks like it isn't) but in any case I think I spotted what you're missing. In your detailed report you have a filter for "Work Order Status Category is prompted" but you don't have one for "Supervisor Department is prompted".

           

          If you look at your detailed analysis results you can see that it's filtering correct on the Status Category (only Open values) but not on the Supervisor Department. I'd say that if you add that to your filter it should work.

          • 2. Re: Drill down on dynamic column fails
            Monwabisa

            Hi Pedro

             

            Thank you for the response.

            The presentation variable is able to be picked up on the detailed report. I just tested that by static text at the top of the detailed report(check the image).

             

            Even though I add the Supervisor Department to be is prompted it doesn't work.

            • 3. Re: Drill down on dynamic column fails
              Pedro F

              Hi,

               

              You're right, the presentation variable value does get through so I'm not sure why you're not able to make this work. I've built an example following the logic you want to implement and it's working fine. Take a look here and see if you can spot some difference between what I built here and what you have.

               

              http://slc02oks.oracle.com:7780/analytics/saw.dll?Portal&PortalPath=%2Fshared%2FOTN%2F_portal%2FOTN

               

              Credentials: Prodney / Admin123

              1 person found this helpful
              • 4. Re: Drill down on dynamic column fails
                Monwabisa

                Hi Pedro

                 

                It worked! the difference was that I used a CASE (Switch) instead of CASE(IF).

                 

                CASE   WHEN '@{varCategoryAttribute}{Supervisor Department}'    = 'Supervisor Department' THEN "Supervisor"."Supervisor Department"   

                WHEN '@{varCategoryAttribute}' = 'Manager' THEN "Supervisor"."Manager Position"   

                WHEN '@{varCategoryAttribute}' = 'Supervisor' THEN "Supervisor"."Supervisor"   

                WHEN '@{varCategoryAttribute}' = 'Work Order Location' THEN "Work Order Location"."Work Order Location"   

                WHEN '@{varCategoryAttribute}' = 'Functional Location' THEN "Work Order Location"."Functional Location"   

                WHEN '@{varCategoryAttribute}' = 'Report Location' THEN "Work Order Location"."Report Location" 

                WHEN '@{varCategoryAttribute}' = 'Asset Number' THEN "Asset"."Asset Number"   END

                 

                This worked and the drill down is working also.

                 

                Thank you so much !

                • 5. Re: Drill down on dynamic column fails
                  Monwabisa

                  Hi Pedro F

                   

                  I don't know whether there is a bug or what... I tried it again, this time it didn't work.

                  I'm using Oracle Business Intelligence 11.1.1.7.150120

                  I'm back to square one.

                   

                  Please assist.

                  Regards