0

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.

1 Answer 1

1

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.

1
  • 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, 2012 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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