Data Model, Development Tips, Excel, PowerPivot

Import data using copy and paste from Excel sheet or other source for PowerPivot Data Model.

You can copy & Paste data from an Excel workbook, or import data from a table on a web page, or any other source from which we can copy and paste into Excel. In the following steps, you add the data from a table.

  • Insert a new Excel worksheet, and give it the name desired any meaningful name that describes the data.
  • Select and copy the data, including the table headers.
  • In Excel, place your cursor in cell A1 of the above created worksheet and paste the data.
  • Format the data as a table. You press Ctrl + T to format the data as a table, or from HOME > Format as Table. Since the data has headers, select My table has headers in the Create Table window that appears.

ExcelData-1

  • Name the table. In TABLE TOOLS > DESIGN > Properties locate the Table Name field, and type desired name.

ExcelData-2

  • Format your data using Excel std. formatting tool.
  • Save the workbook.This file have couple of sheets having data related to Olympics which I have compiled from MS Office support site.
  • You can find the link on the Menu of my blogsite too to access the shared files.
  • I have create a sample Excel File for you which you can down load from the Shared Path using link: http://1drv.ms/1GpZFd4

I will be using this file for couple of walkthrough in my upcoming posts.

Development Tips, Excel, Instalation & Configuration, Jet Reports, Office Integration, Report

Configuring a Data Source in Jet Express

Select Data Source Settings from the Jet Ribbon

Use the Data Source Version drop down button and select “Dynamics NAV 2013 and later”.

JetExcel-6

Select the Web Service Tab

  • Enter the Server, SOAP Services Port and Instance.
  • If your Web Service is configured to use SSL encryption then check the “Use SSL encryption” box.
  • Pick the Jet_Data_Source codeunit.
  • Pick your default Company.
  • Click Test Connection to ensure connectivity.
  • Click OK.

JetExcel-7
Checkout Upcoming posts for more information.

Development Tips, Excel, Instalation & Configuration, Jet Reports, Office Integration, Report

Publishing the Jet Data Source Codeunit to the Web Service

Microsoft Dynamics NAV 2015 includes a “Jet Data Source” codeunit which must be published to enable Jet Essentials or Jet Express to operate. This can be published using the Microsoft Dynamics NAV Role Tailored Client (RTC).

To publish this code unit, inside the RTC go to Departments > Administration > IT Administration > General and select Web Services.

JetExcel-4
Publish the Jet Data Source code unit by selecting New from the Web services ribbon

  • Object Type: Codeunit
  • Object ID: 14125500
  • Service Name: Jet Data Source
  • And check the box to publish.
  • Click OK

Checkout Upcoming posts for more information.