How Non-NAV Users can use NAV in the daily work – part 2

In my previous post, I described one of the ways to using NAV from non-NAV users. In NAV2013R2, we have more functionalities for non-NAV users. Now, we have KPI Web Service based on Account Schedules. The KPI Web Service makes it possible to easily integrate NAV complex analysis with Excel. That means we have use Account Schedules analysis in Excel without using NAV. However, someone must to create Account Schedules in NAV, but after creating, non-NAV users can use this in own daily work.

I always wrote about this functionality with basic detail here. But now, I will post example with more details. For better example, I will use demo NAV database.

Setup in NAV

I create new Account Schedule ‘TRIAL’:

When I start report for current year, I get next data:

Create a KPI Web Service in NAV

In the Search box, enter Account Schedule KPI Web Service Setup, and then choose the related link. In the field G/L Budget Name field, choose currently budget.

In the Web Service Name field, enter “kpi” as the name of for the web service that you are creating. In the Account Schedules FastTab, in the Acc. Schedule Name column, choose ‘TRIAL’ from the drop-down menu.

On the Home tab, in the Process group, choose Publish Web Service. The Publish Web Service function will create the Web Service and insert it in the Web Service table.

On the Navigate tab, choose Web Services. A window then opens, which contains a list of all Web Services that you have created. If you work this the first time, you will see only one line as picture below:

On the Home tab, in the Process group, choose Account Schedules KPI Web Service if you want to see detailed values for the KPIs that you have created a web service for.

Using KPI web service in Excel

Now, you need to use only Excel. If you know OData, you can use it by yourself; if you don’t know this, you need to ask some Excel specialist for the first time connection. In Excel (I use Excel 2013), I connect to Microsoft Dynamics NAV with OData using the KPI web service that I just created. On the Data tab, select From other Sources, and then choose From OData Data Feed.

Enter the location of the KPI web service (from OData URL field in Web Services page in NAV) that you want to connect to and press Next. You need to have credentials for allowing this!

Mark our ‘kpi’ and choose Next again and after that finally choose Finish. In the Import Data dialog box, select PivotTable Report or PivotChart, depends what you want.

In Excel, you can use drag-and-drop editing to move the desired fields into Pivot Table or Pivot Chart fields.

Every time you open the Excel, you need to choose Refresh on the ribbon to get the newest data in Excel and nothing else. You can make a lot of analysis in Excel sheets and don’t need to create nothing more for your analysis needed.

Notice: Whet you create new Account Schedule in NAV, in Row. No, you have to be careful with coding, because Excel default sorting in Pivot Tables.

If you use numbers:

Don’t use: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12; because sorting as 1, 10, 11, 12, 2, 3, 4, 5, 6, 7, 8, 9

Use: 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12

Or you use signs as: AA-01, AA-02, AB-01, BC-01, BC-02, BC-03, RE-01…