Working with SQL Queries Activity

 

 

 

Step

Action

1. 

Activity End Result

At the end of this activity you will have created a stand-alone report that uses two parameters. The parameters allow the report user to specify the product brand names they want to omit from the report, and by which field to order the records.

2. 

Create a new report template named query_param.

3. 

Begin by creating the first of two parameters: the order-by parameter.

 

In the Report Inspector, right-click the Parameters node.

4. 

Click the Add Parameter menu item.

5. 

Next, configure the new parameter in the Properties panel as follows:

 

- Name: order_clause

- Default Value Expression: “ORDER BY product.product_name”

- Parameter Class: java.lang.String

- Use as a prompt: Check this box

 

Enter "order_clause" into the Name field.

6. 

Click the Default Value Expression button.

7. 

Note: The expression editor opens.

8. 

Design an ORDER BY clause that, by default, orders query results alphabetically by product name.

 

Enter ""ORDER BY product.product_name"" into the field.

9. 

Click the OK button.

10. 

Next, create the brands parameter.

 

Right-click the Parameters node in the Report Inspector.

11. 

Click the Add Parameter menu item.

12. 

This parameter accepts one or more brand names from an input control. Configure it as follows:

 

- Name: brands

- Parameter Class: java.util.Collection

- Use as a prompt: Check this box

 

Enter "brands" into the Name field.

13. 

Click the Parameter Class drop-down button to activate the menu.

14. 

Click the java.util.Collection list item.

15. 

Note: Both parameters are configured and appear at the bottom of the Parameters list in the Report Inspector. The parameters can be referenced in a query.

16. 

Next, design a query to populate the report with data.

 

Click the Report query button, at the right of the Preview button.

17. 

Click the Query designer button.

18. 

Click the Schema drop-down button.

19. 

Click the public list item to display the names of the public schema's tables.

20. 

Double-click the product list item to add that table to the query.

21. 

Note: The product table appears on the query canvas.

22. 

Click the product menu at the top of the table.

23. 

Click the deselect all menu item.

24. 

Now, identify the fields that the report needs.

 

Click the brand_name option.

25. 

Click the product_name option.

26. 

Click the sku option.

27. 

Click the gross_weight option.

28. 

Click the OK button.

29. 

Note: The query appears in the main window, and the four field alias names appear in the bottom pane.

30. 

Next, modify the query to reference the two parameters.

 

Click in the Query field at the end of the FROM clause.

31. 

Press [Enter] to add a new line.

32. 

Create a WHERE clause that configures the query to return all records except those whose brand names are submitted by the user.

 

Enter "WHERE $X{NOTIN, brand_name, brands}" into the field.

33. 

Note: The WHERE clause restricts the query so it returns only records whose brand name is NOT among those submitted to the brands parameter through an input control.

34. 

Press [Enter] to add another new line.

35. 

Add a reference to the order_by parameter so the user can submit any well-formed SQL in a second input control and order the query results any way they want.

 

Enter "$P!{order_clause}" into the field.

36. 

Note: $P!{order_clause} is replaced by the SQL the user enters into the input control when the report runs.

37. 

Click the OK button.

38. 

Note: The query is configured to use both parameters. The report user will be prompted to order the information in the report and to specify any brand names to be excluded from the report.

39. 

Next, delete all bands but Column Header and Detail 1.

 

Click the Title band name.

40. 

Press the [Ctrl] key and click the Page Header tree item.

41. 

Also Ctrl-click the Column Footer, Page Footer, Summary, and Background bands.

42. 

Press the [Ctrl] key and click the Column Footer tree item.

43. 

Press the [Ctrl] key and click the Page Footer tree item.

44. 

Press the [Ctrl] key and click the Summary tree item.

45. 

Press the [Ctrl] key and click the Background tree item.

46. 

Right-click the Background band name.

47. 

Click the Delete Band menu item.

48. 

Note: All but two bands are deleted.

49. 

Now, set the remaining bands' heights.

 

Press the [Ctrl] key and click the Detail 1 band name.

50. 

Enter "30" into the Band height field.

51. 

Press [Enter] to commit the change.

52. 

Note: Both bands are 30 pixels tall.

53. 

Next, add fields to the report.

 

Expand the Fields node.

54. 

Click the product_brand_name tree item.

55. 

To select all four fields, press the [Shift] key and click the product_gross_weight field name.

56. 

Now, click on the product_brand_name field and drag all four into the Detail band.

57. 

Next, edit the four column headers to make them more user friendly.

 

In the Column Header band, double-click the product_brand_name field.

58. 

Enter "Company Name" into the Column Header field.

59. 

Press [Enter] to commit the change.

60. 

Change the remaining column headers to Product Name, SKU, and Gross Weight.

61. 

Note: The report is now formatted.

62. 

Preview the report to make sure it works correctly.

 

Click the Preview button.

63. 

Note: The order_clause parameter prompt appears.

64. 

The default display order you configured earlier is by company name.

 

Click the Use default button.

65. 

Note: The brands parameter prompt appears.

66. 

The brands parameter's Default Value Expression was left blank, so no brands are excluded from the report by default.

 

Click the Use default button.

67. 

Note: The report opens in the preview window.

Notice that the results are ordered alphabetically by company name as specified by the order_by parameter's default setting, and the first two company names are ADJ and Akron.

68. 

Click the Run again button, at the right of the Preview button.

69. 

For ordering, click the Use default button again.

70. 

This time exclude ADJ and Akron from the report.

 

Enter "ADJ, Akron" into the brands parameter field.

71. 

Click the OK button.

72. 

Note: The results are still ordered alphabetically by company name, but the ADJ and Akron companies are not among the brands listed.

 

The $X{NOTIN, brand_name, brands} syntax referenced in the query excludes all brand names entered into the brands input control.

73. 

Click the Run again button.

74. 

This time, order the results differently.

 

Enter "ORDER BY SKU" into the order_clause parameter field.

75. 

Click the OK button.

76. 

Exclude no companies.

 

Click the Use default button.

77. 

Note: Records in the report are ordered by SKU (lowest to highest).

78. 

Click the Run again button one last time.

79. 

Press the [Space] key to insert an empty space for the value of the order_clause parameter.

80. 

Click the OK button.

81. 

Click the Use default button to exclude no brands from the report.

82. 

Note: Entering a blank space for the order_by parameter displays the records in no particular order.

83. 

Congratulations!

 

You defined two parameters for a report, referenced them in the query, and tested the parameters.

End of Procedure.