Create Custom Reports in Sourcing and Contract Management Version

Contents

Contents of this topic:

Links to related topics:

Search Contracts Online Doc
Spend Types
Current Release Notes

Overview

Customized reports offer flexibility in deciding what data to include in a report and how to display the data. In addition to using standard reports defined by , you can create and run your own custom reports. You can also edit the report definitions provided, and tailor them to your needs. When you create your own reports, you can:

Access to reports

Custom reports are fully integrated with the Sourcing and Contract Management security settings. A user's ability to create, run, and/or view reports depends on whether a Report Profile is assigned to the Sourcing and Contract Management User ID; and if so, which Report Profile. A Report Profile limits the reports that a user can access, so that not all users at a site may be authorized for all reports. A site's Sourcing and Contract Management system administrator can permit or restrict viewing and / or report creation among users at a site. If you need access to custom reports, see your site's Sourcing and Contract Management system administrator.

Reports, including Sourcing and Contract Management reports, are also subject to a user's Data Profile. You can only run reports on data that you are authorized to work with.

Report objects

Sourcing and Contract Management reports are based on report objects. A report object is a logical view into the database. Report objects combine related data from database tables. Each report definition uses a report object to identify the data for the report. Report objects have associated fields that you can select when you create a new report definition.

To open the custom reports feature,

From Menu next to any report definition on the list, you can print a report, schedule a report to run, view reports that have run, edit the report definition, delete the report definition, and more. Clicking New lets you create a new report definition.

See Setting up scheduled jobs for details on how to use the job scheduler to run your reports on specific days/times.

To create a new report definition,

  1. From the Sourcing and Contract Management main Contents, select Reports > My Report Definitions.
  2. Click New. The Create New Report Definition panel appears (Figure 1).

Figure 1 - Create New Report Definition Title

  1. Enter a title for the report in the Report Title field.
  2. Click the Report Object prompt (...) to select a Report Object.
    (A Report Object is a view into the database -- it contains data from various related database tables.)
    The list of Report Objects appears. Use Position To (or sort the columns) to find the Report Object that you need. Figure 2 shows several Report Objects.
  3. Click Select next to the Report Object that you wish to use.

Figure 2 - Selecting a Report Object - Report Objects for Sourcing and Contract Management

  1. If you wish to make your report available to other users at your site, select the Shared Report box.

    Hint
    You may wish to leave your report unshared while you are working on it. When you have the report definition finished, you can edit it to select the Shared Report box.

  2. If you wish to make the report available for individual departments to run, select Is a Departmental.
  3. Click Next.
    The Report Definition edit panel appears along with the Select Report Fields list (Figure 3).
On the top of the Report Definition edit panel,
- The General tab contains the Report Title and the Shared Report selector, the owner of the report definition and the Report Object being used.
- The Advanced tab lets you define your report's output type.
- The Comments tab is a place for you to annotate your report.
On the bottom of the Report Definition edit panel, the Select Report Fields list contains all the fields that you may include in your report.

Figure 3 - Selecting Report Fields

  1. Click in the box next to a field to select the field. If many fields are available, you may need to scroll down (or press Next Page) to see all the fields.
  1. Click Next.
    Sourcing and Contract Management displays the Report Definition panel with the Report Fields edit panel at the bottom (Figure 4).

    Note: If you need to add more fields to your report, click the Select Fields button on the top of the report. Sourcing and Contract Management again displays fields for you to select.

Figure 4 - Report Fields Panel for Ordering and Configuring Fields

  1. Format your fields for the report.
    You can change the labels that actually print or display on your report. In the previous example, we have defined a field called: "Contract No," but this field can print as "Contract Number," "Contract #," etc.

    - To change the field label, click Menu > Edit next to the report field, or click the edit icon.
    A panel appears (Figure 5).

Figure 5 - Formatting a Report Field

You can enter a new column heading label and column width, specify a font and font size, and other options.
- Click Help for details on the values you can enter.
- Click Save to save your work.
- Click Submit when you are finished.
Sourcing and Contract Management closes the window and applies the designated formatting to the field. The changes you made take effect during the next report run.

  1. On the Report Fields list (Figure 4), specify the order of the columns in your report, fields that are filters, sortable fields, the sort order, columns to be totaled and grouped, and for fields that are grouped, whether you want a page break after the group's data.

Figure 6 - Selectable Options for This Field Will...

Use the values in the drop box to identify the classification or grouping fields in your report, and the fields that you want to total.

For example, suppose you are creating a report that shows how many contracts you have with each of your manufacturers, and who is responsible for the contracts. The data you have in mind for the report looks like Figure 7:

Figure 7 - A Potential Layout for a Report

You want data by manufacturer, and by contract number and name. You also want the total number of contracts for each manufacturer. Your report will have fields for the create date of each contract and the user who created it, the last import date of the contract, and any related contracts. Your grouping field is Mfr.
Selections shown in Figure 8 are:

Begin a new heading or totaling group - Select this value when your field is a grouping or classification field. In Figure 8, you would select this value for Mfr. A field with this value selected should not have a Sorting Order assigned.

Prints with previous group - Select this value if the field belongs with another field. For example (Figure 8), you would select this value for Mfr Name.

Summarize into 1 detail line - This value "collapses" several lines on your report into one line. It is used to create a summary report line.

Print a total - This value identifies a field that you wish to total.

Not print on report - If you do not want a field to actually print on the report (but you do want the report to use data from the field), select this value.

New Page After Group - Select this field to put a page break after each group that you have identified. In the example (Figure 8), you may want to put a page break after the data for each manufacturer, if there are a lot of contracts.

For the report that we defined in Figure 1, the Report Definition, so far, looks like Figure 8:

Figure 8 - Report Fields Defined for Report in Figure 1

  1. Click Selection Builder to specify values for the Selection field(s).
    If your report has selectable fields, the Selection Builder (Figure 9) shows each selectable field that you can specify for the report. Entering a value for a selectable field helps you refine your report to capture more specific data.

Figure 9 - Selection Builder

You can also enter operators (from the list below). Hold the mouse over the box to the immediate left of the selection value (Figure 8, purple arrow.). A question mark appears. Click on the question mark repeatedly to locate and set the operator as you wish. The operators are:

GT - greater than
LT - less than
EQ - equal to
NE - not equal to
GE - greater than or equal to
LE - less than or equal to
Like - match a set of characters. For example, if your site has Clinic A, Clinic B, Clinic C, etc., specifying "clinic" with like will select all the clinics.
You can enter the characters that you want to match directly in the box, or click the arrow to select a value (if there is one) that you want to match. Like is not case sensitive, so it will match a character to both upper- and lower-case occurrences of the same character.
Range - a range of values
List - lists possible values

- If you wish to compare the field to another field, choose an operator, and click Show on the Fields for Comparison box. A list of selectable comparison fields appears.

- Once you set up one or more selection values, you can lock them so that the report will always run for the values you specified. Click on the second box to the left of the selection value to lock or unlock a value (Figure 9, red arrow.)

- To let users specify a value for the selection field when they run the report, leave the selection fields blank.
- Click Help on the Selection Builder for detailed information about its fields, how to use operators, and create comparisons.
- Click Submit when you are finished entering selection field values.

The Report Definition panel reappears.

  1. Click the Advanced tab (Figure 10) to specify the output form of your report.

Figure 10 - Report Definition Advanced Panel

  1. When you are finished setting up your report definition, click Submit. The list of reports (My Report Definitions) appears.
  2. Click Refresh to see your report definition on the list.

To run and view a report from a report definition,

  1. From the Sourcing and Contract Management main Contents, select Reports > My Report Definitions.
    If you are running a system-defined report, select Reports > Standard Report Definitions.

  2. Locate the report that you wish to run.

  3. Next to the report of interest, click Menu > Print Report.
    The Selection Parameters panel appears.

  4. Enter values for the report data on the Selection Parameters panel, or accept the default values.
    For example, if a Year or Period selection field appears, enter the year or period that you wish to run the report for.

  5. Click Submit. A message informs you that the report has been submitted.
    Some reports may take a few seconds or minutes to run. Sourcing and Contract Management updates you on the progress of your report.
    The report is saved in My Archived Reports, but you can also view it immediately from My Report Definitions.

  6. On the My Report Definitions list, next to the report you have just run, select Completed Reports.
    Sourcing and Contract Management displays a list containing each run of the report. If you have just run the report for the first time, the list will have one entry.

    If your report has not finished running, you see messages that update the status of your report.
    - Click Refresh to view the changing status messages as the report runs.

    Multiple runs of the report are listed chronologically.
    - You can delete any run of the report by clicking Menu > Delete.
    - Spreadsheet reports contain a zero in the Page Count column. Zero is normal for spreadsheets.
    - If a zero occurs in the Page Count column for a PDF or other non-spreadsheet report, then no pages were output, and a problem with the report exists.

    When a report contains errors or problems, and cannot run, a problem message also appears.
    - Click the message to read the details of the problem.
    If you are not getting the expected results for a report definition created at your site, you may need to make changes to the report definition.

  7. Select View Report. An online version of your report appears. By default, unless you have specified another file type, Sourcing and Contract Management displays the report as a .pdf file in Adobe Acrobat Reader.

  8. Select the printer icon, or File > Print to print the report.

To view reports that have run,

  1. From the main Contents, select Reports > My Archived Reports.
  2. Locate the report that you wish to view.
  3. Next to the report, select View Report.

To edit a report definition,

  1. From the main Contents, select Reports > My Report Definitions or Standard Report Definitions.
  2. Locate the report that you wish to edit.
  1. Click Save periodically while you are editing to save your changes.
  2. When you are finished editing your report, click Submit. The system displays the My Report Definitions list.
    Note: If you have edited a standard report definition, the system adds the copy of the report definition to the My Report Definitions list, and not to the Standard Report Definitions list.

    From the My Report Definitions List, you can run your report.

To change the format of report fields,

  1. Make sure that you are viewing the Report Definition panel for the report that you wish to format.
    The Report Fields panel is at the bottom.
  2. Locate the field that you wish to format.
  3. Click the pencil icon or select Menu > Edit next to the field.
    The Report Field Definition panel appears (Figure 5).
  4. Change the values in the Report Field Definition panel to specify the format that you wish.
  5. When you are finished making changes, click Submit.
    The changes you made take effect during the next report run.

To output reports in different file types,

  1. Make sure that you are viewing the Report Definition panel for the report.
  2. Click the Advanced tab on the header panel.
    The Report Definition Advanced panel appears (Figure 10).
  3. Click the down arrow in the Output Format Type field. Several output file types appear.
  4. Click on a file type to select it:
  1. Click the Data Only box if you want only data on your report, and not column names.
  1. Click Save. The changes you made take effect during the next report run.
  2. Click Submit to return to My Report Definitions.

To add comments to a report,

  1. Make sure that you are viewing the Report Definition panel for the report that you wish to format.
  2. Click the Comments tab on the header panel. The Comment field appears.
  3. Enter comments in the Comments field. When you are finished, click Submit.
    The system saves the comments and returns you to My Report Definitions.

To remove the default Current Settings for the report,

  1. Make sure that you are viewing the Report Definition panel for the report that you wish to format.
  2. Click the Advanced tab on the header panel.
  1. Click on the Use Current Settings Organization check box to clear the box. Future reports will include all the information that you are able to access as specified in your data profile.

Using Record Count

The Record Count field is available for every Report Object. This field records the number of summarized records; that is, the number of row data records that would occur on the report if the rows were not collapsed and summarized.

To use these fields, edit the report definition and click Select Fields.

Scroll the list to locate and select Record Count. Click Save then Submit to return to the report definition. When you refresh the report definition (by clicking Refresh), the Record Count field appears.

Output a Report as an Excel Spreadsheet

If you wish to perform detailed analysis with a report, a useful strategy is to output the report as an Excel spreadsheet, and then use Excel's tools, such as filters (to filter and rank column data) and formulas (to manipulate mathematical data).

To output a report as an Excel spreadsheet,

  1. On the My Report Definitions list, click the edit icon or Menu > Edit next to the report.
    The Report Edit panel appears.
  2. Select the Advanced tab.
  3. Click the down arrow in the Output Format Type field and select Excel Spreadsheet (Figure 11).

Figure 11 - Setting the Output Format Type for a Summary Report to Excel

  1. Save and Submit the report.
    The list of report definitions appears.
  2. Next to the report definition on the list, select Menu > Print Report to output the printed report to Excel.

When you open the printed report to view it, the Microsoft Windows File Download box appears.

Figure 12 is the Excel spreadsheet output for the report defined in Figure 11. Notice that you can select the Excel AutoFilter capability from the Data menu. AutoFilter inserts a filter selection button (indicated by the red arrow) at the top of each data column.

Figure 12 - Example of Report Output in Excel with Excel AutoFilter Turned On

Click the button to view and select a filter for the column. For example, in the Contract column (Figure 13), you might wish to sort the contracts in descending order. When you select the filter, Excel displays the data filtered as you specified.

Figure 13 - Excel Output for a Report -- Selecting a Filter for the Contract Column