I need to have a tool developed that will give me the possibility to get any possible data from a MS SQL database (Event Management Software (EMS)) into MS Excel without having to open up the server security. It should be an actual tool that I can sell to my clients, although in most cases I’ll be the person developing their pivot tables and charts.
The EMS also has its own Excel integration that works through their API, but it has it’s limitations on what data you can download and also it’s not very user friendly. For example it forgets which table you have loaded on which sheet so you have to manually select it each time and also it doesn’t have an auto refresh option.
My idea is to have a separate API through which you can call any possible database view or stored procedure. This way I am completely free in which information I pull from the database for which client.
So I think the job consists of two parts:
- Separate API to call view/stored procedure
- Excel toolbar through which you can select the views/stored procedures you want to load in Excel
But if you have a better solution to fulfil my requirement, please let me know.
Side note: I am the distributor of the EMS in my local region and I host the software, but I don’t own the EMS nor do I have access to any source code. The only thing I have access to is the API and the database. When it comes to reporting in Excel for my clients I want to be very flexible and don’t want to depend on the EMS company.
Please respond with:
- Your suggested solution
- The expected time to completion
- Your experience and why you are capable of performing this job
If you have any questions, don’t hesitate to ask them.