gwreports - geekwright Reports

Concepts

Topic

Topics serve to group reports for menus and blocks. A topic is only visible to a user if there are one or more reports assigned to the topic that the user has the authority to view.

Related Elements

Report Topic - Name for the topic, used through out the system, including menus

Description - A description of the topic shown in tool tips and the header of a topic's menu

Report

A report is a set of SQL queries, parameter definitions, permissions and formatting instructions that together for a unit which can be invoked by a user to display data residing in MySQL tables. A user's authority to access a report is controlled by the CMS groups authorized to use the report by the module administrator.

Related Elements

Name - Name for the report, used through out the system, including menus

Authorized Groups - CMS groups authorized to view the report

Assign to Topic - Topic that a report is assigned to

Description - A description of the report shown in tool tips and when parameters are collected prior to running a report

Is Active - Allows a report to be turned on or off. A report will be available to run only when Active is Yes. When a report is first added, this is set to No, and set this way a report can be edited and tested, but it will not be available outside of the editing environment.

Section

A section is a part of a report definition that specifies a single SQL query and related formating instructions. When a report is first created, the initial query is used to create a section named the same as the containing report. This section has default values, and can be edited. Multiple sections are executed in order, and their output appended to the report. The order of the sections in a report can be changed using the Reorder Section function.

Related Elements

Section Name - Name for the section, used in editing, and optionally shown in the report.

SQL Query - A query to execute to retrieve data for the report section. This query will be executed in the context of the normal CMS database user and connection. To reference report parameters in the query use the name of the parameter in brackets, i.e. {name}. To reference the CMS database prefix use {$xpfx}. To reference the uid of the current user use {$xuid}.

Show Section Name  - if Yes, the section name will be show as a header at the top of the section in the report.

Section is Multirow - if Yes, the results of the query are shown as a row of column names, followed by rows of result data. If No, the results of the query are show in rows consisting of  the column name and column data, and this is repeated for each row of result data.

Supress display if empty - if No, a message will be presented in the report if the query did not return any data. If set to Yes, the entire section will be quietly omitted from the report.

Description - A description of the section shown in tool tips

Parameter

A parameter is a specific named input to a report that is obtained from the user before executing any SQL queries to extract data from MySQL. Insertion points for named parameters are specified in the SQL as the name surrounded with curled brackets, i.e. {name}.

Related Elements

Parameter Name - the name for this parameter, used to reference the parameter value in a section's SQL, and as the root of the name used in the form for collecting parameter's from the user. Parameter names starting with a '$' and the name 'rid' are reserved and should not be used.

Title to Display - the title displayed when this parameter is collected for or shown in summary on a report

Description - a description of the parameter shown in tool tips

Field Length - the maximum length of the form input used to collect the parameter

Number of Decimal Places - the number of decimal places for a decimal parameter

Required - if Yes, entry of the parameter is required. When a report is run, it will show a parameter entry form to the user if a required parameter has not been entered, even if the parameter has a default value defined.

Parameter Type - the type of parameter which controls how the parameter entry is displayed an processed. Valid types are:

  • text - a straight text value

  • liketext - a text value which will be surrounded by like characters before being passed to the query, i.e. %value%

  • date - a date value which will be converted to a MySQL unix_timestamp before being passed to the query. The input will be converted using the PHP strtotime() function.

  • integer - the input will be interpreted as an integer before being passed to the query.

  • decimal - the input will be interpreted as a decimal number rounded and formated to tne number of decimal places specified before being passed to the query.

  • yesno - the input is interpreted as a 1 (yes) or 0 (no) before being passed to the query.

Default Value - a default value for the parameter.

Column

A column is a named data as returned by an SQL query. A query returns a result set that may contain rows. Each row contains one of more named columns. Column formating instructions specific to a section are associated with the result data by the column name.

Related Elements

Column Name - the name of the column in the MySQL result set when the section query is executed. It may be convenient to use an alias when the select_expr for the column is a function, i.e. SELECT CONCAT(last_name,', ',first_name) AS full_name

Display Title - the title of the column shown in the report section header. If blank, the column name will be used.

Hide this column? - if Yes, the column will not be shown

Sum this column?- if Yes, the column values will be summed, and shown in summary at the end of the report section and at any column change breaks.

Break on column change? - if Yes, a break line will be inserted at any change in the column value from one row to the next. This break column will include a sub-total of any sum columns in the section.

Outline column? - if Yes,  the display of any column value which is equal to the column value of the preceding  row will be suppressed and replaced by a blank value.

Convert newlines? - if Yes,  any newline characters in the column value will be converted to HTML break tags.

Column is Unix Time? - if Yes, the value of the column will be treated as a MySQL unix_timestamp. By default this will be converted to a display value using the CMS formatTimestamp() function, but an alternative format can be specified in the format string.

sprintf() or date() format string - a format string to be used for this column, which will be interpreted in one of two ways:

If the column is unix time is yes, the format string and column value will be passed to the PHP date() function, and the return of that function will be shown in the report

Otherwise, the format string and column value will be passed to the PHP sprintf() function, and the return of that function will be shown in the report

HTML/CSS style for column - any value supplied here will be inserted as attributes in the HTML TD tag surrounding the column data in the report display.

Extended format - The extended format can be used to build any arbitrary string from the column data for a result row. Any column data can be referenced by the column name in {} brackets, i.e. {name}. The value of the the current column will be as otherwise specified in the definition, while other columns will be the value as returned in the result set. In addition to named column values, the term {$xurl} in the extended format will be replaced by the base CMS URL (i.e. XOOPS_URL.)



Previous | Contents | Next

Creative Commons License
gwreports - geekwright reports - Usage and Administration Guide by geekwright, LLC is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
Permissions beyond the scope of this license may be available at http://geekwright.com/.

Manual


Share

Share |


View mode: Standard | Mobile