Reporting and Web Applications Using Access

Reporting and Web Applications Using Access

In this article, you’re going to complete a scenario where you’ll capture equipment/hardware requests by clients. I chose this scenario because it’s a common process that you’ll find in IT teams. Even more often I’ve seen hosting providers capture such requests to keep track of their servers. But this process can be used in any other scenario where you need to create a reservation-type system to prevent communal resources from being double-booked and to keep individual users from checking out more items than they should. Another example where this could be used, apart from the IT-related scenario we have here, is for reserving a book from a communal library. Keep using your imagination; the techniques you’ll learn here can be used for many other scenarios.

We’re going to walk through one of the options for capturing the information and reporting on it. The approach, implemented with SharePoint Foundation, will work as long as your users have SharePoint Foundation and Access. Let’s start by talking about the scenario you’ve been given and how you’ll attain the solution utilizing SharePoint.

Managing hardware reservations

The first part of this discussion, situation, provides a detailed explanation of the request that you’ve received. The next part, business priorities, will extract a list of requirements based on priority to accomplish your goals. The third part will give you an overview of the solution. By completing this you’ll learn how to build dynamic reports and web-based systems hosted on SharePoint, using Access. Many of you are probably already familiar with Access and will be excited to learn that SharePoint 2010, unlike SharePoint 2007, directly integrates with Access and allows you to host the database on a SharePoint site. We’ll dive into that technology and ensure that not only will you learn some of the basics of Access, but you’ll also know how to use the integration capabilities with SharePoint.

Situation

In this scenario, you’re part of an IT team that manages hardware that different clients can use. For example, you have rental laptops, projectors, and even servers. You need to find a way for the IT team members to capture approved requests and to report on the reserved items. Reporting back on that data is important. Gathering data is great, but you really can’t benefit from the data until you slice and dice it using reports to extract and visually display the valuable information that was gathered. Now that you understand the situation, let’s discuss the business priorities.

Business priorities

I’ll now define the business priorities so you can put together an appropriate solution:

1. Enable the IT team to manage information in relation to their hardware and clients.

2. Track approved requests with the hardware and client information.

3. Make this information dynamically available so the IT team can quickly track current reservations by hardware type and client.

4. Advanced requests: Publish this same information dynamically for the clients who don’t have Access.

I’ve also provided a process map to help you understand the system you’re going to implement. This is shown in figure 1. The next part discusses the solution we’re going to build.

Figure 1 Process map for the solution that you’re going to build. This explains the roles of the IT team and the client. It will help you to visualize the requirements and how the users will flow through the system.

Solution

For this solution, you’ll create a navigational form view that contains the reports shown in the tabs at the top of the form. To manage the input of the information, you’ll use the links on the right side of the form. The solution is displayed in figure 2.

Figure 2 Navigation form that displays the different reports at the top of the form. Links to manage the input of the data are displayed on the right side of the form.

A separate page will track the information the IT team enters, including the hardware, clients, and reservations. An example of managing reservations is shown in figure 3.

Figure 3 This reservation form captures the start and end dates for hardware reservations that are approved for the clients.

Building out this solution should take less than an hour. You’ll be impressed to find how easy it is to now dynamically generate reports on information stored in SharePoint or to allow your end users to work with data in Access though a web application published to your SharePoint site. Now that you understand the solution and its benefits, let’s get started building it out.

Leveraging Access with SharePoint Foundation

This will be our approach for implementing this scenario using SharePoint Foundation and Access. You’ll start by creating a blank site in this scenario and potentially a publishing site if you have SharePoint Server. We’ll then cover some of the core components of Access such as tables, queries, and reports. The data that you’ll be using is pulled from a SharePoint list and managed through a SharePoint site, but the reports will be generated using Access. I’m excited, so let’s get started.

Creating your Foundation site

To begin you’ll want to make sure you create a site using a Blank or Publishing Site template. Enter the following for Title, Description, and Web Site Address:

Now that you’ve created your foundation site, you’ll set up a custom list to manage the clients, hardware, and reservations. In our SharePoint Server scenario, you’ll use Access tables and publishes them via Access Services to a SharePoint site. This will allow you to meet your advanced requirement, which enables end users without Access to dynamically work with the data.

Custom list: creating a data store to manage clients, hardware, and reservations

You want to create a SharePoint list to manage your clients. You need this list so you can track important information about them such as how to contact them in case you need to follow up with them on their hardware reservation. Luckily, SharePoint has an out-of-the-box list that has already been configured to help you maintain your contacts. You can reuse this list to manage your clients by following the two easy steps listed here:

Now that you’ve created this list, you need to create a custom list that will store the information pertaining to the hardware that can be reserved. If there isn’t an out-of-the-box list like Contacts, it’s easiest to start with a custom list, which has only one column, Title. Essentially you’re starting out with a blank list, and you can easily add in your own custom columns to track information. In this scenario, you want to track information about the type of hardware you’re renting out and the ID for that hardware:

Because this is a custom list, you need to update the columns in this list to capture the appropriate information regarding your hardware, such as an ID and the type of hardware. For simplicity, you’re going to implement a basic list that captures just those two items. If you want to capture additional information, I recommend that you read the short sidebar so you can get additional ideas to further enhance this list:

Implementing with content types

If you want to make the list more robust, you could create content types associated with each hardware type. This would allow you to have unique columns associated with that hardware type. For instance, if you’re entering a server, you may want to capture information about it such as processing power and/or RAM, and this may not be valid information to capture for a projector.

Finally, you’ll create a list that manages the reservations and the associated client information and hardware information from the lists you’ve already created. When entering data regarding a reservation, you’ll want to track the contact info for the client and the hardware info for the reservation. You won’t want to track this information in multiple places, so you’ll create a lookup field that can link the reservation to the client and hardware ID. Later on when you generate your reports, you’ll combine the information from these three tables to provide the full details about the reservation in one place:

Similar to the previously created custom list, you need to update the columns in this list to capture the appropriate information regarding your reservations, such as client and hardware lookups and a start date and end date for the reservation:

Good job! Your end users now have a way to track all the vital information to get this application going. Now that you’ve built that out, we’ll jump from SharePoint to Access to show how you can pull in this data and dynamically report on the content, getting up-to-date reports as multiple end users update the tables you just created.

Linked tables: pulling the data into Access

In the previous section, you created several tables that you can work with in SharePoint to manage your information. You now want to pull that data into Access so you can generate your reports. To do this, you’ll use linked tables. There are two types of tables in Access: local and linked. To learn more about the local tables, read the following note.

NOTE For the implementation with SharePoint Server, you’ll use local tables instead of linked tables and maintain the information using web forms. This can also be done with SharePoint Foundation. Linked tables have many advantages over local tables, but they can’t be used with the SharePoint Server’s Access Services. A linked table can dynamically share data between Access and Share Point. This allows you to pull in the data from SharePoint so you can generate real-time reports.

Let’s walk through the steps to create your linked table. These steps will need to be completed in Access 2010, not SharePoint. So go ahead and open Access, but make sure you have a connection to your SharePoint environment:

You now have the data in a format that Access understands, and you didn’t have to recreate the tables in Access. You just had to follow some simple steps to pull in the data. This is useful if you need to start reporting on data that’s already been stored in various SharePoint sites. A common example of this would be user information stored in a Contacts list across projects. If you need to consolidate that information, one easy approach would be to pull in the information using this method and create a query, which creates a union between the tables. This would prevent you from having to maintain the data in multiple places but still pull it into a central location to report on. Next, you’re going to create a query so you can pull together the data from the different tables and use it to report useful information to the end users.

Client query: integrating the data in the different tables

You now have access to the data using your database, but you need to query across the tables so you can later generate a report containing information about the client, hardware, and reservation that was made.

For the first report, you want to show the hardware that’s reserved grouped by type. The fields that you want to display are Hardware Type, Hardware ID, Client Name, Email, Phone, Reservation Start Date, and End Date. To do this you’ll need to pull information across all three tables:

You’ve created an SQL query using the wizard to pull the information together across the three tables. This will make it easy for you to generate your reports because you now have the logic in place for the information that you want to pull together. Let’s see just how easy it is by diving into the next section, where you’ll create the report.

Client reports: displaying the data

Reporting is crucial to mining data and getting together valuable information that your end users can understand. If you were to show your clients or manager the raw information in those three tables, it would be cumbersome to look up. If you were to store it all in one table, it would be frustrating and error prone to have to enter the same client contact information for each reservation. Reports help you take the data from various tables and display it dynamically. Some of this can be done with lookup fields and views in SharePoint, but I encourage you to play around with these Access reports as well. You’ll quickly find that they can provide additional functionality to pull information across sites and site collections as well as enhanced reporting options to create appealing reports and to share the data. In this example you’ll create a simple report using the wizard, but I recommend you experiment more once you finish this exercise to continue learning about the power of Access 2010:

At this point, you’ll need to do some formatting to get your report to fit the page. I’m not going to walk you through this step by step; play around with the boxes in the layout view, shown in figure 4. You can select the boxes and resize them until you get your report in a format that you want.

Figure 4 Hardware Reservations report by type in layout view. Using this view you can select boxes and move them around and/or resize them until you get the design that you’re aiming for.

With six simple steps, you should now have a report that looks similar to figure 5.

Figure 5 The Hardware Reservations by Type report can dynamically pull data from the three SharePoint lists and display it as shown.

See how easy it is to create reports? I suggest that you run though the Report Wizard again and see what other reports you can create by doing some brainstorming and playing around with the additional features. Once you save the report you generated, you can open this Access database at any time to rerun the report on the information that’s been entered into your SharePoint list.

Summary

Following is a summary to help you understand the functionality that you should now be comfortable with implementing from completing this scenario:

§ Creating a custom list and working with the contact list in SharePoint

§ Pulling the data into Access from SharePoint using a linked table

Here are some other Manning titles you might be interested in:

Last updated: January 24, 2012