Skip to content

External data available in Excel services

2011/09/28

If you want to work with table data based on an external data connection in Excel Services, you can’t use an external data range (ordinary excel table). You must create a PivotTable and make it look like an ordinary table by flatten the hierarchies into a table.

Create a connection to a data source, get data and place it in a pivot table.

Place all your "columns" in Row Labels area.

image 

Set the options so that the pivot table don’t show subtotals and grand totals.

image

Select the option to show the pivot table in Tabular Form. You’ll find it on the ribbon under PivotTable Tools tab and then Design tab.

image

To remove the +/- signs before each level click the Buttons button under Show/Hide group in PivotTable Tools, Options on the ribbon.

image

If you publish this to SharePoint using Excel Services to show it you can now filter, refresh and all the things you can do with a pivot table in Excel Services. With the look of an ordinary table.

Advertisements

From → Excel, Patrik, Sharepoint

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: