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.