Handle scripts in data reports: practical examples
Scripts can be written for event handlers to configure the behavior of report controls, data, or bands. Scripts are executed when the corresponding event takes place and the report is generated. The dbForge tool enables you to add scripts in C#, Visual Basic .NET, and JScript .NET script languages in the Scripts editor.
To access the editor, switch to the Scripts view in Report Designer.
Note
The report for which you want to run the code must be bound to the table.
In the Scripts editor, each report element contains a specific set of events. You need to select the report element (1) and event (2) from the drop-down lists. Selecting the event inserts an automatically generated event handler’s template (3) into which you need to place your code. Upon loading the report in the Preview mode, the script is executed.
Work with scripts
To help you understand how to work with scripts in the dbForge tool, let’s walk through an example. We’ll start by creating a simple report that displays information about the bicycle total sales grouped by year, quarter, brand, and category. After we have the report structure in place, we’ll change the background and foreground colors of the cells using a script written in C#.
The guide describes the following:
For the example, we’ll use the following SELECT statement as a data set.
SELECT
YEAR(orders.order_date) AS year
,DATEPART(QUARTER, orders.order_date) AS quarter
,categories.category_name AS category
,brands.brand_name AS brand
,order_items.quantity * order_items.list_price AS order_total
FROM production.products
INNER JOIN production.brands
ON products.brand_id = brands.brand_id
INNER JOIN production.categories
ON products.category_id = categories.category_id
INNER JOIN sales.order_items
ON order_items.product_id = products.product_id
INNER JOIN sales.orders
ON order_items.order_id = orders.order_id
INNER JOIN sales.customers
ON orders.customer_id = customers.customer_id
INNER JOIN sales.staffs
ON orders.staff_id = staffs.staff_id
WHERE YEAR(orders.order_date) IN (2017, 2016)
AND brands.brand_name IN ('Trek', 'Surly')
AND categories.category_name IN ('Cyclocross Bicycles', 'Mountain Bikes', 'Electric Bikes');
Step 1: Create a simple report
1. Navigate to the File menu and select New > Data Report.
2. In the Data Report Wizard that opens, select a standard report type and click Next.
3. Choose a connection you want to use in your report and the Custom Query data type, and then, click Next.
4. On the Create a new query page, enter the SQL query whose columns will be used for the report, and then click Next.
5. Select the columns to be displayed in your report and click Next.
6. Specify the Tabular report layout and click Finish.
The report will open in the Report Designer. To preview the report, click the Preview view.
Step 2: Customize the report appearance using the script
Once we have the report structure set up, we can customize the fore- and background coloring of the cell using the script in C#.
Step 2.1: Change the background color of the cell
Let’s apply a green background color to the total sales that exceed 4999, and for the rest, let’s mark them with a yellow background color.
1. Click the Designer view to return to the Report Designer.
2. Select the element for which the script will work. In our case, it is the order_total field.
3. Click the Scripts view at the bottom.
4. In the Scripts editor that opens, choose the Before Print event from the drop-down list.
5. In the event handler’s template that opens, insert the following C# script to check the value of the total_order field and apply the green color to the specific cell (tableCell16 in our case) if the total order value meets the specified condition. Otherwise, the yellow color will be used.
private void tableCell16_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) {
tableCell16.BackColor = Convert.ToInt32(GetCurrentColumnValue("order_total")) < 4999 ? Color.Yellow: Color.Green;
}
- tableCell16 is the report element for which the script is executed.
- GetCurrentColumnValue(String) is the method that returns the current value of the specified column (field).
- order_total is the name of the column, to which values the condition will be applied.
The script uses a ternary (conditional) operator to assign the appropriate color based on the comparison result. For example, Color.Yellow: Color.Green means “use a yellow color if the condition is true, and green color otherwise.”
6. Optional: Click Validate to verify that the script is valid and errorless and can be executed in the report.
Note
To ensure proper configuration, verify the following options:
- Go to the View menu and select Properties.
- In the panel that opens, select the element from the dropdown list for which the script should work.
- Under Style Priority, clear the Use Background Color checkbox.
- In the Styles > Style, select the DataField option.
7. Click Preview to see the result. Upon the script execution, the order_total column will be highlighted with a green background color for values greater than 4999, and with a yellow background color for values less than 4999. This can be achieved by using the BeforePrint event and a C# script that checks the current value of the order_total column and sets the background color accordingly.
Step 2.2: Change the foreground color of the values in the column
Now, let’s modify the same report and change the foreground color of the total_order column.
1. Click the Designer view to return to the Report Designer.
2. Select the element that the script will work on. In our case, it is the order_total field.
3. Click the Scripts view at the bottom to open the Scripts editor.
4. In the editor, choose the Before Print event from the drop-down list and insert the following C# script in the event handler’s template that opens. The script will check the value of the total_order field and apply the green color to the value if the total order value is greater than 3999, the red color - if the value is less than 1500, and leave the default (black) color for the values that are between 1500 and 3999.
private void Detail_BeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e) {
XRTableCell[] cells = new XRTableCell[] { tableCell16 };
System.Decimal order_total = (System.Decimal)GetCurrentColumnValue("order_total") ;
if (order_total < 1500)
ChangeCellsColor (cells, Color.Red) ;
else if (order_total > 3999)
ChangeCellsColor(cells, Color.Green) ;
else
ChangeCellsColor(cells, Color.Black);
}
void ChangeCellsColor(XRTableCell[] cells, Color color) {
int count = cells.Length;
for (int i = 0; i < count; i++)
cells[i].ForeColor = color;
}
- XRTableCell refers to a cell in the table.
- tableCell16 is the report element whose foreground color will be changed according to the specified condition. Here you list the cell or range of cells to format.
- GetCurrentColumnValue(String) is the method that returns the current value of the specified column (field).
- order_total is the name of the column to which the condition will be applied.
- if…else is the condition upon which the color change will be applied.
- ChangeCellsColor() is the function that defines an array of cells and a color name.
6. Optional: Click Validate to verify that the script is valid and errorless and can be executed in the report.
7. Click the Preview view to see the result. Upon the script execution, the foreground color of the total_order cell will change based on the specified conditions. If the total order value is less than 1500, the cell’s foreground color will be red. If the total order value is greater than 3999, the foreground color will be green. For values between 1500 and 3999, the default black color will be used.
Want to Find out More?
Overview
Request a demo
