Define Custom Report using User Query

Step 1: Defining Report

· Go to Utilities in Menu bar and select Define Custom Report to find the following tab.

· In this example, we take SampleReport and Sample Report as the Report Name and Report Menu respectively.

For the Report name, the typical syntax to be used is without any special characters including space. Ideally, the first letter is a Capital.

· For Report Menu, since this will be displayed, the proper name can be provided with space between words.

Example: | Serial No. | Report Name | Report Menu | | ---------- | ---------------- | ------------------ | | 1 | ResourceLoad | Resource Load | | 2 | ForecastAccuracy | Forecast Accuracy |

· User can also give Module Name and Data Grid Name. It will help the user to generate a report by right-clicking on the Data Grid Name.

· Save the data after entry.

· Go to the Custom Report tab on the same page to enter the data that enables you to define the data required in the report. Click on Add icon

· Tab name can be entered arbitrarily. Here we give Report Summary as the Tab name.

· Under one report, the user can define multiple tabs. Each tab will have appeared in the Report with the unique name given.

· If the user defines the parameters, they will be applied to all the tabs.

· There are two ways to enter the query.

1. User Query - We manually enter the query using SQL commands.

2. Stored Procedure - We generate the procedure in SQL and then just enter the name of the procedure in the tool.

· In this example, we select User Query. Click on Generate to define SQL Query.

· Inner Join clause is used to combine selected headers of Work Order Plan and Item Master.

· Query should be entered in a single line.

SQL Query: 
  Select a.PlantId, a.WorkOrderId, a.Item, b.ItemDesc, b.ItemType,  b.Sellable,a.RoutingId, a.BucketDate, a.OrderStatus 
  from WorkOrderPlan a inner join ItemMaster b on b.PlantId=a.PlantId and b.Item=a.Item

· Please refer to the image below for SQL Query.

· Save and Add the data entered.

Step 2: Attaching Report to Menu

· Go to Utilities and then to Custom Menu Design to find the following page. Click on the Edit icon.

· This page allows users to design the menu bar.

· Only Text and Module box are active since Type is already defined.

· Click the Edit icon and fill the Text field with the proper name which appears in the Menu bar.

· In this example, we enter Test Report as Text field.

· Select Planning and Scheduling as the Module Name.

· Right click on the Test Report to add the child. The child is the one that drops down once when we click on the Test Report.

· Select the Type as Custom Report.

· Select the Program from the dropdown list which is predefined.

· Once the Menu Design is completed, save it.

### Step 3: User Authorization

· The user authorization screen helps in providing role-based security access to the screens.

· Go to File in the Menu bar and to User Authorization to find the following page.

· Out of multiple tabs, select Role Info.

· Click add icon and Select Attribute which is predefined. Here, the attribute is Sample Report.

· Multiple privileges are given as follows. Tick the checkbox as per requirement.

1. Add Flag: allows to add of new records

2. Edit Flag: Ability to edit records

3. Delete Flag: Ability to delete records

4. View Flag: Ability only to view

· Clear Cache once the changes are made.

· Go to Utilities --> Clear Cache.

How to Add Parameters in the Report (Stored Procedure)

· Parameters help the user to specify the list of items or list of resources or start and end time.

· These values will be expected from the user at the time of generating the report.

· This will filter the number of records generated in the report.

· User is required to first create a report to add parameters.

Step 1: Define Procedure

· Modification has to be done in the stored procedure since we use a report which is defined in the stored procedure.

· Defining Report remains same as followed in this Example

Step 2: Define Parameter(s)

· Parameters can be defined on the same page of Define Custom Report under Parameter List. Click add.

· Define the parameter name and select the data type.

· Appropriate data type has to be de-selected according to the parameter.

· In this example we give the Parameter name as Item and select string as Data type.

· Multi-select is used to select more than 1 Item during report generation. Here we select False since we select one Item at a time.

· Click on Generate to define the SQL Query.

· The Query allows the user to get the report of only one distinct Item in the WorkOrderPlan list.

· Please follow the Query.

· The Query helps the user to select one distinct Item during report generation.

· Attaching Report to Menu and User Authorization remains the same as followed in this Example

· Clear Cache once the changes are made.

Step 3: View Report

· Find the Test Report in Menu Bar and click SampleReport.

· The following allows the user to specify an Item and get a report of the distinct Item.

· Click the Item dropdown to get multiple items, out of which select one arbitrarily and click ok.

· Generate a report to get all the information about one distinct Item as shown below.

· To view the report, go to Menu bar --> Test Report --> Sample Report.

· The final report appears like the following image.