I have new challenge. We have a sql data pull that produces a simple set of data. We want to use easymorph to run the sql query, export the results to an excel spreadsheet, then create a pivot table off of that data. I used the aggregates action, and it works great, the problem is our users want to be able to double click on any of the totals and see excel open a new tab with only those records. Using Aggregate only shows the counts but does not give you the option to show those rows that were included for that total in a new sheet. Can this be done in Easymorph? I will attach some simple data in one spreadsheet and an example of the pivot table we are using. It’s just got a count of account numbers per client ID as an example. Any advice is greatly appreciated.
To get the pivottable functionality you’re looking for (“double click on any of the totals and see excel open a new tab with only those records”), you’ll need to utilize the pivottable in Excel. EasyMorph isn’t really interactive with the data in that way.
The workflow for this is fairly simple (attached below). Just read in your SQL data file and Export to Excel, replacing the sheet contents where the data table is.
A couple of caveats for you.
Recreate the source data range to include all of columns A, B, and C, instead of just the area of the data table you have. This will allow the pivottable to pick up data that runs past the size (rows) you currently have allocated. (Click within the pivottable, then select the “PivotTable Analyze” tab and click the “Change Data Source” button in the “Data” group. You should jump to your “Data” sheet with a marquee around your current selection. Drag across the column headers A, B, and C, so the reference in the “Data Source” box reads: Data!$A:$C)
Next, right-click within the pivottable, and select “PivotTable Options…” from the context menu. In that dialog, select the “Data” tab and check the “Refresh data when opening the file” box on. That way, after updating the data, you only need to open the file and the pivottable will refresh itself. (Otherwise, you’ll need to click the “Refresh” button on the “PivotTable Analyze” tab after every data update.)
Once those are done, just save updated data files over the existing ones (or update the reference in the Import action in the EasyMorph workflow if you want to use different file names), and run the workflow. If you need to do any cleaning or restructuring of the data before writing it out, just add those actions between the Import… and Export… actions.
Save the attached workflow in the same folder as the SQL data file and the output pivottable file to run it “out of the box”. Otherwise, you’ll need to browse to wherever you have the SQL data file and the pivottable file saved on your system for it to work.
I hope that helps. And, we were all "EasyMorphNewbie"s at some point.
