Create Custom Reports in Sourcing and Contract Management Version

Contents

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:

• Include data elements that are not found on any of the predefined reports.
• Group fields and calculate totals.
• Create a filter to select particular data to include in the report.
• Arrange report columns in any order that you wish.
• Change the font (text style) and size of any field in the report.
• Output reports in different file formats for import into applications such as Microsoft Excel.
• Include comments with stored report definitions.
• Share reports that you create with other users.
• Schedule your reports to run automatically on specified days/times.

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 the Sourcing and Contract Management main Contents, select Reports > Standard Report Definitions.
  The list of reports appears. If there are no reports, No Data appears.

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

3. Enter a title for the report in the Report Title field.
4. 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.
5. 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

6. 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.

7. If you wish to make the report available for individual departments to run, select Is a Departmental.
8. 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

9. 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.
   • If you need to add or remove a field later on, you can come back to the list.
     Look in the upper right of the list -- next to Quick Report -- to see the number of pages.
   • Use Next Page and Prev Page to view more fields.
   • Sourcing and Contract Management brings fields into the Report Definition in the order that you select them (but you can change the order, as discussed below).
   • Click Save periodically to save your selections.

10. 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

11. 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.

12. 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.

• Ordering the report columns
  Column Order tells Sourcing and Contract Management how to order the columns in a report. A whole number appears in the Column Order space next to each Field Name. The number specifies the column placement in relation to the other columns (left to right) in the report. The columns for the report are listed by tens (10, 20, 30, etc.) instead of by ones (1, 2, 3, etc.). This numbering scheme lets you reorder columns without having to renumber all of them. For instance, you may want the second field in the Field Name list (Figure 4) to become the fourth column in the report. In this case, assign the second field a column order between 50 and 60; for example, 55.
  - Click Save periodically to save your work.
  - Click Refresh (after clicking Save) to see the fields rearranged in the order you specified. (If you open the Report Definition again for editing, the order sequence interval will be 10.)
  - Click Print Preview at any time while designing a report to see how the columns are going to look.

• Identifying a field as a filter
  You may wish to create a report containing data that are specific for a manufacturer, vendor, or some other criterion -- in other words -- to set up a filter for users of the report. To designate a field on your report as a filter, check the box under Use as Selection Criteria for the field (Figure 4).
  When users run the report, Sourcing and Contract Management prompts them for a value for the filter field(s). For example, if you check the Use as Selection Criteria box next to Contract Owner Name (red arrow in (Figure 4), Sourcing and Contract Management prompts for the contract owner(s) before it runs the report.

• Specifying sortable columns
  You may need to sort the report by the values in one or more of its columns. To designate a column as sortable, select a rank from the drop box under Order By (Figure 4). If your report should sort by only one column, under Order By, select 1st next to the field name.
  
  For example, if your report contains dates, you could designate the date field as a sort field. Users could then sort the report information in ascending order, with the earliest date first; or in descending order, starting with the latest date. Similarly, it may be logical to sort by manufacturer, contract owner, vendor, or other fields. You may wish to use multiple sort fields; for example, if you are writing a report for imported contracts, the report could sort first by last import date (1st), and then by contract number within each date (2nd). If you select a field as a grouping field (see the discussion of grouping fields), do not assign it a sort order.

• Descending
  Select the Descending box (Figure 4) if you want the sortable report field to sort in descending order. (The default is ascending order.)

• This field will... (Produces column totals and groups)
  This field will... (Figure 4) contains selections that group data and calculate column totals. For a column that contains numeric data -- data that you can add and subtract -- Sourcing and Contract Management generates a total. For non-numeric data, Sourcing and Contract Management generates a count. Figure 6 displays the values in the This field will... drop box.

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.

• Important: In order to get a page break, groups must contain at least one totaling field.
  If you do not have any numeric fields on your report to total, use Record Count, which gives a count of the data records.

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

13. 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.

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

Figure 10 - Report Definition Advanced Panel

• Select Use Current Settings Organization to use data associated with the organization under which you have logged in. (The organization appears in your Current Settings when you log in.) If the box is not checked, Sourcing and Contract Management refers to your data profile to determine what organization to use in reports.
• Select the Output Format Type.
  Click the down arrow to choose the output format for your report. The choices are:

  Adobe PDF Document (default)
  Word Document
  Excel Spreadsheet
  Comma Separated Values
  Pipe Delimited
  Tab Delimited
  Text (creates a file as ACSII text)

  Note: The format output for Pipe Delimited and Tab Delimited reports is .txt.

• Data Only (for CSV & Excel output only, when selected, causes Excel- and .csv-formatted reports to only show data, not column headings or page breaks.
  - Click Help on the Report Definition panel for details on this field.
  

15. When you are finished setting up your report definition, click Submit. The list of reports (My Report Definitions) appears.
16. 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.

• To edit a report definition in My Report Definitions: Next to the report title, click the pencil icon or select Menu > Edit.
  The Report Definition and Report Fields panel appears (Figure 4).
  
  
• To edit a report definition: Next to the report title, click Menu > Copy. (You can only edit copies of Standard Report Definitions.)
  The Create New Report Definition panel appears (Figure 1).
  - Rename the report if you wish.
  - Click Next.
  - The Report Definition and Report Fields panel appears (Figure 4).
  

• To add more fields to your report:
  - Click Select Fields. The system displays the fields associated with the Report Object.
  - Click the boxes under Select to add new fields to your report.
  - When you are finished, click Submit.
  The system displays the Report Definition panel again.
  - Click Refresh to see the fields you added.
  You can continue editing your report definition using these panels.

• To change report field characteristics:
  Follow the instructions for the Report Fields panel if you wish to change any general characteristics of your report.

• To identify selection fields, see the discussion on using the selection builder.
• To change the appearance of report fields, see the section on changing the appearance of report fields.
• To output the report in a different format, see the section on outputting in a different file type.
• To add comments to the report, see the discussion of adding comments.

3. Click Save periodically while you are editing to save your changes.
4. 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:

• Acrobat PDF Document - Outputs a report as an Adobe Acrobat .pdf file. This format is the default.
  Adobe Acrobat Reader displays reports.
• Word Document - Outputs reports as Microsoft Word .doc files.
  You can edit reports in this format with Microsoft Word.
• Excel Spreadsheet - Outputs reports as Microsoft Excel Spreadsheet (.xls ) files.
  You edit reports in this format with Excel.
• Comma Separated Values - Outputs reports as .csv files.
  You can use Excel to read this file type as a spreadsheet.
• Text (creates a file as ACSII text - .txt).
• Pipe-delimited - Outputs a file with pipe-delimited fields.
• Tab-delimited - Outputs a file with tab-delimited fields.
  

5. Click the Data Only box if you want only data on your report, and not column names.

• Click Help to view details on this field.

6. Click Save. The changes you made take effect during the next report run.
7. 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.

• On the Advanced tab, look at the Use Current Settings Organization check box. Reports that you generate, by default, use data associated with the Organization, Department, and Asset Location specified in the Current Settings (above the main Contents).
• If your data profile gives you access to a wider range of data, you can clear the Use Current Settings Organization check box.

3. 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

4. Save and Submit the report.
   The list of report definitions appears.
5. 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.

• Click Save to save the Excel report to a file folder on your network.
• Click Open to open the report in Excel. (You can use the Excel File > Save menu to save the report later, if you wish.)
  

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

Copyright © 2023 by Premier Inc. All rights reserved.