6 Replies Latest reply: Jul 2, 2014 3:54 PM by Mike Kutz RSS

    Export to CSV Help

    Joe R

      Version 4.1.1.00.23

       

      Hello,

       

      I'm trying to export a Classic Report that has Icons to display the Status of Projects.

       

      I've enabled the Report Export in the Report Attributes.

      When I hove over the link I see the Request parameter of the URL has: FLOW_EXCEL_OUTPUTR<internal region id>_en

       

      Within my query for the report I'm using DECODE to evaluate REQUEST.

      If the value of REQUEST is FLOW_EXCEL_OUTPUTR..... then query the table that contains the text value of the Status and if REQUEST is not FLOW_EXCEL_OUTPUT.... then return the Icon that represents the Status.

       

      I'm having trouble to get the syntax on the DECODE to evaluate the REQUEST.

      This is what I currently have (that does not work):

      ,CASE overall_status
          WHEN 'C' THEN
              DECODE(:REQUEST, 'FLOW_EXCEL_OUTPUTR'||&REGION.||'_en', (SELECT color_value FROM colors WHERE color_value = overall_status), (SELECT '<img src = "#WORKSPACE_IMAGES#' || color_link || '">' FROM colors WHERE color_value = overall_status))
      
      

       

      Can someone help me with how to do this?

       

      Please let me know if I'm not clear.

       

      Thanks,

      Joe

        • 1. Re: Export to CSV Help
          Mike Kutz

          note 1

          mixing CASE and DECODE is a solution based on insanity.

          In your case, use CASE

           

          Note 2

          "&stuff." is a flat out replacement.  Personally, I'd keep it within the single quotes.

           

          Note 3

          Please drop the scalar sub-queries by JOIN-ing the COLORS table with the rest of your SELECT statement.

          The CBO can't optimize that part of the SQL if you place them there.

           

          side note - you might not need the "overall_status= ?" section of your CASE statement due to the JOIN

           

          Note 4

          Please use table aliases and fully qualify your column names.

          It makes code a little bit easier to read.

           

          Resulting code should look like:

          SELECT
          ...
          CASE
            when overall_status='C' and :REQUEST='FLOW_EXCEL_OUTPUTR&REGION._en' then
              c.color_value -- all 'return' types must match.  use to_char() if this is a number
            when overall_status = 'C' then
              '<img src = "#WORKSPACE_IMAGES#' || C.color_link || '">'
            -- WHY are we doing a CASE based on 'overall_status' when we are already JOIN-ing on it?
          ...
          END
          FROM sometable X
            join COLORS C on ( X.overall_status=C.color_value )
          

           

           

          MK

          • 2. Re: Export to CSV Help
            Joe R

            Mike,

             

            Thanks for the help! I really appreciate it!

             

            There are actually 5 statuses that are being evaluated and didn’t think having 5 joins would be much better???

            The 5 Statuses can have different values in the Colors table to represent different Icons based on those Statuses.

            I'm not getting the values in Excel. The Icons show up correctly in the Report.

             

            This is what I have now:

            ...
            ,CASE
               WHEN pr.overall_status = 'C' AND :REQUEST = 'FLOW_EXCEL_OUTPUTR&REGION._en' THEN c1.color_value
               WHEN pr.overall_status = 'C'  THEN '<img src = "#WORKSPACE_IMAGES#' || c1.color_link || '">'
               WHEN pr.overall_status = 'C2' THEN '<img src = "#WORKSPACE_IMAGES#' || c1.color_link || '">'
               ELSE '<img src = "#WORKSPACE_IMAGES#' || c1.color_link || '">'
            END overall_status
            ...
            FROM   report r
                INNER JOIN project_report pr ON pr.report_id = r.report_id
                INNER JOIN ryg_project rp ON rp.ryg_proj_id = pr.project_id
                INNER JOIN slt_dashboard sd ON sd.dashboard_id = rp.dashboard_id
                INNER JOIN colors c1 ON pr.overall_status = c1.color_value
                INNER JOIN colors c2 ON pr.schedule = c2.color_value
                INNER JOIN colors c3 ON pr.scope = c3.color_value
                INNER JOIN colors c4 ON pr.external_readiness = c4.color_value
                INNER JOIN colors c5 ON pr.resources = c5.color_value
            
            

             

            Any ideas? What information can I provide?

             

            Thanks,

            Joe

            • 3. Re: Export to CSV Help
              Mike Kutz

              I'm wondering if that first line of the CASE statement is acting like you want it to.

              (either that, or the "download" ignores the image column)

               

              Try something simple like:

              CASE
              WHEN pr.overall_status = 'C' AND :REQUEST = 'FLOW_EXCEL_OUTPUTR&REGION._en' THEN 'I am a CSV "&REGION." '
              else 'I am an IR'
              end overall_status
              

               

               

              MK

              • 4. Re: Export to CSV Help
                Joe R

                Mike,

                 

                I'm getting "I am an IR" in the Report and in Excel.

                 

                I changed the condition for Overall Status to include all the Statuses for that column and I'm getting the "I am an IR" in both the Report and Excel.

                 

                This is what I changed:

                CASE
                WHEN pr.overall_status IN ('B','C','C2','G','PIAR','R','Y') AND :REQUEST = 'FLOW_EXCEL_OUTPUTR&REGION._en' THEN 'I am a CSV "&REGION." '
                ELSE 'I am an IR'
                END overall_status
                
                

                 

                Question: If 'FLOW_EXCEL_OUTPUTR&REGION._en' is a string, how does &REGION. get resolved?


                Thanks,

                Joe

                • 5. Re: Export to CSV Help
                  Joe R

                  Mike,

                   

                  I got it to work.

                   

                  This is the change I made:

                  ,CASE
                     WHEN pr.overall_status IN ('B','C','C2','G','PIAR','R','Y') AND :REQUEST LIKE ('FLOW_EXCEL_OUTPUT%') THEN pr.overall_status
                     WHEN pr.overall_status = 'C'  THEN '<img src = "#WORKSPACE_IMAGES#' || c1.color_link || '">'
                     WHEN pr.overall_status = 'C2' THEN '<img src = "#WORKSPACE_IMAGES#' || c1.color_link || '">'
                     ELSE '<img src = "#WORKSPACE_IMAGES#' || c1.color_link || '">'
                  END overall_status
                  
                  

                   

                  So I changed the REQUEST from :REQUEST = 'FLOW_EXCEL_OUTPUTR&REGION._en' to :REQUEST LIKE ('FLOW_EXCEL_OUTPUT%')

                  and the values show up in the download.

                   

                  Thanks for your help Mike!

                   

                  Thanks,

                  Joe

                  • 6. Re: Export to CSV Help
                    Mike Kutz

                    nice.


                    You may want to review your logic.  It looks like you can shorten the CASE statement to just

                    CASE
                      when :REQUEST LIKE 'FLOW_EXCEL_OUTPUT%' then
                        pr.overall_status
                      else
                        '<img src = "#WORKSPACE_IMAGES#' || c1.color_link || '">'
                    end overall_status
                    

                     

                    MK