Currently I create pivot tables in excel from linked tables inside of SQL Server. We started hitting memory limitations within 32 bit excel and would like to move the processing onto the server.

I have analysis services installed but I am unsure as to the best/easiest way to recreate this functionality. I only have 1 table and the only calculated value is a count of the rows. Seems super simple but can someone walk me through what I need to do, point me in the right direction, or recommend something else to solve this problem.

I really just want the user to be able to select columns from the table as row and column labels in a pivot and have SQL Server or Analysis server calculate the count of rows with those values. So basic pivot table functionality but the processing is done on the server not the local machine.

Cheers.

Hi cypherignite,

The best way to avoid the memory issue is use 64 bit of  Excel. I recommend you refer to the following Marco's blog about this topic, please see:
Memory Considerations about PowerPivot for Excel: http://sqlblog.com/blogs/marco_russo/archive/2010/01/26/memory-considerations-about-powerpivot-for-excel.aspx

Best Regards,