Do you want to analyze or manipulate your data, but it is trapped in another program? This can happen with many programs, leading to onerous procedures that you have to repeat every time you want to update a report.
For example, your sales data is in your accounting program, but to do custom analysis or reporting on it you must first run a report in your accounting program to export it to Excel (or Power BI, Crystal Reports or some other program), then begin again to format and analyse it to get the result you want. It isn’t uncommon for accountants to spend hours, a day, or more each month going through the same process to prepare management reports. What if there was a better way? Often, there is.
Enter the ODBC interface. This functionality allows you to export data from a database directly into another program, in effect giving you a tunnel into your data. While this can be a bit of a challenge to set up the first time, especially if you are not familiar with the process, once it is done you can just refresh your report to have Excel go back and reread the database with the parameters you have given it and update the report.
Imagine getting a pivot table or pivot chart set up just the way you want it, then simply clicking Data, Refresh All to add in the current month’s data, instead of having to recreate it every month. Would that be worth investing a little effort to accomplish?
And when you combine this with some of the capabilities of Power Query, your reporting process may not only be simplified but also be made more powerful, allowing possibilities you had previously only wished for, as you combine data from multiple sources, add KPIs or measures, and feed these into custom reports for your end users.
If we can help you explore what might be possible in your situation, please reach out to us using the Contact Us form.
