HOUR 19
Producing a Report from a Single SPF 2010 List

What You’ll Learn in This Hour

Your options when creating a report from a List

Creating a List report by using Access 2010

There is a clear difference in the techniques that should be used when producing a report using Access 2010 from a single list and producing a report from two or more lists. This hour shows how to best create a report from a single list. The following hour shows how to best create a report from two (or more) lists.

Alternative Approaches to Creating a Report from a SharePoint List

We now discuss the alternative approaches to creating a report from a SharePoint list.

Creating a Report of a View While in SPF 2010

SharePoint Lists are designed to be viewed on a screen. Most views of SharePoint Lists have restrictions in the number of items that you can view on a single (screen) page.

Printing from SharePoint Lists that use standard views is therefore unsatisfactory. It is however possible to create a view that is more suitable for printing.

Here are the main points that need to be considered:

Ensure that the width of the view is not wider than can fit onto a page.

Given that when you create SharePoint Views you aren’t allowed to specify the width of fields when they display, the only easy way to control this is to reduce the number of fields to the minimum. (More controlled but more difficult is to use SPD 2010 to edit the source code of the View page.)

Change the default value for the number of lines to be shown on a page.

This is done by amending the value in the Item Limit parameter when specifying the View you want to use for printing.

The paging function in the operating system ensures that there is a page feed when one is needed by using a large number that is more than the number of items in the list; only the printing function controls page breaks.

Make clear in the name of the view that this view is intended for printing only.

This is essential to make sure that users don’t select this view by mistake and then wonder why it takes a long time for the page to appear on the screen (and why it is so long and doesn’t contain many columns). The simplest methods are the best here. Just call the view something like Only for Printing.

This method works, but the printouts produced by it are only suitable for a Share-Point administrator to make a quick check of the data contained in a List (offline) or for handing to people who understand the ad hoc nature of the list. The main reason is that managers like to manage and will request a (in their words) “small” amendment to the list before they accept it. There is no straightforward way with this kind of list to make such “small” amendments, whether they are the addition of more fields or different headings or anything else.

The other restriction with this kind of report is that it is only possible within Share-Point to create a report from a single list. If we want to create a report that uses information from different lists, we would need to create a single list that used such techniques as Lookup fields to make data from a second list appear in the single list. Doing this just to create a poor quality report doesn’t make much sense.

The following section is an overview only.

Creating a Report of a SPF 2010 List Using SQL Server Reporting Services

Using Reporting Services is perhaps the most powerful way of creating reports from SharePoint Lists, although the reports will not look any better than those produced from Access 2010.

Both SQL Server 2005 Reporting Services and SQL Server 2008 Reporting Services can be used with SharePoint systems. Setting up either application to work with Share-Point sites is complicated and is beyond the scope of this book.

Using either Reporting Services application is a several stage process, which is outlined here:

1. Install Reporting Services.

2. Adjust the installation so that it can connect with a SharePoint system.

3. Create reports.

There is a need for expertise in all three areas.

Normal Reporting Services books cover items 1 and 3 of the preceding list only, so the choice is to either buy a Reporting Services book for the version (2005 or 2008) that you are using and find (on the Internet) guides for item 2 of the previous list, or to find a book that deals specifically with using Reporting Services with SharePoint. At the moment of writing, there is a single book like this that covers MOSS 2007 and SQL Server 2008 Reporting Services. Reading it can help you with SPF 2010 and SQL Server 2008 Reporting Services but no more than that.

The following solution (using Access 2010) is both easier to set up and produces equally good-looking reports. It does however require that users have Access 2010 on their clients. This is something that you can avoid with a solution that uses Reporting Services. A Reporting Services solution is thus more scalable and more suitable for the kind of large companies that have the manpower needed to support it.

The rest of us, including the readers of this book, will find the two (single list and multiple list) solutions for reports using Access 2010 covered in this and the next hour to be perfectly good solutions for their needs.

Using Access (2010 and 2007) to Create Reports from SharePoint Lists

The third main way of creating reports from SharePoint lists is to use Access.

Both Access 2010 and Access 2007 can be used. When creating reports from a single SPF 2010 list (covered in the next main section of this hour), there is little difference between the ways the two versions work.

This book’s main focus in the section of this book on combining Office products with SPF 2010 is on the use of Office 2010, so that is what will be used for the text and for screen prints that follow both in this hour and the next one (on creating reports from multiple lists). At the end of both hours there will be a short note about the differences when using Access 2007.

Using Excel to Create Reports from SharePoint Lists

It is not that possible to use Excel 2007 or Excel 2010 to create reports from Share-Point lists.

It is, however, possible to do this with Excel 2003.

The only way to use Excel 2010 (and Excel 2007) for report creation from SharePoint lists would be to open a list in Datasheet View and copy across cells. Such a bulk copy of cells works only for certain—the simplest—kind of cells, so in most cases would mean that data would be lost before it arrives in Excel for tidying up and making a reasonably looking report.

I won’t go into this any more in this book because the alternative of using Access 2010 (or 2007) that follows is so much better.

Creating a Simple Report from a Single SPF 2010 List

After going briefly through the alternatives, we have now reached the main topic of this hour: how to create a report from a single list.

To create a report from a list, we first need a suitable list with data so here–as we’ve already done this earlier in the book–is just a quick run-through of what I did to create the list that I used for the report creation here.

I’ve created a new site called BookSite3, using the Blank Site site template using the standard technique that you have already used (Site Actions > New Site > Specify Blank Site template).

In that site, I created (Site Actions + More Options) a new List of type Custom List, called it CompanyStaffNumbers, and then added (List Settings) four columns to it (Company; Location; Purpose; Number Of Staff). Then I made the Title field not required (and removed it from the All Items view) and removed Attachments from the List. Then I added several records to display Figure 19.1.

FIGURE 19.1 The Company-StaffNumbers List with some records

The next step is to create an Access 2010 table from an SPF 2010 List using the technique described in Hour 18, “Sharing Access 2010 Tables with SPF 2010,” “Scenario 1: Creating a Linked Access 2010 Table from a SharePoint List” section that starts from Figure 18.1. Call this database BookDatabase19.

At the end of this process, you should see something like Figure 19.2.

FIGURE 19.2 The Company StaffNumbers List as an Access 2010 Table

Note: Here are a few comments on what you see in Figure 19.2:

The Title column was not deleted when the list was created. It was just marked as not required and removed from the View. Here in the database it is listed because it is a column in the List even though it has no content.

The Company, Location, and Purpose fields were marked as required, and each had defaults (HP/Boston/Marketing, respectively). The field marked New (which doesn’t exist) thus shows those default values rather than the more common spaces. They can be changed (by drop-down) when a new row is added in the table.

Some columns have been reduced in width to make Figure 19.2 fit better on the page. The All Access Objects column has also been reduced in width slightly for the same reason.

Now that we have an SPF 2010 List in Access 2010, we can use the Access 2010 Report functions.

To create simple reports from single lists, use the Reports Wizard by following these steps:

1. Click Create in the Menu line. Figure 19.3 is an extract from the ribbon items for Create.

FIGURE 19.3 Part of the Create ribbon

2. Click Report Wizard.

You may see the following Security Warning (Figure 19.4). If so just ignore it, and move on with Open.

FIGURE 19.4 An Access security warning

The Report Wizard then shows a list of Available Fields.

3. Move the Company, Purpose, Location, and Number of Staff fields from left to right to display Figure 19.5.

FIGURE 19.5 Selecting the fields for the report

4. Click Next.

Now we need to configure group levels. For this report, let’s group on Company and Purpose. The basic technique used is to click the name of the field in the left column and then to click the right arrow in the center of the screen to move the field to the right column. The result, after specifying Company and Purpose groups in this way, is shown in Figure 19.6.

5. Click Next.

Now we can decide on the fields to be sorted at the detail level. Here we have two fields remaining (because we are already grouping on the other two), so it’s logical to sort on the Location field (see Figure 19.7).

6. Click Summary Options.

In Figure 19.8, I have specified Sum and that the percent of the Total should be calculated for sums.

FIGURE 19.6 Grouping for a Report

FIGURE 19.7 Specifying the sort order

FIGURE 19.8 Specifying summary values

FIGURE 19.9 Selecting the Layout Type

7. Click OK, as shown in Figure 19.8.

8. Click Next, as shown in Figure 19.7.

The next page (Figure 19.9) lets you choose the layout from three options. The left of the page gives you a feeling for what a report using the selected layout may look like. I’m going to use the default here, Stepped, which is probably the most commonly used.

9. Click Next.

Figure 19.10 enables us to change the name of the report, so we can just add a couple of spaces in the default name (=table name) to call it Company Staff Numbers.

This screen also offers the choice of Previewing the report or Modifying it. We haven’t yet seen what the report looks like, so Previewing is the best option here. That’s the default value, too, so complete the process with step 10.

10. Click Finish.

FIGURE 19.10 Changing the title of the Report

Figure 19.11 shows the top section of the report the wizard has created.

FIGURE 19.11 Top section of the wizard-created report

Study the whole report to see if it is good enough to present to the boss.

Several things need changing from the entire report:

The company name should stand out more. Let’s make it bold and underlined.

No boss will be happy with “Marketi” or “Producti” in a report. We need to make the field wider. Because we have short company names, we don’t need to widen the Company field this time.

Including the % figure wasn’t such a great idea after all because it is not a percentage within a company but a percentage of the whole list. It’s more confusing than helpful, so let’s get rid of this row entirely.

The entire summary section at the bottom of each page is pointless. Let’s get rid of a page bottom section.

There’s a new set of steps for report modification:

1. Close the Print Preview. (There’s a large icon at the far right of the ribbon.)

This gives a report design that can be edited in detail. (Figure 19.12 shows the top part of the central section.)

2. Select the Company field in the Company Header section.

FIGURE 19.12 The central section of the Report Design page

When this has been done correctly, a yellow frame displays around Company and the Property Sheet on the right of the screen changes and is now full of information about the Company field (see Figure 19.13).

FIGURE 19.13 The Property Sheet for the Company field

To specify bold and underline, in the Property Sheet we need to amend the values for the Font Weight and Font Underline fields.

3. Click Normal in the Font Weight line. A downward-facing arrow is visible.

4. Click the down arrow and select Bold.

5. Click No in the Font Underline line. A down arrow is visible.

6. Click the down arrow and select Yes.

This completes the first task. The Company is now bold and underlined in the report.

The second task is to widen the Purpose field (under Purpose Header) so that we see Marketing rather than Marketi:

1. Select the Purpose field and get the yellow frame around it as before with Company.

We could change the width in the Property Sheet but instead follow step 2.

2. Move the cursor on the right vertical side of the yellow frame until you see a double direction arrow symbol.

3. Without losing contact with the mouse, drag the box to the right as far as you think is necessary.

You’ll notice when you do this that this action moves the header line to the right, this line to the right, and also the detail line to the right.

4. Stop the drag operation when you are satisfied the width is now suitable by letting go of the mouse.

This completes the second task.

The third task was to remove the percentage calculation. This has two parts: the word standard and a formula that can be seen by scrolling the central section to the right.

1. Select the two fields (standard and the formula field in the same row).

2. Right-click the Standard field and select Delete.

Now the text and values are gone, but there is empty space where they were. Remove the empty space with steps 3 and 4.

3. Select the Company Footer section heading.

4. Move the cursor close to the top of that section until you get an icon with a downward-pointing arrow, a smaller upward-pointing arrow connected to it, and a horizontal line between the two. When you have this, drag the top of the Company footer section heading upward. This reclaims the space that is no longer used. This completes the third task.

The fourth task is to remove the entire Page Footer section. Here we leave it in case we change our minds but make it invisible:

1. Select the heading of the Page Footer section.

2. In the Property Sheet change (Format) Visible to No.

3. Select both formulae in this section by clicking the left border of that line; here too change the Property sheet so that Visible equals No. This completes the fourth task.

Now we need to check the result by following these steps:

1. Select View at the far-left part of the ribbon.

2. Select Print Preview from the drop-down (see Figure 19.14).

FIGURE 19.14 A revised report

A quick check shows that all our aims have been achieved.

The final thing to do is to add some more items to the original SPF 2010 list called CompanyStaffNumbers. Then reopen the Report we just created in Access 2010. The additions made in the SPF 2010 list will already be incorporated in the report that you now see.

Summary

In this hour, we first looked at several alternative methods of creating reports from SharePoint lists. We then created a table in Access 2010 that was linked to a list in one of our SPF 2010 sites and using that table created a report of the list. We showed that whenever changes are made to the list data, either in the list itself or in the table, the changes are reflected in any newly generated report.

Q&A

Q. How do I remove a table in Access 2010 without also deleting the SharePoint list it is connected to?

A. It’s actually not a problem. In Access 2010, select the table linked to an SPF 2010 list and click Delete at the top of the page.

You’ll see Figure 19.15.

FIGURE 19.15 A horrific warning

Despite appearances, this terrible looking warning is actually warning you that only the table will be deleted. Nothing else happens. The SharePoint list is preserved as before. It is just no longer linked to the Access 2010 table.

Q. What do I lose when creating a report using Access 2007 rather than Access 2010?

A. In the case of creating a report from a single SharePoint list that we did here in detail using Access 2010, the answer is nothing. In both cases a linked Access table is created from the list, and the Access Report Wizard is used to create a report following what essentially are the same steps. The only difference is that the Access 2007 Report Wizard enables you to specify the style of the report (see Figure 19.16), whereas this step isn’t included in the Access 2010 Wizard.

FIGURE 19.16 Access 2007 Report Wizard offers Styles.

Q. How does Access 2010 know what server to contact to synchronize the table with the list we started with?

A. The address of the server is included in the properties of the tables in a database.

This is fine when using a network where the name of the server (SPF1 here) can be used to access the site throughout the network. It is not so fine when a TCP/IP address is used and when this changes—perhaps because the server is in a Virtual Machine in a portable that is sometimes connected to one network and sometimes to another.

Workshop

Quiz

1. Why do we use Access 2010 to provide reports on SharePoint lists?

2. Give one reason why the details of using Reporting Services to create reports from SharePoint Lists aren’t included in the book.

Answers

1. Access 2010 provides quality reports from SharePoint lists quickly. These reports when created automatically include any new data that has been added to a list since the report was run.

2. It is too complicated a procedure for the scope of this book. (It’s also too expensive a solution for the typical users of the SPF 2010 product.)