Skip to content

Define Custom Report using User Query

image-20210615104943544

Step 1: Defining Report

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

image-20210615104957718

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

image-20210615105418363

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

image-20210615105439190

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

image-20210615105449586

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.

image-20210615105955246

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

image-20210615110032320

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

image-20210615110046141

· Select the Type as Custom Report.

image-20210615110056021

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

image-20210615110102822

· Once the Menu Design is completed, save it.

image-20210615110109721

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

image-20210615110119473

· 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

image-20210615110139486

· Clear Cache once the changes are made.

· Go to Utilities --> Clear Cache.

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

image-20210615110426745

· The final report appears like the following image.

image-20210615110434548