BookmarkSubscribeRSS Feed
LHV
Calcite | Level 5 LHV
Calcite | Level 5

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

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

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

LHV
Calcite | Level 5 LHV
Calcite | Level 5

Hi Cynthia,

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

LHV


Bazza669
Calcite | Level 5

Hi LHV,

Did you find an answer to this?

Cheers

Barry

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 3453 views
  • 2 likes
  • 3 in conversation