Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I'm hoping to get some feedback on this multi-parted problem. The end goal with this problem is to create reports within SharePoint from data contained in some db.

Currently, an Excel report exists with heavy VBA that pulls data from Access. I've been tasked with migrating Access to SQL Server, updating the data connection, and ultimately creating some interface that will produce the same report within SharePoint. I have had previous experience with SharePoint, specifically lists, and it's my understanding lists are stored in Access. My initial thought is that this process will take significant effort, so it may be easier to keep the data in Access, and just integrate that data with reports in SharePoint.

I've been researching reporting within SharePoint and it seems as though some particular license may be needed or the reporting template needs to be enabled to use the functionality (which is currently not enabled on our instance). Otherwise, I assume that it may be possible to create a Web Part using a the Excel report. The only issue with this is that depending on the user's access, some parts of the report will be hidden and different data will be populated. Can SharePoint replicate this?

Again, any feedback is appreciated.

share|improve this question
add comment

1 Answer 1

up vote 1 down vote accepted

Depending the complexity of your database, it might be best to just shift the entire schema and data over to SQL Server (Access has a feature to do this, can't remember the name of it myself).

If you have SQL Server Standard you can install SQL Server Reporting Services (SSRS) with Integrated Mode, which basically integrates the storage of reports and other metadata in SharePoint rather than a Reporting Web Service database. This makes the storage and management of your reports hell of a lot simpler, not to mention the publishing of Reports using the SharePoint web parts available for SSRS.

If it were me, I would take this route.

share|improve this answer
    
James, thanks for your insight. I stumbled upon SSMA (SQL Server Migration Assistant) for Access that should help the migration. After further research, it seems that an external DB will have better performance and provides better flexibility. –  Kermit Apr 30 '12 at 14:37
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.