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

- 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.
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 = @PropertyNumberwhere 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.

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.

This is a report where all values available for a parameter in the database are used.
For example, choosing a property from all available properties and then display only the top 15 requisitions for it.
Sample SQL Query
select TOP 15 * from RequisitionDetail where PropertyNumber = @PropertyNumber
select * from property
Parameters

Fig.11 - Parameters for example report with all available filter values taken from the database
Data Member - Provide dataset table for the look-up
Data Source - Provide a dataset name.
Display Member - Provide a display column
Value Member - Provide a value column
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
The selection page of this example report will look like this.

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

Report with Multiple Filters
This is a way to create a report with multiple filters. The SPLITSTRING_RETURN_TABLE stored procedure should be used in this case. For example, wanting to create a report for all the requisitions in a specified property that were created not later than the date specified. To achieve this, results by both property name and creation date must be filtered.
Sample SQL Query
select * from Requisition R
INNER JOIN SPLITSTRING_RETURN_TABLE(@PropertyNumber,',') SP ON R.PropertyNumber =
SP.ID
WHERE CreatedDateTime > @CreatedDate
select PropertyNumber, PropertyName from Property
select P.PropertyNumber AS PropertyNumber, P.PropertyName AS PropertyName from Property P
Parameters
In this example, two filters will be added: Property Number and Created Date.
Property Number

Look–Up Settings - Dynamic List. This will create a list of dynamic parameters taken from specified dataset
Data Member - Provide dataset table for the look-up
Data Source - Provide a dataset name.
Display Member - Provide a display column
Value Member - Provide a value column
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 - set to 8 to perform data check
Name - The name of the filter to be displayed
Create Date

Value - This is default value to be displayed. Can be any valid date.
Name - The name of the filter to be displayed.
The selection page of this example report will look like this.

- Choose a property and ensure the date to filter requisitions is created later that the date specified
- Click Submit
The report will look like this.

Fig.17 - Example custom report with multiple filters
Comments
0 comments
Please sign in to leave a comment.