AppId is over the quota
This post was written by Harrison Shapley, a Program Manager on the Access team.
After you start organizing your data with Access 2013 web apps, you may find yourself wishing to visualize or summarize it. For example, imagine you are tracking your deal pipeline in Access, including when you expect to get paid and how much. Wouldn't it be great if you could make a visual graph of these projections to help you make decisions?
That's where external data connections come in handy. By following the steps below, you can connect an Excel workbook directly to the SQL Server database that stores the information in your Access 2013 web app. Then, you can build live graphs and visualizations using Excel. Of course, you could copy and paste the data from an Access datasheet into an Excel workbook, but if the data in Access changes, you'd have to repeat this process each time. By setting up the connection, you streamline the process, making it much easier to generate up-to-date reports.
Best of all, you can use this same technique with other programs and services that know how to communicate with a SQL Server database, such as Crystal Reports, Zapier, desktop Access databases, and custom web pages. The possibilities are vast, but for now, we'll focus on Excel.
There are many ways to summarize data in Access 2013 web apps. If Excel is your application of choice, you can follow these steps to connect your Excel workbook to your Access 2013 web app database for easy reporting.
Step 1. Enable connections to your database
Open the app in Access, and then go to File to view the backstage (also known as the Info section of the file menu—see the picture below). There you will see information about your database, and you have options to report on your data and manage connections to your database. Before you can create reports, you need to enable connections to your database from the Connections menu. At the minimum, you will need to Enable Read-Only Connections. Depending on your situation, you may also need to enable connections "from your location" or "from all locations".
Step 2. Create a connection to your database in Excel
After enabling connections to your database, open up a new workbook in Excel and navigate to the Data ribbon. You want to Get External Data from your SQL database, so select the From Other Sources button, then From Data Connection Wizard. The parameters of the connection need to be specified manually, so selecting "From SQL Server" will not work. Once the Data Connection Wizard is open, select Other/Advanced and click Next. Select the SQL Server Native Client and click Next again.
You will then be prompted for the database connection information. If you go back to the backstage in Access, you will find an option to View Read-Only Connection Information in the Manage Connections menu. Selecting this option will bring up all of your connection information, including the server name, database name, username and password. This information can be copied directly into Excel's data connection wizard.
Copy the information as shown below and then click OK:
Step 3. Select the data to link
The next screens will ask you about what tables you want to bring in from your database. On the first screen, uncheck Connect to a specific table and click Next.
On the second screen, make sure to select Enable selection of multiple tables and Import relationships between selected tables. You only need to select the tables you want to report on, and make sure not to select any tables from AccessSystem or AccessRuntime. In this example, all of the tables were selected to be linked.
Finally, a dialog comes up asking what you want to create, including a regular table, a PivotTable, and a PowerView report. In this example, we created a PivotChart.
Step 4. Create the reports and visualizations you want
Once you click OK, a live connection will be made and the data will be fetched from the SQL server and shown on the right. You can then use the full capabilities of Excel as you normally would to create your report.
In this case, we were creating a PivotChart to show the companies with the most orders, so we brought the Company field from the Customers table to the Axis area and the Order ID field from the Orders table into the Values area. Instead of the sum of Order IDs, we wanted the count of Orders, so this was changed from the dropdown in the Values area. Finally, we needed help Excel understand how the two tables connected with each other, so we created a new relationship linking the Customer ID field in Orders to the ID field in Customers. The PivotChart now displayed as desired, and we could easily see that Company F and Company H had made the most orders.
In summary, we covered how to create reports in Excel, but this is just the beginning of what you can achieve by exposing your data through ODBC connections. By exposing your SQL database to ODBC connections, you can use any application that supports ODBC. This permits many possibilities for extensibility to any kind of custom data application, not just Access and Excel. We can't wait to see what you do with this feature! Please share your experiences and feedback in the comments section below.
No comments:
Post a Comment