Good afternoon,
We would like to pull data into Excel. After watching a Demo on Spreadsheet Server and reviewing XL Connect, I’m wondering if anyone has had a good experience with pulling data through REST and Odata. I also saw someone used CDATA Connectivity Tool. I need to know what has worked well for the User experience and the IT experience.
Thank you for your time!
EL
XL Connect is extremely slow and not worth it IMO. We had a few users excited about it, but after using it, they gave up on it. We recently shut down the XL Connect server since nobody wanted to keep using it.
It’s possible to create Excel spreadsheets in BPMs, though it’s a bit awkward. It’s very easy to right click in a Dashboard and pull the data into Excel.
There might be a product that works well for pulling data into Excel from queries directly, but XL Connect isn’t it.
@eleale We pull data into excel all the time, but we do it directly from queries (views and stored procedures) built directly into a SQL database that sits alongside of our Epicor database. If you have the skills/resources to do something like that, I highly suggest it because the long term maintenance is quite easy and the users love having completely tailored ‘real time’ data.
You can also set up the REST API to allow Excel to pull data using the BAQ service - meaning you can create BAQ’s and effectively call them from Excel. Again, this takes some work on both Excel and Epicor, but it would work too.
It really comes down to what resources you have to work with.
(PS - We’re on-prem)
@mikeGross - Thank you! The SQL Database - is that a data warehouse you use only for reporting? I have experience with using SQL for Reporting Data Warehouse. My question with Epicor, since it is realtime, how often do you run a data refresh? Is that automatically once per day or throughout the day? I was successful with pulling data into Excel with MSQuery but I’m not sure that is going to be a great long term solution. The XLC and SS solutions offer great basic reports but I know users, they always want something “not out of the box”.
What connection type do you use for Excel connecting to the SQL data? ODBC or RESTAPI?
Thanks!
EL
@eleale there are so many questions 
We do not have a separate database (copy of Epicor data) to use as a warehouse. Setting up a read only instance of the database (many ways to do this) is a very separate discussion. We use the Epicor database but only connect to it using a highly restricted login (special user with only read-only rights to what I want them to see). If we were to set one up, it would use the the native SQL functionality to keep it in synch in real time. In older versions that would be transactional replication, and in newer versions of SQL there is a new feature whose name I forget (since we’re still on an older version).
MS Query is on it’s last legs IMO and PowerQuery is where it’s at currently, but they’ve changed the query language of course. I can do MDX in a pinch, but I stay away from it currently. If you prefer visual query tools, then there are SQL query tools out there that can help you build “views” which are queries stored in the database that act like tables. But do not add them to the EPICOR database - this is my rule, and generally Epicor’s rules, but in the end you are responsible for your own database objects b/c Epicor can (and will) just ignore them during a database upgrade. You can also use those tools to build the query that can be used inside Excel if you do not want to add objects to a database.
I find it easier to split the effort based on what is needed. For large data sets like labor, GL, or multi-year analysis - I’ve built my own database and put my objects in there (tables, views, etc.). I’ll use a direct connection from Excel to SQL (with that limited user) instead of ODATA (never really use ODBC b/c it’s so slow). For smaller data sets I use Epicor’s BAQs and dashboards. You can easily export that data to Excel when needed.
I second what @MikeGross is saying regarding the REST API. That way you are managing only one “query” (BAQ). If you use it for a dashboard in EPICOR or consume the data in excel.
@MikeGross - Thank you so much! I appreciate the information. I will discuss with our IT manager and see what options we have available in SQL. Being new to Epicor, I’m trying to make sure I understand what is available and what gives the best user/IT experience.
I see lots of documentation on REST API - I will dig into it and see if I can make sense of it.
Thanks again!
@knash - Thank you! I want to make sure I don’t make a rocket ship that never launches when the users wanted a paper air plane
This is very wise. Your IT people will certainly appreciate that mindset as well 
And if your IT people want to chat about creating a utility database alongside the Epicor database or any of these other concepts, I’d be happy to chat with them as well about what we’ve done here.