Creating Reports

If shapes have shape data fields in which you have entered data, you can generate a report that includes selected properties and values. Visio 2007 includes a number of predefined reports that you can use for specific types of information, such as asset reports, or you can create your own report and define its contents. What a report looks like depends on what you put in it and how you want to save it. Visio 2007 can save a report as an external file in XML format (with the file extension .vrd) or as a table shape on the drawing page that’s linked to the information.

To set up a new report or run an existing one, you use the Report command on the Data menu. The Report tool lists existing reports that you can run or modify, as Figure 6-12 shows, or you can define your own reports. Because the Report tool is meant to be a design tool, its contents vary depending on whether anyone has created new reports for you to run. Visio Standard 2007 and Visio Professional 2007 come with different built-in reports for specific diagram types. For example, Visio Professional 2007 includes a report called the Door Schedule, which a builder or architect might run to show all the door specifications in a floor plan. You can even customize any of the built-in reports to tailor the report to your needs.

Running a Report

Many Visio 2007 solutions include predefined reports that you can run to evaluate the data stored with a drawing. For example, a flowchart report tabulates cost, duration, and resources associated with the shapes in a flowchart. A door schedule lists all door specifications for a building plan. Table 6-3 lists the predefined reports that are included with different templates. To run a report, you use the Reports command on the Data menu, and then specify the output format you want.

Table 6-3. Built-In Report Definitions

Visio Standard Report	What It Does
Flowchart Report	For flowchart shapes, lists the text displayed on flowchart shapes as well as the values of the resources, cost, and duration shape data sorted by shape name
Gantt Chart Report	For each resource in a Gantt chart shape, lists the task name, start date, end date, duration, user-defined number, and percent complete
Organization Chart Report	For each department, lists the employee name, title, and telephone number
Shape Inventory	For all the shapes in any diagram, lists the text displayed on shapes, and each shape’s height, width, and location on the page (as x and y coordinates)
Asset Report	For shapes in a floor plan, space plan, or office layout, shows who owns an asset (the Belongs To field), as well as the asset type, name, and manufacturer
Visio Professional Report	What It Does
Door Schedule	For door shapes in a floor plan, space plan, or office layout, lists the door number, size, type, and thickness
Equipment List	For equipment components in a process engineering diagram, lists the tag, description, material, manufacturer, and model
Instrument List	For instrumentation components in a process engineering diagram, lists the tag, description, connection size, service, manufacturer, and model
Pipeline List	For pipeline components in a process engineering diagram, lists the tag, description, line size, schedule, design pressure, and design temperature
Space Report	For shapes in a space plan, lists the department, room number, use, and area
Valve List	For valve components in a process engineering diagram, lists the tag, description, line size, valve class, manufacturer, and model
Window Schedule	For window shapes in a floor plan, space plan, or office layout, lists the window number, size, and type

Visio 2007 can create the report’s contents as a table shape on the drawing page, or it can save your report as an external Microsoft Excel, HTML, or XML file.

To run a report, follow these steps.

1.	Choose Data, Reports. The Report dialog box appears and displays all the predefined reports in the Report Definition list. Different reports will appear depending on whether you have Visio Standard 2007 or Visio Professional 2007 and the diagram that you are working with.
2.	Under Report Definition, select the report you wish to compile, and then click Run.
3.	In the Select Report Format pane, select an output format. If you choose Microsoft Excel (File), HTML, or XML, specify a name for the report file that Visio 2007 creates in the folder you want. If you choose Microsoft Excel (Shape) or Visio Table Shape, choose whether to link shapes to the report definition or save a copy of it with the shapes.
4.	In the Run Report dialog box, click OK. Visio 2007 displays a progress indicator as it generates the report and displays the report when it is created. If the output is a table shape, Visio 2007 creates the shape and adds it with the shape data information to the current page.
5.	In the Report dialog box, click OK or choose another report to run.

Troubleshooting: When running a report, Visio displays a message that no shapes had the selected data or satisfied the report selection criteria Visio 2007 displays an error message and cancels the report if the shapes in your diagram do not contain shape data or contain different shape data than specified in the report definition. To see which properties are specified by a report, select the report name and then choose Modify in the Report dialog box.

Defining a Custom Report

You can define a new report from scratch or based on an existing report definition. In the report definition, you specify whether the report will include all or some of the shape data stored with your shapes. In addition, you can set up your report to run for all the shapes on a page or all the shapes in the drawing file.

To create a new report definition, choose Data, Reports, and then click New, or select an existing report definition and click Modify. When you create a new report or modify an existing one, the Report Definition Wizard walks you through the steps. Visio 2007 stores your report definition as a .vrd file in the location you specify and adds it to the list in the Report dialog box.

To define a new report, follow these steps:

1.	Choose Data, Reports. Click New. Specify whether to report on all shapes in the file or just on the page. Click Next.
2.	Select the data you want to include in the report. In addition to shape data, your report can include other internal properties that Visio 2007 tracks for shapes, as Figure 6-13 shows. Click Next. Figure 6-13. When you set up a report, Visio 2007 lists not only the shapes’ shape data fields, but also default shape data and internal properties used by the solution called user-defined data, which appear in the ShapeSheet’s User-Defined Cells section.
3.	Specify how to filter the data for your report. The Report tool reviews all the shape data, including hidden properties created for the solution and user-defined properties in the ShapeSheet. You probably don’t want all this information in your report, so you can select just the properties you want. Click Next.
4.	Choose how to format the report. You can group, sort, and specify output formats for the shape data fields and their values. Click Next.
5.	Choose whether to save the report definition as part of the current drawing file or as a report (.vrd) file that can be run from other Visio 2007 drawing files. When you save a report definition with your drawing, you can run the report whenever you open the drawing. Click Finish.

Selecting Report Contents

Defining a report starts with the decision about which shapes and properties to include. The Report Definition Wizard includes a Limit Selection option that lets you filter properties according to criteria you define, as Figure 6-14 shows. To access this feature, click Advanced on the first page of the Report Definition Wizard. You can choose to include any of the following in a report:

• Shapes on a particular layer

• Shapes that include particular shape data

• Shapes that include shape data with a particular value

• Shapes that include a particular user-defined cell value

• For network diagrams in Visio Professional 2007, device shapes that result from the AutoDiscovery command

In the Limit Selection dialog box, you use a generic query language to specify the criteria for shapes to include in your report. If you’ve used any of the typical spreadsheet or database query operations, the options here should look familiar. You select a property that you want to use in a comparison, specify an operator (such as =, <>, >, <, >=, or <=), and then type a value to compare against. Depending on the property and condition operator, the value can be true or false or a number. For example, you can filter your report so that it looks only at shapes where the Height property is greater than 2 inches. For strings, the comparison criteria can be case sensitive or case insensitive.

Formatting a Custom Report

After you’ve selected the shapes and properties you want to include, you can specify how to organize the information in your report. The Report Definition Wizard includes options for specifying how to group, sort, and format data, as Figure 6-15 shows. Visio 2007 can group the output by master name or by shape data, and then show totals and subtotals for each property. Other standard calculations (count, total, average, median, minimum, maximum) can be performed.

Performing Calculations on Report Contents

To group subsets of data and calculate new values from the data in the individual groups, as shown in Figure 6-16, use the options (listed in Table 6-4, which follows) in the Subtotals dialog box, which appears when you click Subtotals.

Table 6-4. Options in the Subtotals Dialog Box

Option	What It Does
Group By	Lists all properties. If you select shape data, report values are grouped according to the values of the selected data. In technical terms, this option is the equivalent of a GROUP BY clause in SQL.
Options	Displays the Options dialog box, where you can choose to show all values for the group, not repeat identical values, show only subtotals with a group, and show grand totals.
Property	Lists the properties you’ve selected to appear as columns in your report. For each property, you can select a calculation. If the calculation is not valid for the type of data represented by the property value, the option’s check box is displayed in gray.
Count	Calculates the number of objects with a particular value for the property.
Total	Calculates the sum of the list of property values.
Avg	Calculates the average (arithmetic mean) of the property values.
Max	Calculates the largest number from the list of property values.
Min	Calculates the smallest number from the list of property values.
Median	Calculates the median of the property values. The median is the number in the middle of a set of numbers; that is, half the numbers have values that are greater than the median, and half have values that are less.

Sorting Report Contents

When you sort a report, you determine the order in which properties are listed in the rows and columns of the final output. To sort a report, click Sort in the Report Definition Wizard. In the Sort dialog box, you sort the following:

1.

In the Column Order list, choose the order in which you want the property names to appear across the top of the report.

2.

In the Row Order options, choose the order in which property values are listed in the body of the report. You can sort a report based on the contents of one or more columns. For example, if your report contains employee information, you can sort based on the hiring date and on the employee names to show who was hired when, as Figure 6-17 shows. Figure 6-17. This report is sorted in chronological order by date of hire. For each date, employees are sorted in alphabetical order. [View full size image]

Table 6-5 lists the options in the Sort dialog box.

Table 6-5. Options in the Sort Dialog Box

Option	What It Does
Column Order	Lists the properties you’ve selected to appear in the report. When you run a report, the properties are listed in columns from left to right in the order they appear in this box. You can select a property and then use the Move Up and Move Down buttons to adjust its order.
Move Up	Moves a property selected in the Column Order box one position higher in the list.
Move Down	Moves a property selected in the Column Order box one position lower in the list.
Sort By	Sorts rows in the selected order (ascending or descending) based on the value of the selected shape data.
Ascending	Sorts values for the property in the Sort By box in ascending (1 to 9, A to Z) order.
Descending	Sorts values for the property in the Sort By box in descending (9 to 1, Z to A) order.
Then By	Sorts rows in the selected order (ascending or descending) based on the value of the selected shape data.

Specifying Numeric Precision for Values

For numeric output, you can specify the level of precision as the number of values to the right of the decimal and whether to include units of measure. In the Report Definition Wizard, click Format to display the Format dialog box, which allows you to define the way that the data values are displayed, as Figure 6-18 shows. Table 6-6 lists the options in the Format dialog box.

Table 6-6. Options in the Format Dialog Box

Option	What It Does
Precision	Applies to numbers only. Enter a value from 0 to 14.
Show Units	Specifies whether to display units of measure in the final report.

Saving a Report Definition

The final step in creating a report definition is to name the report and indicate where to save it. The Report Definition Wizard provides options for you to save your settings with the current drawing file or as a separate file that can be opened from within other Visio 2007 drawings, as Figure 6-19 shows.

Saving a report definition is a two-step process: First, click Finish in the Report Definition Wizard, and then click OK in the Report dialog box. If you click Cancel, Visio 2007 will warn you that your report definition will be lost. Table 6-7 lists the options in the Report Definition Wizard for saving your report.

Table 6-7. Options for Saving the Report Definition

Option	What It Does
Name	Identifies the report definition. The name you type here appears in the Report dialog box.
Description	Provides identifying information about the report that appears in the Description box of the Report dialog box. The text you type here can act as a prompt, which is particularly useful if others will be running the report, but it’s optional.
Save In This Drawing	Specifies to save the report definition in the drawing file. When you choose this option, you can run the report whenever you open the drawing. If you select Save In A File, this option is not available.
Save In A File	Specifies to save the report definition as an external report (.vrd) file that can be run from other Visio 2007 drawing files. Type a file path and name in the box, or click Browse to specify the path. If you select Save In This Drawing, this option is not available.
Browse	Opens the Report Wizard dialog box, which provides the standard options for saving a file. By default, Visio 2007 supplies the name Report_1.vrd for report files and saves them in the My Documents folder. You can type a new name and/or specify a different folder to save it in. Visio saves report files in .vrd format only. If you select Save In This Drawing, this button is not available.

When you save a report definition, it is listed in the Report dialog box. To see whether your report definition provides the expected output, you need to run the report. Creating a report definition does not automatically run a report. To do that, select the report name in the list of report definitions, and then click Run.

Importing Excel Information

Excel can be vital for organizing large amounts of data, but Visio 2007 can import and display that information in a way that can easily be digested. Not to worry, Visio 2007 can do this for you through a simple process using the Data Selector Window. Follow these steps to connect Excel information to your Visio diagram.

1.	On the Data menu, select Link Data to Shapes.
2.	On the Data Selector Wizard, the first page should show six types of data sources to which you can link: Microsoft Office Excel Workbook Microsoft Office Access Database Microsoft Windows SharePoint Services List Microsoft SQL Server Database Other OLEDB or ODBC data source Previously created connection
3.	Choose Microsoft Office Excel Workbook and click Next.
4.	Select the workbook you want to import and click Next.
5.	The next page will ask you what worksheet or range you want to use. There is a drop-down list of choices already available, but if you wish to type in your own range, click Select Custom Range if a range has not already been selected. A box will appear where you may type in the range you wish to have. At the bottom of the page with the drop-down list, there is a check box asking you if you want the first row of data to contain column headings. Select it if you do want column headings, or unselect it if you do not wish to have column headings.
6.	Click Next.
7.	On this page you can manually select the rows and columns you wish to use in your diagram. To do this, click on either Select Columns or Select Rows. When you choose either one of those, a list of the columns/rows you have in your Excel file is displayed. To use a column/row, make sure the check box is selected beside it. Unselect the check box if you do not wish to use the column/row.
8.	Click Next.
9.	In Visio 2007, data can automatically be updated. On this page in the Data Selector Wizard you can choose what rows will be updated when certain columns change. The list of column headings is shown in the list box, with check boxes beside each column heading. Select the headings you wish to have automatically updated; or you may choose the option at the bottom of the page that specifies nothing will be updated.
10.	Click Next.
11.	Click Finish.

After you click Finish, your Excel file will be located in the External Data Window. If you exit out of this window and wish to view it again, choose View, External Data Window.

Linking Rows of Data in Your Excel File to a Shape

There are three ways to connect rows of data to shapes in your diagram: link a row of data to a shape one at a time, automatically link the data to the shape, or create shapes from the data.

To link a row of data to a shape one at a time, follow these steps:

1.	Make sure there is already a shape in your diagram.
2.	In the External Data Window, choose a row of data, and drag it onto a shape in your diagram.

To automatically link a row of data to a shape, follow these steps:

1.	On the Data menu, choose Automatically Link.
2.	The Automatically Link window should appear, prompting you to select which shapes you wish to link data to: Selected Shapes, or All Shapes on this page.
3.	Click Next.
4.	Before Visio 2007 can automatically link data to a shape, the shape must contain data that Visio can match with the new data. In the first drop-down box, choose the Data Column you wish to have linked to a shape. In the next drop-down box, choose the shape field or the data that are already in the shape you wish to have connected. If you want to add more data to be linked, click the And button. If you don’t want data to be linked to a shape, click the Delete button. At the bottom of the Automatic Link dialog box there is a check box option that says Replace All Existing Links. If this is checked, all existing links will be broken. Uncheck this option to keep existing links.
5.	Click Finish.

To create shapes from your data, follow these steps:

1.	Make sure no shapes are selected in your diagram.
2.	In the Shapes window, select a shape.
3.	In the External Data window, drag a row of data onto your diagram. It will appear as the shape you selected in the Shapes window.

Using the Data Selection Wizard

The Data Selection Wizard greatly decreases the amount of time you spend importing data into your diagram. It basically does all the work for you. All you do is answer some questions, leaving all the thinking to Visio 2007. To access the Data Selection Wizard, on the Data menu choose Link Data to Shapes. This will open the Data Selection Wizard. In the window, choose the data you wish to use, whether your data is in Excel, Access, SharePoint, SQL Server database, or other OLEDB or ODBC data source. Depending upon which option you choose, the rest of the process will vary.

Updating Your Data

In Visio 2007, Visio can update your data for you. This can happen automatically, or you can do it manually. In Visio 2007, updating data may also be referred to as refreshing data. To refresh the data in your diagram, follow these steps:

1.	In the Data menu, choose Refresh Data. If Refresh Data is unavailable (dimmed) it could be caused by the following cases: You did not use the Data Link features to import your data. You are working in a PivotDiagram. To refresh the data in a PivotDiagram, on the PivotDiagram menu, click Refresh Data.
2.	In the Refresh Data dialog box, do one of the following: To refresh a single data source, select it in the Data Source list, and then click Refresh. To refresh multiple data sources, hold down CTRL while you select data sources in the Data Source list, and then click Refresh. To refresh all data sources, click Refresh All.

To have Visio 2007 automatically refresh the data in your drawing, follow these steps:

1.	In the Data menu, click Refresh Data.
2.	In the Refresh Data dialog box, select one or more data sources.
3.	Select the Show This Dialog Box On File Open check box.
4.	Click Configure.
5.	In the Configure Refresh dialog box, under Automatic Refresh, select the Refresh Every check box, and then click the arrows to choose an interval. It is very important to remember that for the refresh to work correctly, you must make sure you select the correct unique identifier on the right side of the Configure Refresh box.

Even though Visio 2007 makes refreshing data extremely simple, problems might still arise between old data and new data; for example, if you delete a row from your data or create a new row with the same unique identifiers as another row in the data source. In both cases, the Refresh Data command cannot refresh the data in the shapes because Visio 2007 cannot match the data. If conflicts like these happen, then the Refresh Conflicts window appears. The window provides options that help you resolve these conflicts.

To solve problems with deleted rows of data, follow these steps:

1.	In the Refresh Conflicts window, in the list of shapes, select the shapes to which you want to apply one of the following commands.
2.	Click one of the following: Delete Shape to delete only the selected shapes. Delete All Listed Shapes to delete all of the shapes with missing rows. Keep Shapes to keep only the selected shapes. Keep All Listed Shapes to keep all of the shapes with missing rows.

If you are having problems caused by data that can’t be uniquely identified, the Refresh Data command might not be able to match the correct row with the corresponding shape. Follow these steps to resolve the conflicts:

1.

In the Refresh Conflicts window, in the list of shapes, select the shapes to which you want to apply one of the following commands.

2.

Click one of the following: Select Matching Row to open the Select Matching Row dialog box, where you can determine which row should be associated with the shape in question. Ignore All Conflicts to keep the duplicate rows in the External Data window. Existing links to these rows are deleted. If you click Ignore All Conflicts, the rows with duplicate unique identifiers remain in the window, the original data is still stored in the Shape Data dialog box, and no links exist between the shape and the data.

Connecting to and Refreshing Access Data

To connect to Access data, it’s as simple as any other type of data you would want to import. Access is a program that was created to make large databases of information. Visio 2007 enables you to connect with Access to use the Access data in your Visio diagram. Follow these steps to help you connect your Visio diagram with Access data:

1.	On the Data menu, select Link Data to Shapes.
2.	This will open the Data Selector Wizard,. Choose the Microsoft Office Access database option.
3.	Click Next.
4.	Select the Access file you want to use.
5.	Click Next.
6.	Select the rows and columns you wish to include.
7.	Click Next.
8.	Click Finish.

If you need to refresh the Access data in your diagram, follow these easy steps:

1.	On the Data menu, click Refresh Data.
2.	In the Refresh Data dialog box, do one of the following: To refresh a single data source, select it in Data Source list, and then click Refresh. To refresh multiple data sources, hold down CTRL while you select data sources in the Data Source list, and then click Refresh. To refresh all data sources, click Refresh All.

To have Visio 2007 automatically refresh the data in your drawing, follow these steps:

1.	In the Data menu, click Refresh Data.
2.	In the Refresh Data dialog box, select one or more data sources.
3.	Select the Show This Dialog Box On File Open check box.
4.	Click Configure.
5.	In the Configure Refresh dialog box, under Automatic Refresh, select the Refresh Every check box, and then click the arrows to choose an interval. It is very important to remember that for the refresh to work correctly you must select the correct unique identifier, which is located on the right side of the Configure Refresh Dialog box.

Creating a Chart with the Organization Chart Wizard

Another way Visio 2007 simplifies your life and saves time is the Organization Chart Wizard. If you have data, Visio 2007 can put this data into an easy-to-comprehend chart. Follow these simple steps:

1.	In the Data menu, select Insert Data Solutions and click Organization Chart.
2.	When the Organization Chart Wizard window opens, choose where you want to create your organization chart from. If you choose Information That’s Already Stored In A File Or Database, then you can use files that have already been created. If you choose Information That I Enter Using The Wizard, you can use existing files, but you have more control and can enter new information.

If you chose Information That’s Already Stored In A File Or Database:

1.	Click Next.
2.	Choose where your organization information is stored.
3.	Click Next.
4.	Choose the file you wish to have in your chart, and specify the language you wish to have it in.
5.	Click Next.
6.	Specify the columns that contain information that defines the organization chart.
7.	Click Next.
8.	Choose the columns that you wish to display. In the list boxes, you can add or remove any columns.
9.	Click Next.
10.	Choose the columns from your data that you wish to have organization shapes added to and click Next. Now you can specify the amount of your organization to display in your chart. Or, you can let the Organization Chart Wizard organize it for you. You also have the choice of making the employee shapes hyperlinks or not and synchronizing employee shapes across the page or not.
11.	Click Finish.

If you chose Information That I Enter Using The Wizard, follow these steps:

1.	Click Next.
2.	Choose whether you want your information to come from Excel or from a delimited text file and click Next. If you chose Excel, then Excel opens; if you chose Delimited Text, you will see a dialog box prompting you to type over the sample text, and after you click OK, Notepad opens. Either way you have to enter the information you want, and then select File, Save and File, Exit.
3.	Click Next. Now you can specify the amount of your organization to display in your chart. Or, you can let the Organization Chart Wizard organize it for you. You also have the options of making the employee shapes hyperlinks or not and synchronizing employee shapes across the page or not.
4.	Click Next if you chose to specify the amount to display on each page, but if you choose to let the Wizard do it for you, click Finish. Now your chart will be displayed in your diagram.