Excel with queries, macro, pivot tables, user input VS PowerPivot in SharePoint

Question

Hi,

I have an Excel workbook which is used for ad-hoc analysis using pivot tables. The workbook has macros, queries against an database and can handle user inputs.

I want to use Excel Services and Power Pivot for developing av new version of this report which can be shared on SharePoint.

- Of course you cant use macros

- I can query my data through the powerpivot window and link the result tables so it can be used for pivoting in the Excel sheet.

------------------------

Q: Does anyone have suggestions for how I can hadle user inputs i powerpivot?

I want my users, who will work in the Excel Sheet with pivot tables, to be able to(examples):

- define the date range for the data

- Select "Expence" data with or without administration expences

- Select standard or alternative accounting data, and so on

The message box powered by macro's has been the method for solving this till now...

I have been looking at perspectives and measures in SSAS Tabular, maybe this is a better solution that PowerPivot?

Sumeet

Answer 1

Hi,

You can define perspectives and measures in power pivot foe Excel too.

http://technet.microsoft.com/en-us/library/hh272060.aspx

http://social.technet.microsoft.com/wiki/contents/articles/1106.powerpivot-measures.aspx

You can create slicers in a sheet of Excel using power pivot for excel so that end users select the required values in those slicers so that entire work book will be sliced based on the selected values.

http://technet.microsoft.com/en-us/library/gg399096.aspx

check this link for more features of power pivot.

http://technet.microsoft.com/library/ee210639(SQL.105).aspx#DataAcquisition

======================================

Please mark as answer if helpful