{"id":1735,"date":"2011-04-17T13:16:45","date_gmt":"2011-04-17T07:46:45","guid":{"rendered":"http:\/\/sendyoursmiles.com\/articles\/report-creation-through-wizard"},"modified":"2014-08-21T08:01:21","modified_gmt":"2014-08-21T08:01:21","slug":"report-creation-through-wizard","status":"publish","type":"post","link":"https:\/\/erpschools.com\/erps\/articles\/report-creation-through-wizard","title":{"rendered":"Report Creation through wizard"},"content":{"rendered":"

Objective:<\/span> Create a report using wizard in Report Builder.<\/p>\n

Open Report Builder<\/p>\n

\"\"<\/p>\n

This is the first screen you will see. Select the first option “Use the Report Wizard” and click\u00a0OK.<\/p>\n

\"\"<\/p>\n

Click\u00a0Next<\/p>\n

\"\"<\/p>\n

Enter Report Title: erpschools_sample_report and then select “Tabular” option.<\/p>\n

As per name “Tabular” our output will be organized in a tabular way (rows and columns).<\/p>\n

You can select any option you want based on your requirement\/wish.<\/p>\n

Then click\u00a0NEXT<\/p>\n

\"\"<\/p>\n

Select the first option “SQL statement” and click\u00a0Next<\/p>\n

SQL statement: If you select this option you have to write the query directly.<\/p>\n

Express Query: If you select this option you have to\u00a0connect to the database and then select the tables\/columns you want to display in the report. Simply this is wizard for building the query.<\/p>\n

\"\"<\/p>\n

Enter the below SQL Query and then click\u00a0Next<\/p>\n

SELECT * FROM MTL_SYSTEM_ITEMS_B WHERE SEGMENT1 =:P_SEGMENT1<\/p>\n

In the above query: p_segment1 is called as bind parameter\/variable.<\/p>\n

Note: There are two types of parameters that we use in reports. Lexical Parameter and Bind Parameter<\/p>\n

Bind Parameter:<\/span> This is used to pass the values dynamically at run time.<\/p>\n

Examples: 1) If you want to see only one item from your inventory and you want to select that item at the time of running report. 2) If you want select only particular department employees and you want to select that department at runtime.<\/p>\n

Lexical Parameter:<\/span> This parameter is used to build the query dynamically.<\/p>\n

Examples: If you have two\u00a0users A, B will be running the report and if use A want to see only columns 1, 2, 3 where as User B want to see columns 2, 3, 4 in that case we build the query dynamically using lexical parameters.<\/p>\n

\"\"<\/p>\n

Here you have to enter the username, password of your\u00a0database. Usually in many development environments username and password will be “apps”.<\/p>\n

So enter apps\/apps@datbase and click\u00a0Connect<\/p>\n

\"\"<\/p>\n

Once you are successfully connected you will see the above message box.<\/p>\n

Action: Click\u00a0OK to Proceed<\/p>\n

\"\"<\/p>\n

Based on your Query above screen will display the Available Fields. In this case we will have all columns from mtl_system_items_b table. Select the columns you want to display in the report and use arrow buttons to move them to Displayed Fields.<\/p>\n

\"\"<\/p>\n

Here I have selected the three columns. You have to select the SEMENT1 column if you have any parameters associated with it, as we have one parameter with it I am selecting that field.<\/p>\n

click\u00a0Next<\/p>\n

\"\"<\/p>\n

In the above screen you have option to select any Totals\/Sum\/Averages if you want. Here i will proceed without selecting any.<\/p>\n

Click\u00a0Next<\/p>\n

\"\"<\/p>\n

In this screen you have the option to change the heading\/display column names in the report. Here i have changed the “Description” to “Item Description” and “SEGMENT1” to “Item Number”.<\/p>\n

click\u00a0Next<\/p>\n

\"\"<\/p>\n

Here you have the option to select any template if you have. I will go with “No Template” option<\/p>\n

click\u00a0Next<\/p>\n

\"\"<\/p>\n

Finally click\u00a0Finish<\/p>\n

\"\"<\/p>\n

It will prompt you with the “P Segment1” parameter. Enter some valid value and click green signal button to run the report.<\/p>\n

Note: To check valid parameters for this report run the below query and copy the output.<\/p>\n

SELECT SEGMEN1 FROM MTL_SYSTEM_ITEMS_B WHERE ROWNUM<2;<\/p>\n

\"\"<\/p>\n

Finally this is my report output.<\/p>\n

Click File — Save and enter your report name (sample_report.rdf to use concurrent program\u00a0registration document for your reference)<\/p>\n

Note:<\/span><\/p>\n