2 Replies Latest reply on Mar 11, 2017 7:19 AM by Paurav Joshi

    Automate Report which is using OLEDB Enterprise, and Excel

    Paurav Joshi

      Hello All,

       

      We have one daily report, which has following steps:

      1. Execute pre-defined query under PI Data Archive in PI SQL Commander.
      2. Copy output of step-1 to pivot table, which will create pivot chart accordingly.

       

      What is the best way to automate whole this process?

       

      Thanks,

      Paurav Joshi

        • Re: Automate Report which is using OLEDB Enterprise, and Excel
          John Messinger

          Hi Paurav,

           

          Firstly, is there a reason you can't combine these two steps, and configure the pivot chart in Excel to directly use the Enterprise OLEDB provider as an external data source, rather than executing the query in SQL Commander and copying the output into a pivot table?

          Are you then looking to automate the execution of the report on a daily basis? If so, there are a few different options for doing this. Adding some VBA code to your spreadsheet to automate a 'Save As' operation, and then closing the original file is straight forward, and you can open the file with a simple scheduled task. You can also script the whole process externally (PowerShell, VBScript etc), and call that script from a task schedule. Or you could look at SSRS as an option, though I'm not 100% sure of the availability of a pivot chart in that environment (pivot tables are there as a Tablix).