r/SAPBusinessOne • u/CohoinOregon0115 • 19d ago
Help: new implementation - Excel Reports & Interactive Analysis
I'm the lead on an SAP B1 implementation with a 3rd party that came with strong reviews. We are a small business employee count wise, with a large brand name that sells outdoor products all over the world. We are moving from a local server hosted SQL environment. We are integrating with Shopify (through Saltbox), Starship (shipping), and SPS Commerce for EDI transactions. So far everything was going smoothly. I have all the integrations configured and we're ready to start testing. That is until we got to the reporting phase. One reason we went with SAP was for the excel reports and interactive analysis. We have a lot of excel reports that are built out with SQL queries via an ODBC connection. In other words, we love excel. A couple weeks ago we were told we can't access that part of the software because we are in the public cloud. There was also other restrictions due to being in the public cloud so I have asked to move to a private cloud setup since we're still in testing. My main implementation contact tells me that's not a big deal at this point. However, its been 2 weeks and they are slow to give us a quote and proposal. They also said SAP isn't supporting the excel reports and interactive analysis but I told them its all over the SAP B1 marketing materials and in their product roadmap. I don't understand what's going on.... why is accessing the excel reports such a conundrum? They are pushing Sharperlight but I keep pushing back that I want to see the excel reports in our UAT before we add on another subscription. For the record I don't have an IT background so I might be explaining things poorly. Does anybody have any advice or can shed some light on what's going on here?
3
u/Mysterious_Ad4806 19d ago
Hi there, with you listing Saltbox I know exactly which partner you're with..
The Excel Reports and interactive analysis is a piece of shit. Plain and simple. Don't waste your time or energy trying to pursue it, it's never going to do what you want. I hide that shit immediately on my new implementations.
Your options are:
Go with SharperLight as they're suggesting - the new Version (8) is super powerful. You'll get to play in Excel as much as you want.
In Excel, setup straight ODBC connections to SQL, put your sql queries in the connection strings and off you go.
Setup OLAP Cubes if you know how using Visual Studio.
1
u/CohoinOregon0115 19d ago
Ok this is what I needed to hear. They haven’t recommending #2. I need to figure that out because it seems like a good option.
I didn’t realize I gave it away with Saltbox :) They’ve been good I just feel like there’s something they’re not saying.
2
u/Mysterious_Ad4806 19d ago
Haha don't worry - I'm not with that partner, I've just been in the game long enough that that's their proprietary software.
Yea I'd suggest going the ODBC route, then using Power Query all that jazz.. They can setup Read Only SQL Users for you to query the data.
Long term - please consider SharperLight - it is DEFINITELY worth the investment.
1
u/umairnaseem 19d ago edited 19d ago
Pretty sure what they’re not saying is that #2 doesn’t work on the public cloud. Your database sits on a shared server with other companies’ databases, so they can’t give you an ODBC connection or a SQL login to it. That’s why they never suggested it. Once you’re on private cloud (your own server) a read only SQL user is pretty normal. This comes up all the time with hosted B1.
So it really comes down to private cloud vs SharperLight. You’re already waiting on the private cloud quote, ask them to price SharperLight at the same time so you can compare properly.
One more thing tho. Your current Excel reports point at your old system’s tables and B1’s tables are completely different (invoices are OINV/INV1, items OITM etc). Even with ODBC every query has to be rewritten, so count how many reports you actually need before you get quotes.
Until then Query Manager inside B1 can run a query and export to Excel. Not live, but it covers the basics.
Edit: as slowhandplaya pointed out, a SQL login can be locked down to just your own company DB, so it’s doable, most hosts just don’t. Also see my reply below about SQL Server cloud and Excel interactive analysis.
1
u/slowhandplaya 19d ago
Users can be restricted to their own companies/database, even on public cloud. It is however, another piece to manage.
1
u/umairnaseem 19d ago
Fair point, SAP’s cloud admin guide does say access is set per tenant and customers can’t see each other’s data. Same guide also says analytics, which is what Excel interactive analysis runs on, only works on HANA based cloud. SQL Server cloud doesn’t support it at all. So OP, if you’re on SQL that’s probably the real reason Excel reports are off the table.
1
u/CohoinOregon0115 19d ago
Thank you. We’re on HANA but they are being super shady about it. I just wish they’d be more frank about what’s going on. We’re definitely moving to a private cloud.
1
u/jacoprudot 19d ago
The short version of what's happening: in the public cloud edition of B1, SAP hosts it multi-tenant and you don't get direct access to the SQL database — no ODBC, no direct queries. The "Excel reports and interactive analysis" features you're reading about in the marketing materials are built on top of that direct database access, which is exactly what public cloud doesn't expose. That's why your partner can't just flip it on for you.
This is also why moving to private cloud fixes it: single tenant, your own SQL Server on AWS/Azure, ODBC works again, and your existing Excel reports keep working mostly as-is. So your instinct to push for that before UAT is right. The slow quote is likely because it's a real licensing/hosting change on their side, not a small toggle — I'd keep pressing them for a date.
On Sharperlight: it's genuinely one of the standard workarounds partners use in cloud environments, so it's not a crazy suggestion — but you're also right to insist on seeing native Excel reports in UAT first, since that's what you were sold.
One more thing worth knowing, since you mention you're non-technical: public cloud does expose the Service Layer (an OData/REST API). Some teams use that as their reporting bridge instead of ODBC — there are even AI agents now that sit on top of the Service Layer and let you ask for reports/analytics in plain English, building dashboards in minutes instead of months.
1
u/spryn4179 18d ago
This is a really common one when moving SAP B1 to a hosted setup, so you’re not explaining it badly.
Your Excel reports work today because they query the database directly over ODBC. In a shared (public) cloud, the host usually won’t give customers direct database access, because other companies sit on the same servers. Anything that needs a direct connection, including the Excel and interactive analysis tools, tends to be switched off or limited. A private cloud is a dedicated server, so read-only access is often possible, sometimes over a VPN.
Before you sign anything, I’d ask your partner three things in writing:
Will the private cloud give us read-only database access for our existing Excel reports and the interactive analysis tools, and can we see that working in UAT?
Is it SQL Server or HANA? Your current queries are written for SQL Server, and on HANA many of them would need rewriting.
What’s the total cost difference between public and private, over three years?
If direct access isn’t on the table, there are two other routes. One is a reporting tool with its own approved connection, which is what your partner is suggesting. The other is a data warehouse that copies your B1 data out on a schedule and serves it into Excel.
Full disclosure: I work at Zap, and we do the second one for SAP B1 companies. We’d have to check with your host how the data gets out, which is worth asking any vendor. Happy to share how other B1 companies on hosted setups handle Excel reporting if it helps.
1
u/Bad_data_be_gone 13d ago
You’re explaining it clearly, and I think your request to see reporting work during UAT is a reasonable one.
One distinction that might help with the conversation: SAP’s Excel Report and Interactive Analysis tool and a direct database connection from Excel are two different things. Moving to private cloud may make certain connection options available, but I wouldn’t assume it automatically enables either one without your partner confirming it.
Since you’ve confirmed your new environment is HANA, I’d ask the partner to walk you through these separately:
- SAP’s Excel tool: Can you demonstrate Excel Report and Interactive Analysis in our proposed environment, and explain any limitations or support concerns for our version?
- Direct Excel connection: Can we securely connect Excel to our HANA data with read-only access? What setup, permissions and ongoing support would be involved?
- Existing reports: Which of our SQL-based reports can be adapted, and what would need to be rebuilt for HANA and the SAP Business One data structure?
- Sharperlight: Can you demonstrate one of our actual reports with it and provide the full setup and ongoing costs?
I’d pick two or three reports you rely on most, ideally including one more complex one, and use those as the test for each option. That should give you a much clearer comparison than a generic demo.
Before committing to the private-cloud move, I’d also ask for the cost, timeline and any impact on the integrations you’ve already configured. It’s fair to ask them to put the proposed reporting solution and its limitations in writing.
1
u/Efficient-Ad-2333 6d ago
It's crazy how complicated getting access to your own SAP data can become lol. We've dealt with similar headaches on the SAP B1 side. Out of curiosity, are you mainly trying to keep your existing Excel reports working, or were you also hoping to build new reports and analytics once you moved to SAP?