topic Re: Changing source dataset while using SAS add-in for MS Excel in ODS and Base Reporting

Changing source dataset while using SAS add-in for MS Excel

LHV — Tue, 18 Sep 2012 12:01:47 GMT

Hi,

I have used SAS add-in for MS Office (Versopn 2.1) to bring a SAS dataset into Excel pivots and created multiple charts based on the pivots.

Now I need to change the source data set name from which the pivots are derived?

I tried to change the source data set of each pivot table by changing dataset name in the Connection section of Edit OLE DB Query. (click on pivot table ->Data -> Import External Data -> Edit Query).

There I changed the name in two places: 1) Data Source statement & 2) From clause of SQL query.

Upon entering User name and Password in the following prompt, it has connected to SAS server and updated the individual pivot using the new dataset. So far i was successful.

Similarly i have changed source dataset names for each of the remaining pivot tables and they were individually updated upon source dataset change.

However, unfortunately i am no longer able to see the new source dataset name in the Refresh Multiple / Refreshlist. This list still shows the old dataset names and updates only the old dataset data.

Still, i am able to update individual pivots successfully (Right Click on pivot -> Refresh Data), if i want to see the changes in the source dataset. But it is not a best way, as it is difficult go to each and every pivot to update manually when there are many of them.

Is there any way to see the updatedsource dataset names in the Refresh Multiple/Refreshlist, so that i can updates all pivots in the Excel workbook at a go.

Otherwise is there any better way of handling the situation here, i.e., changing the source dataset(s) of pivot(s) in Excel, when they are derived from a SAS dataset through SAS Add-in.

Many thanks for any help in advance

Re: Changing source dataset while using SAS add-in for MS Excel

Cynthia_sas — Tue, 18 Sep 2012 15:21:01 GMT

Hi:

This is not really an ODS or Base Reporting procedure question. The SAS Add-in for Microsoft Office uses the Metadata server and the BI server infrastructure in order to read SAS data sources, as they are defined in the metadata.

Your best bet for help with this question is to either post it in the Integration with Microsoft Office forum or the Enterprise Guide forum (which is where a lot of the BI questions end up). Otherwise, since your version of the Add-in is a few versions behind (the current version is 5.1; the previous version was 4.3) , you might just want to work with Tech Support on your question.

cynthia

Re: Changing source dataset while using SAS add-in for MS Excel

LHV — Wed, 19 Sep 2012 08:27:55 GMT

Hi Cynthia,

Sorry for posting in wrong area, many thanks for your suggestion.

LHV

Re: Changing source dataset while using SAS add-in for MS Excel

Bazza669 — Thu, 09 Apr 2015 13:52:03 GMT

Hi LHV,

Did you find an answer to this?

Cheers

Barry