| This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post. |
If you use SAS Add-In to Microsoft Office – specifically Excel, then you already know how awesome it is to link to the SAS data on the server. First, if you are using data that needs to be refreshed – just a click of the button brings you the new rows and your report or charts are updated immediately. Awesome! Let the analysis begin …
Refreshing Your MS Office Report with SAS Data
Using the SAS Add-In to MS Excel (or Word or PowerPoint), you can build charts from the SAS data sets. You may have a report that you create each month based on an Oracle table that is extracted into SAS. On the first day of each month the data is made available, which is great because you report is due the next day and your manager gets upset when it’s not on his desk. [He’s a fan of your work – what can you say?]
Let’s make your job a little easier so you can get back to answering your fan mail. Using the SAS Data button from the SAS ribbon, you can peruse the SAS Server to find the desired data. With the found data, you can setup filters and sorting – it’s quicker to sort 100,000 rows on the SAS Server than your laptop. You can pull the dataset directly into MS Excel worksheet ready to be counted, averaged, and otherwise manipulated.
Tip! Notice that you can export the data directly to a Excel pivot table.
In the past, you would have had to cut-and-paste or type the data (eek!). With the SAS connection, you can create the report once and then each month/week/day simply click the Refresh icon to have your new data! Of course – you should probably not tell your manager about this time saver so you can take an extra coffee break. (You are so naughty!! Must be why he loves you!)
Breaking the Link to the SAS Data
Perhaps when you distribute the report, you would prefer that others not be able to make modifications to the data. For instance, if others have the SAS Add-In for MS Office on their laptop, they could also click the Refresh icon and update the report. What you don’t want is for the reports to get out of sync or when you make changes, you want others to use your most recent report.
Create a copy of the report that you intent to distribute and use this technique on it. There is no reversing this process!
To break the link, click the Manage Content icon from the SAS ribbon (see figure above) and do the following:
- Click the check box for the data set where you want to break the link.
- Click the Remove Link icon on the toolbar.
- A SAS sanity check box appears warning you that once you do this – there’s no going back! You can Undo this! Repeat after me, “This is forever!”
- Afterwards, the dataset is no longer appears in the Manage Content window. Poof … just like magic.
Learn More about the SAS Add-In for MS Office and the other SAS BI Clients!
In the new Building Business Intelligence with SAS: Content Development Examples, you can learn more about using all the SAS BI client tools. See inside the book or download the first chapter for free. Tweet me any comments or questions!
| This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post. |