It can be useful to have a count of how many records are contained in a report. In grouped or summary reports, you can display a count of how many records are in each group. Or, you can add a line number to each record to make it easier to refer to each one. This article explains, step-by-step, how to add counts and line numbers to your report.
Activereports 7 Serial Number
You can number items in your report. For example, in a Sales by product report, you might want "1" to precede the first item in a product group, "2" to precede the second item, and so on. When the next product group begins, the count starts over, and "1" precedes the first item.
You can also write an expression to round a value to a multiple that you specify, similar to the MRound function in Excel. Multiply the value by a factor that creates an integer, round the number, and then divide by the same factor. For example, to round 1.3 to the nearest multiple of .2 (1.4), use the following expression:
You can combine functions in an expression to customize the format. The following expression changes the format of a date in the form month-day-year to month-week-week number. For example, 12/23/2009 to December Week 3:
The CDate function converts the value to a date. The Now function returns a date value containing the current date and time according to your system. DateDiff returns a Long value specifying the number of time intervals between two Date values.
The Regex functions from the .NET Framework System.Text.RegularExpressions are useful for changing the format of existing strings, for example, formatting a telephone number. The following expression uses the Replace function to change the format of a ten-digit telephone number in a field from "nnn-nnn-nnnn" to "(nnn) nnn-nnnn":
By specifying a key field, you can use the LookupSet function to retrieve a set of values from a dataset for a one-to-many relationship. For example, a person can have multiple telephone numbers. In the following example, assume the dataset PhoneList contains a person identifier and a telephone number in each row. LookupSet returns an array of values. The following expression combines the return values into a single string and displays the list of telephone numbers for the person specified by ContactID:
Test the value of the PhoneNumber field and return "No Value" if it is null (Nothing in Visual Basic); otherwise return the phone number value. This expression can be used to control the value of a text box in a report item.
The RowNumber function, when used in a text box within a data region, displays the row number for each instance of the text box in which the expression appears. This function can be useful to number rows in a table. It can also be useful for more complex tasks, such as providing page breaks based on number of rows. For more information, see Page Breaks in this topic.
The scope you specify for RowNumber controls when renumbering begins. The Nothing keyword indicates that the function will start counting at the first row in the outermost data region. To start counting within nested data regions, use the name of the data region. To start counting within a group, use the name of the group.
In some reports, you may want to place a page break at the end of a specified number of rows instead of, or in addition to, on groups or report items. To do this, create a group that contains the groups or detail records you want, add a page break to the group, and then add a group expression to group by a specified number of rows.
The following expression, when placed in the group expression, assigns a number to each set of 25 rows. When a page break is defined for the group, this expression results in a page break every 25 rows.
At each step of the SQL execution plan, the database tracks statistics by performance metrics such as elapsed time, CPU time, number of reads and writes, and I/O wait time. These metrics are available in a graphical and interactive report called the SQL monitor active report.
Every monitored database operation has an entry in the V$SQL_MONITOR view. This entry tracks key performance metrics collected for the execution, including the elapsed time, CPU time, number of reads and writes, I/O wait time, and various other wait times. The V$SQL_PLAN_MONITOR view includes monitoring statistics for each operation in the execution plan of the SQL statement being monitored. You can access reports by using DBMS_SQL_MONITOR.REPORT_SQL_MONITOR, Oracle Enterprise Manager Cloud Control (Cloud Control).
Statistics at each step of the execution plan are tracked by key performance metrics, including elapsed time, CPU time, number of reads and writes, I/O wait time, and various other wait times. These metrics enable DBAs to analyze SQL execution in depth and decide on the most appropriate tuning strategies for monitored SQL statements.
Optionally, you can specify the session ID and session serial number in which to start the database operations. Thus, one database session can start a database operation defined in a different database session.
To begin the operation in a different session, specify the combination of session_id and serial_num. The BEGIN_OPERATION function returns the database operation execution ID. If dbop_exec_id is null, then the database generates a unique value.
You want to ensure that the preceding query does not consume excessive resources. While the statement executes, you want to determine basic statistics about the database operation, such as the level of parallelism, the total database time, and number of I/O requests. 2ff7e9595c
Comments