Let's go through the processes to create a table report (rows and columns). You should already know how to add a category and a report. If you don't then view the guide for Creating Categories and Reports. To create a table report, find the Category or Report under which you wish to create a report and right-click. Then choose the ‘Add Report’ option.
Now you will see a 'New Report' with few attributes. Enter a logical name for the report. Find a suitable name for the report, in this case we have used 'My First Table Report' as the report name. The default report type is a Table Report. If it is not, then Select report type as Table Report.
In this guide, to maximize the screenshot space we hid the Reports Tree in many screen-shots.
Open the Datasources dialog to select a datasource against which you want to run the report.
Click on the Update button to save the report. The report will be created and shown in the report tree. To create/save any report, a datasource should be always selected.
You are now ready to enter a query to retrieve data. To keep things simple, the Graphical Query Designer (GQD) is shown to be used, to create a report that shows the details of all customers in a list report. See Query Designer for more details.
The Query Designer button opens the BI PLUS drag-n-drop type query builder, where you can create some of the most common queries. The list of all tables / views and materialized views that are available in the datasource (which connects as a particular user) appears on the left side. As you double-click a table, the table appears in the designer window. Notice that where the database table contains a Primary Key constraint on the table, this appears as red (e.g. CustomerID). As you drag the mouse over a column-name, a tool-tip displays the datatype of that column. You will know if this is a numeric, alpha or date/timestamp column, at its source.
To select a column to appear in the query, double-click the column name.
Now you are ready to test your report. This is done by clicking the Evaluate Query button and you will get the result.
The Evaluate Query has a few features worth discussing.
The generated SQL displays in the main top window. To the right of the window are a few other options.
- Query timer: The timer starts as soon as a connection to the datasource is attempted and will keep running until the query is killed or it returns the first batch of data.
- Result set batch limit: This is set to 1,000 rows by default, but can be configured to return 10, 100, 1000, 10000 or All the rows of a particular query. This is particularly useful if you as a developer just want to see what the result will look like based on a few sample rows and don’t necessarily want to return 10 million rows from a database (not to mention the strain that processing 10 million rows will place on the server).
- Stop Query: This lets you immediately kill a long-running query if it does not respond in a timely manner.
- Evaluate query: This is not for GQD, but for manually entered SQL, where the developer might want a quick tweak or change a function value or an alias or whatever in the SQL and does not want to have to save this change to the report definition before running the query again.
On the bottom portion of the Evaluate Query result pane is the record count of the returned data set. The current sample maximum row limit (10,000 by default) and then the actual raw unformatted results (unless formatting is done via SQL which makes it database dependent) from the datasource.
Click Close to close the Evaluate query panel that will return you to the GQD dialog.
You can now click SAVE to return you to the report definition.
The background in the SQL panel for a GQD-generated report is a peachy color and while you can select (to copy) the SQL, you cannot edit it immediately, since it was automatically generated by a query generator.
Click the Viewer tab to see the report the way an end user would see it. This invokes the Themes, totals, field formats and other properties of the report. Using this Viewer pane, you can now interact with the report just like an end user could. The Viewer tab allows the developer to invoke sort, filter, search, annotation, save-as features, treatments, Pivot Analysis and Graphical Explorer options.
Now the user (the developer in this case) can also interact with the report using the right-click menu. you can sort an individual columns by clicking the column header once to sort ascending, click again to sort in reverse order, etc.
A user can also save, email, print a report or apply several other features on the report.
Table Properties
Though you are familiar with the basics of creating Table report, there are more features supported by table reports. These features are defined in the Table Properties tab.
- Suppress Duplicate Column Count: This feature hides the duplicate values in the column. You can select the column number from the drop-down list on which to apply. For instance, if the report is sorted by the first two columns (country, city) and you set Suppress duplicate column count to 1 i.e. for the Country. Then repeating values for the country will be blanked out by the BIPLUS.
- Grex Configuration: See Grex Configuration for more details.
- Pivot Configuration: See Pivot Configuration for more details.
- Auto Fit Column Width: Arranges the width of all columns automatically.
- Show Table Header on Each Page: When a table report is exported to a static file format (e.g. PDF, Word etc.) then the Column Header(Name) is displayed on each page of the file.
- Show Vertical Label: Display all column header labels vertically. If you cannot see the label after applying this feature then you also need to set the Column Header Height field format.
- Show Line Number: Displays line numbers (record counter) for each record shown.
