Dynamics GP Reporting for Remote Users – Part 3
In this three-part series, we’ve looked at reporting options that enable you to provide reports to users who may not be in the same physical location. The first option was SQL Server Reporting Services. The second option was Management Reporter. The third—covered here—is Power BI with OData.
What is OData? Without getting too technical, it's a method of getting your data via the web. Solutions like Reporting Services (see part 1 in the series) connect directly to the database. OData, though, is a middle ground between the database and the user.
Why use OData instead of direct access? First, you don't always have direct access. Second, you may not want users hitting the database directly for security reasons. Third, for performance reasons, you may not want users to run queries directly against the live database.
Beginning with Dynamics GP 2016, Microsoft provides OData functionality. It enables you to publish tables and views so systems like Excel and Power BI can be used to create reports. The components are a Windows service that you install, an SSL certificate for security across the web, OData objects you publish and security permissions to the objects. Microsoft has a video available on YouTube on the setup and configuration of OData. One important note about OData—GP 2018 provides a better experience related to performance.
From a security standpoint, you manage the security permissions to individual objects within GP using roles and tasks. GP ships with standard OData roles and tasks but you can modify these and create your own. Lastly, it’s necessary to associate a Windows login with the GP login in the User Setup window.
Here's an example of the window in GP where you publish OData objects, which are tables and views. You can publish custom tables and views as OData feeds. If you have ISV solutions like MEM you can create views and publish them as OData feeds.
In Excel, the Get Data function is used. Enter the full OData URL or the portion ending with your database name. The next image shows an example of how to enter the URL.
Once you select the OK button, the data will appear in Excel just like it does in an Excel Refreshable Report. The only difference between the two scenarios is one uses a direct SQL connection and the other uses OData.
Now that OData is working, you can use it with Excel or move on to use it with Power BI.
If you haven't heard of Power BI by now, you're missing out on a very powerful tool. Power BI provides users with the ability to create reports containing visualizations. A visualization can be something like a bar chart, a line chart and a table—but those are just objects you might have used in Excel. There are so many more that you see all the time.
For example, if you've seen the news about COVID-19, you're no doubt familiar with the curve that needs to be flattened. That's an example of a visualization. Check out a live COVID-19 Power BI report here.
Power BI Desktop, a free tool, enables you to connect to the OData URL in the same manner as Excel. Select Get Data and then OData. Enter the URL shown in the Publish OData window and a window will appear showing the data.
Reports can be developed similar to the one in the next image.
Once the reports are developed, you can publish them online. The next image shows the same report in app.powerbi.com.
Power BI online provides access to reports and dashboards. To use it, you can sign up for Power BI Pro for $9.99 per month (for each user). It’s also available through an Office 365 E5 account. In addition to accessing reports and dashboards through a browser on a computer, Power BI is available as an app on tablets and phones. The next image shows the same report on a tablet.
Dynamics GP users need information. This article has described two options: OData and Power BI. OData can be used by applications, like Excel and Power BI, to provide access to data. Power BI can be used on multiple platforms, making it advantageous for remote users.
If you have questions on this topic, reach out to your BKD Trusted Advisor™ or submit the Contact Us form below.