Purchasing & Inventory - Creating Custom Reports

Overview

The Purchasing & Inventory Report Center provides a wide range of predefined reports. However, there is a way to create custom reports according to specific business needs.The custom reports feature provides ability to create customer-specific reports. The reports use SQL queries to obtain the required information.

There are five types of reports that can be created in Adaco.

• Simple report with a single predefined filter. For example, a report for one definite property.
• Report with a single nondefined filter. For example, a report for some property. In this case the property number will need to be provided before report generation.
• Report with a single non-defined filter but with a list of predefined values to choose from.
• Report with a single filter but with predefined list. The list is taken from the specified database. For example, a property name can be chosen from the list of all available properties.
• A report with multiple filters.

To Create a Custom Report

• Go to Report Centre > New Report

The Report Wizard will open.

• Click New > Standard Report > Next
• Provide a report Name and Description and click Next
• Click Custom Query
• Enter an SQL query and click Apply. The query will be executed and its result will be displayed. The parameter must be declared for the query to be valid
• Click Next
• Select columns to display in the report
• Provide grouping levels if required. There may be a need to have several fields in the same group
• Click Next
• Chose the layout of the report. Provide one of the following:
  • Columnar - Displays information in columns from left to right
  • Tabular - Uses a table view with information displayed from top to bottom
  • Justified - Justifies information on the page
  • Orientation - Select either Portrait or Landscape
  • Adjust the field width so that all the fields will fit on page - as described.
• Click Next
• Apply a style from the given list. The style can be adjusted later in the Report Designer
• Click Next
• Write a title - this will appear at the top of the page in the generated report
• Click Finish

Specifying Query Parameters

Once the report is created its parameters need to be configured so that it matches one of the five standard report types.

To Set Up the Report

• Go to the Report Designer, click anywhere in the blank space within it and go to Property Grid on the right hand side

• Go to the Data section and update the SQL query if required
• Update values in the Parameters section if required.

Further information on specific report parameters can be found below.

Simple Report for a Specified Property

This report will generate result according to the request for one predefined property.

Sample SQL Query

DECLARE @PropertyNumber INT =1

select * from Property where PropertyNumber = @PropertyNumber
where INT = property number

This sample report will look like this.
 

Fig.3 - Example custom report
 

Report with no Predefined Filter Value

In this report, a filter value has to be provided before report generation. In this example a property number is provided to generate the report.

Sample SQL Query
select * from Property where PropertyNumber = @PropertyNumber

Parameters
The following parameters should be set:

Fig.4 - Parameters for example report with no predefined filter value

• Type: Number (32 bit integer). This is a type of the value to be provided as a filtering parameter. In this example Property Number should be used
• Value: This is a value displayed by default. In this case is is 0
• Name: The name of the filter to be displayed.

This is how the selection page of the report will look.
 

Fig.5 -Selection page of the sample report with no predefined filter value

• Provide a property number and click Submit.

The following report will be generated.

Fig.6 - Example custom report with no predefined filter value

Report with Predefined Filter Values

There is a way to set up a report with choice of predefined parameters. This is useful if wanting to select some but not all of the available parameters.

Sample SQL Query

select * from Property where PropertyNumber = @PropertyNumber

Parameters
 

Fig.7 - Parameters for example report with predefined filter values
 

• Look-up settings - Static List. This will create a list of predefined parameters to choose from
• Look-up Value - Click the ... button and type the values to be used in the filter drop-down list
• Type - Number (32 bit integer). This is a type of the value to be provided as a filtering parameter. In this example Property Number is used
• Value - Must be set to 0
• Name - The name of the filter to be displayed

 

Fig.8 - Example created Property Number look-up values for report with predefined filter values

Only the Description field will be used in the filter drop-down list.

The selection page of the report will now look like this.
 

Fig.9 - Selection page of the example report with predefined filter values

• Choose a property from the drop‐down list and click Submit.

The report will look like this.

• Choose a property from the drop‐down list and click Submit.

​

• Choose a property and ensure the date to filter requisitions is created later that the date specified​
• Click Submit