Dynmic data representation - updating Excel Pivot tables?

[Stalk]

I am looking for a best way to process my data and export to excel so the users will have flexibility to filter and get the summary counts.

So far what I have done in my SAS program is:  I applied all the filters and the business rules and created a line list. Using Excel engine I wrote this line list to a pre-existing Excel template where I created a pivot table with all the row and column counts and grand total along with standard bar graphs. When I run this every afternoon the line list is overwriting the existing Excel table so my pivot table is not refreshing with new data.  Running SAS on local machine (windows environment). Open to any suggestions to give flexibility to my users to filter by start date, end date, dept. etc and get the overall counts.

Thank  you

[Reeza]

Make sure you're building your Excel template such that your pivot tables are being built off an Excel Table not a range. Ranges won't dynamically update if you add rows, but a Table will.

You will also need to add a step to REFRESH or force a refresh on opening so that the tables are refreshed. Your source data can be at whatever level you want to include in the dashboard, I usually hide it and the users only see the pivot table but do have access to the underlying data if needed.

[Stalk]

Yes, I created the pivot table and charts from excel table instead of the range. Every day when I updated my line list( excel table) that table is dropped and recreated that's when the link is broken between the pivot table and excel table as a result its not getting refreshed, even when I checked the refresh data when opening the file.  May be appending the rows might work but I cannot use append because some of my previous data will be updated so I need to write the complete table every day.

How do you create the pivot table? ODS or DDE or just proc export?

[Stalk · Accepted solution]

I found a solution...

option 1: Creating table using libname BL xlsx file="c:\temp\temp.xlsx"; - this method will not work  

option 2: Only ODS will work..

ods excel file="c:\temp\temp.xlsx" options(sheet_name="shoe_report");

proc print data=sashelp.shoes;
run;

ods excel close;

Followed this link and was able to create pivot table. Very helpful: https://blogs.sas.com/content/sgf/2015/03/27/using-sas-to-add-pivottables-to-your-excel-workbook/. But have some issues with chart creation

[Reeza]

It seems like when you're exporting and replacing the data the link was broken but you never showed us how that was happening. If my memory is correct, PROC EXPORT with XLSX does not do the replacement correctly but the PCFILES or ODBC will replace the table correctly. You also need to ensure you drop the table before you recreate it.

[Stalk]

While using the libname excel engine statement (option 1 in my post), I am writing to a preexisitng Excel template where the pivot table and pivot chart are locally created in Excel template using the line list exported from SAS. Next day when I run my SAS program new line list is over writting the exisitng line list in excel( which is nothing but the Excel table) the source of local pivot table.

By using option 2, Pivot table is created in the SAS program with all the dimentions of columns and rows and then exported to the Excel.

Hope this clarifies your question.