This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
versago:report_configuration [2020/01/08 09:44] runger [Display Tab] |
versago:report_configuration [2020/12/01 12:32] (current) dlee [Report and Data Source Information] |
||
|---|---|---|---|
| Line 88: | Line 88: | ||
| {{ : | {{ : | ||
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | | + | |
| - | - A new category can be created by typing the desired value in the blank drop-down box. This category will then be displayed in the list the next time the drop-down is selected. | + | |
| - | | + | |
| - | - When a connection is selected, the associated database name is displayed in the “Database Name” field. | + | |
| - | | + | * **Data Source Type** are the various ways to obtain data from a database. The values displayed in the **Data Source** area will vary depending on the selected Report Object Type. See the **// |
| ===== Availability ===== | ===== Availability ===== | ||
| Line 103: | Line 103: | ||
| {{ : | {{ : | ||
| - | | + | |
| - | | + | |
| - | - A report may be associated with multiple roles. | + | |
| - | - Click the <color # | + | |
| - | | + | |
| - | | + | |
| - | - An example of how this option might be used is to provide a list of company locations with address and telephone numbers. This information would be general and not necessarily pose a security or data risk. | + | <WRAP center round important 80%> |
| - | | + | **Use caution with this option**. No restriction on data content will be applied as may occur once a use is logged in. Whatever the report returns will be displayed to the user. |
| - | | + | |
| - | - Use of this option is typically not needed if you are using custom menus for all non-admin users. | + | An example of how this option might be used is to provide a list of company locations with address and telephone numbers. This information would be general and not necessarily pose a security or data risk. |
| + | </ | ||
| + | |||
| + | | ||
| + | | ||
| + | | ||
| ===== Presentation Format ===== | ===== Presentation Format ===== | ||
| Line 203: | Line 208: | ||
| </ | </ | ||
| - | =====[[Display Tab v23-|Display Tab versions 2.3 and lower]]===== | + | =====Display Tab for versions 2.3 and lower===== |
| + | [[Display Tab v23-|Display Tab versions 2.3 and lower]] | ||
| - | =====[[Display Tab v24+|Display Tab versions 2.4 and higher]]===== | + | =====Display Tab for versions 2.4 and higher===== |
| + | [[Display Tab v24+|Display Tab versions 2.4 and higher]] | ||
| ====== Record Submission Tab ====== | ====== Record Submission Tab ====== | ||
| Line 248: | Line 255: | ||
| {{ : | {{ : | ||
| - | ====== Links tab ====== | + | ======Links tab====== |
| Report **links** provide access to a variety of functions that can enhance the overall use of the report. There are two key characteristics of links. | Report **links** provide access to a variety of functions that can enhance the overall use of the report. There are two key characteristics of links. | ||
| - | | + | |
| - | | + | |
| - | ===== More on Links ===== | + | Details |
| - | + | ||
| - | A link can be created on an existing column or a new column. Keep the following in mind when determining which type of link to use. | + | |
| - | + | ||
| - | - If the action is associated with a specific column in the main record, that value should be used for the link. This makes it easier for the user to understand the relationship of the action to the main record. For example, the main report might be a list of customer orders. The action is a report of details about a specific order. Using the “Order Number” value in the main report as the linking item helps the user understand what is being used to control the child report. | + | |
| - | - Any “new columns” specified for linking will always appear at the end of the main record. These columns cannot be moved so it may be difficult for the user to find them. | + | |
| - | - An action can use a “Calculated Value” column as the linking item. This “calculated value” can be set up to show a text value. And “calculated value” columns can be repositioned in the column sequence, so the links can be set in a more logical sequence within the row. | + | |
| - | + | ||
| - | ===== Links Setup ===== | + | |
| - | + | ||
| - | All actions use the same basic setup as shown below. | + | |
| - | + | ||
| - | {{ : | + | |
| - | + | ||
| - | - **Link Name** is a unique name for this link. It is displayed as the column header and content value if the //Link From// is “New Column (Link name)”. | + | |
| - | - The **Type** drop-down list is used to select the type of action to be used. Available actions are: | + | |
| - | - Report | + | |
| - | - Forms | + | |
| - | - Crystal Report | + | |
| - | - Bizweaver Webservice | + | |
| - | - Other URL | + | |
| - | - **Link From** indicates if the link will use an existing column or a new column. Select the desired option from the drop-down list. | + | |
| - | - The **Link Column** is the column used for the link if the “Existing Column” option is used for //Link From//. Select the desired column from the drop-down list. | + | |
| - | - **Destination** defines the target of the action. The values in this field will change depending on the link Type. More information on Destinations is found in the discussion of each Type below. | + | |
| - | - **Show in New Browser tab** indicates if the link action results should be displayed in a new browser tab (default) or in the current page. | + | |
| - | - **Conditions** can be used to control when an action link is active based on values in other columns. See //**[[# | + | |
| - | - Click the red <color # | + | |
| - | - The **Link Parameters** section is used when it is necessary to pass parameters to the target function. There may be some instances where the target function does not require information and this section is not needed. | + | |
| - | - Click the red <color # | + | |
| - | - **Destination Field** is the linking item in the target function. For example, we might need to pass an invoice number to a child report | + | |
| - | - The **Is Static** checkbox indicates whether the value to be passed to the target function will be a value from a column in the source report or a fixed value. When this option is selected the //Source Field// will be a text box rather than a drop-down. | + | |
| - | - The **Source Field** provides a parameter value to the target function. If the //Is Static// checkbox is not selected, a drop-down list of columns from the main report is provided. Otherwise the field appears as a text box where a static value can be entered. | + | |
| - | + | ||
| - | ===== Report Setup ===== | + | |
| - | + | ||
| - | The **Report** link is the execution of a Versago report. The concept is like the Sub-Reports function described earlier, except that the report being opened does not necessarily need to be connected to the main report, as it is in the Sub-Reports. Using a Link rather than a Sub-Report is sometimes helpful if having the child results open in a new browser tab will make the results easier to use. | + | |
| - | + | ||
| - | The following image shows the setup for a Report action to call the Sub-Report illustrated earlier in this document. | + | |
| - | + | ||
| - | {{ : | + | |
| - | + | ||
| - | ===== Forms Setup ===== | + | |
| - | + | ||
| - | The **Form** action provides a mechanism to open a Versago Form from a Versago Report and either find an existing record for maintenance or start a new record and automatically populate fields on the form. | + | |
| - | + | ||
| - | The following example shows the Form action calling a Versago form used to create Service Call for SAP Business One. | + | |
| - | + | ||
| - | {{ : | + | |
| - | + | ||
| - | In this example, the customer code (BP Code) and customer name (BP Name) are being passed into the form. | + | |
| - | + | ||
| - | <WRAP center round help 100%> | + | |
| - | If the value passed to the form is the primary key (e.g. vgoRecNum or whatever it has been named) the application will automatically execute a lookup for the record of the value passed. If the record is found the remaining fields will be populated. | + | |
| - | </ | + | |
| - | + | ||
| - | + | ||
| - | ===== Crystal Report Setup ===== | + | |
| - | + | ||
| - | The **Crystal Report** action provides a way to execute a Crystal Report for presentation to the user. An example of how this could be used is where the main report is a list of customer invoices. A Crystal Report action could cause a copy of the invoice document to be generated using Crystal Reports. | + | |
| - | + | ||
| - | {{ : | + | |
| - | + | ||
| - | Registration of the Crystal Report is the same as described for Crystal Reports in the // | + | |
| - | + | ||
| - | <WRAP center round help 100%> | + | |
| - | Crystal Reports that use a specific value for execution (e.g. an invoice number) must have a parameter in their definition. The Link Parameters will show the parameter(s) as Destination Field(s) that are mapped to a Source Field from the main report. | + | |
| - | </ | + | |
| - | + | ||
| - | + | ||
| - | ===== Bizweaver Webservice Setup ===== | + | |
| - | + | ||
| - | A **Bizweaver Webservice** link allows a user to execute a Bizweaver workflow from a report using the Bizweaver API. These workflows can be used for a variety of background processing. | + | |
| - | + | ||
| - | <WRAP center round tip 100%> | + | |
| - | Prior to v2.3, calls to the Bizweaver API were only sent using GET. Beginning with v2.3, calls can be made using GET or <color # | + | |
| - | </ | + | |
| - | + | ||
| - | The “Destination” value is the string that calls the Bizweaver web service and passes any parameters that are needed. When parameters are specified in this string the are automatically set as Destination Fields in the Parameters section. Each parameter then needs to be matched to a Source Field from the report using the drop-down lists. | + | |
| - | + | ||
| - | ==== Calling Bizweaver Using GET ==== | + | |
| - | + | ||
| - | {{ : | + | |
| - | + | ||
| - | * **Method** is GET. | + | |
| - | * **Task ID** is not used for GET. | + | |
| - | * **Destination** is the URL needed to contact the Bizweaver web service. | + | |
| - | * All values to be used to execute the Bizweaver workflow are included in the " | + | |
| - | + | ||
| - | <code html> | + | |
| - | https://< | + | |
| - | </ | + | |
| - | + | ||
| - | * **< | + | |
| - | * Use HTTP or HTTPS as needed by your site | + | |
| - | * If Versago uses HTTPS the Bizweaver web service must use HTTPS as well. | + | |
| - | * **Task ID** is the target workflow ID defined in Bizweaver. | + | |
| - | * **Arguments** are any values that need to be passed to the workflow. | + | |
| - | + | ||
| - | ==== Calling Bizweaver Using POST ==== | + | |
| - | + | ||
| - | {{: | + | |
| - | + | ||
| - | The POST option is easier to configure since most of the values are entry/ | + | |
| - | + | ||
| - | * **Method** is POST. | + | |
| - | * **Task ID** is the target workflow ID defined in Bizweaver. | + | |
| - | * **Destination** is the URL needed to contact the Bizweaver web service. | + | |
| - | + | ||
| - | <code html> | + | |
| - | https://< | + | |
| - | </ | + | |
| - | + | ||
| - | * **< | + | |
| - | * Use HTTP or HTTPS as needed by your site | + | |
| - | * If Versago uses HTTPS the Bizweaver web service must use HTTPS as well. | + | |
| - | + | ||
| - | * **Link Parameters** are any values that need to be passed to the workflow. | + | |
| - | * Enter the name of the variable expected by the Bizweaver workflow in the Destination field. | + | |
| - | * Select the source value from the drop-down list in the Source Field. | + | |
| - | * Leave these two values blank if no parameters need to be sent to the workflow. | + | |
| - | + | ||
| - | ===== URL Setup ===== | + | |
| - | + | ||
| - | The **URL** action is used to call a “website” (the URL). The URL is entered in the “Destination” field. The page being called could contain general information. In this case the “Destination” string is whatever you would type into a browser (e.g. __%%www.twbs.com%%__) to open the Third Wave website. | + | |
| - | + | ||
| - | Or you may want to pass information to the page for processing there. An example of how this could be used is to call the UPS website and pass a tracking number. | + | |
| - | + | ||
| - | An example of how the UPS example might be set up is shown below (the “source” fields are used only for illustration). | + | |
| - | + | ||
| - | {{ : | + | |
| - | + | ||
| - | The Links Parameters are determined by Versago from information in the URL. In this case the URL is “__%%wwwapps.ups.com/ | + | |
| ====== Chart tab ====== | ====== Chart tab ====== | ||
| Line 393: | Line 269: | ||
| See the // | See the // | ||
| - | |||
| - | ====== Report Object Types ====== | ||
| - | |||
| - | **Report Object Types** are the mechanisms that can be used to obtain desired data from a database. Only one method (object type) can be selected for a report. | ||
| - | |||
| - | {{ : | ||
| - | |||
| - | The image above shows the list of methods displayed as of Versago version 2.2. However, some of these methods are slated for removal in upcoming versions. Use only the options described below. | ||
| - | |||
| - | ===== SQL Select ===== | ||
| - | |||
| - | The **SQL Select** method is the most commonly used option. When it is selected, a text box is presented where standard SQL script can be entered. The script can be entered manually or pasted from another source such as SQL Server Management Studio. | ||
| - | <WRAP center round important 60%> | ||
| - | Note that only SELECT statements may be used. | ||
| - | </ | ||
| - | |||
| - | {{ : | ||
| - | |||
| - | - Enter the SQL SELECT statement in the “Query Statement” area. | ||
| - | - Once the SQL statement has been entered, click [**Execute**] to validate the statement. | ||
| - | - Standard SQL error messages will be displayed if errors are encountered. | ||
| - | - If the statement is correct, the first 50 records of the dataset are displayed in the “Output Results” area. | ||
| - | - Date values are shown in a rather strange format as show in the image above. This is normal. The dates will be displayed correctly in the report presentation. | ||
| - | - Click [**Save**] to confirm that the results are what you wanted and to save the SQL statement. The SQL Select dialog area will close automatically. | ||
| - | - Click [**Close**] to close the SQL Select dialog box without saving any changes that may have been made. | ||
| - | |||
| - | When working with an existing report the first part of the current SQL Select statement is displayed. Click on the [**…**] button to open the editing window. | ||
| - | |||
| - | {{ : | ||
| - | |||
| - | <WRAP center round important 60%> | ||
| - | The SQL statement can be changed at any time. However, remember that changes may affect other areas of the report defined in subsequent steps. Be sure to complete the [**Execute**] and [**Save**] steps if you do make changes. Otherwise your new selection station will not be saved. Then be sure you continue the wizard through the “Display” step to ensure any data changes are reflected in the formatted report output. | ||
| - | </ | ||
| - | |||
| - | |||
| - | ==== SQL Statement Restrictions ==== | ||
| - | |||
| - | Certain SQL statements are not allowed in the SQL Select function. | ||
| - | |||
| - | * An **Order By** clause cannot be used except in cases where the statement contains a TOP operator. The ordering (sorting) is handled by the report output process. See // | ||
| - | * A **Select Distinct** statement cannot be used directly. This is due to some internal constraints used in the Versago SQL processing. If “Select Distinct” is needed it will need to be done using a sub-query. | ||
| - | * **Computed values** in the SQL statement must be assigned an alias. For example, the statement “ColA + ColB” will not work. Instead, you would need to use something like “ColA + ColB as [CalcValue]” where [CalcValue] is the alias. “CalcValue” is then seen as a valid column in the subsequent report definition steps. | ||
| - | |||
| - | ===== Crystal Report ===== | ||
| - | |||
| - | A different data presentation option is provided through the **Crystal Report** method. In this case a Crystal Report is developed outside of Versago. The report is then registered in Versago and used as the presentation method. | ||
| - | |||
| - | - Select the Crystal Report object type. | ||
| - | - A dialog box is presented to select the desired Crystal Report. | ||
| - | |||
| - | {{ : | ||
| - | |||
| - | - Click [**Choose File**] to open the standard Windows Explorer tool. | ||
| - | - Locate and “open” the desired Crystal Reports file (.RPT). | ||
| - | - The file name is displayed in the Versago dialog. | ||
| - | - Click [**Select**] to associate the Crystal Report file with the Versago report. | ||
| - | - Once the file has been selected you will see file path information displayed in the Report Data Source box. | ||
| - | |||
| - | {{ : | ||
| - | |||
| - | The Crystal Report source file is copied from the original location to the folder “C: | ||
| - | |||
| - | ===== B1 Saved Query ===== | ||
| - | |||
| - | The **B1 Saved Query** option is only available for sites using the SAP Business One business management system. This option provides a method to use queries developed in SAP Business One as a Versago data source. | ||
| - | |||
| - | The database connection (“Database” as described earlier in this document) must include the credentials required to connect to SAP Business One for this option to work. This configuration is described in the //Versago – Database Connections Configuration// | ||
| - | |||
| - | - Select the “B1 Saved Query” option. | ||
| - | - Click the “Report Data Source” drop-down to see a list of available saved queries from SAP Business One. | ||
| - | - Select the desired query. | ||
| - | |||
| - | While this option is available, the preferred approach is to copy the query from Query Manager in SAP Business One and paste it into Versago as a SQL Select statement. | ||
| - | |||
| - | ===== Stored Procedure ===== | ||
| - | |||
| - | In some rare instances it may be necessary to use a SQL Stored Procedure as the data source. This approach is not recommended if any other option is available. However, it is available. | ||
| - | |||
| - | - Select the “Stored Procedure” option. | ||
| - | - Click the “Report Data Source” drop-down to see a list of available stored procedures in the designated database. | ||
| - | - Select the desired procedure. | ||
| - | |||
| - | Keep in mind that if the procedure is changed so that different/ | ||
| - | |||
| - | ===== View ===== | ||
| - | |||
| - | The “View” option is no longer needed since a SQL View can be referenced in the SQL Select option. The “View” option will be dropped in a future release of Versago. | ||
| - | |||
| - | ===== Visual Query Editor ===== | ||
| - | |||
| - | The “Visual Query Editor” is no longer supported in Versago and should not be used. | ||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | ====== Supplemental Field Controls ====== | ||
| - | |||
| - | **Supplemental field controls** are exposed by clicking the {{ : | ||
| - | |||
| - | {{ : | ||
| - | |||
| - | All rows will display the “Is Groupable, | ||
| - | |||
| - | - **Is Groupable** causes the output to be automatically grouped for each unique value in the selected data. | ||
| - | - Only one value can be marked as “groupable.” A message is displayed when moving to the next step if multiple grouping items are found. The message indicates which item will be used for grouping. | ||
| - | - The grouping set in the report definitions can be removed when the report is executed but cannot be changed by the user. | ||
| - | - **Sorting Options** are ascending and descending. Select the desired option from the drop-down list. | ||
| - | - Sorting is applied based on the display sequence of the columns. You may need to change the display sequence to get the sorting you desire. | ||
| - | - If no sorting option is selected, the data is presented in whatever order it is read from the database. | ||
| - | |||
| - | There are several **Column Formats** available. Their use depends on the type of data being reported. Usage is outlined in the following table. | ||
| - | |||
| - | ^**Format**^**Used with Data Type**^**Usage**^ | ||
| - | |Decimal_2 |Numeric | ||
| - | |Currency | ||
| - | |Date |Date or DateTime | ||
| - | |DateTime | ||
| - | |Email | ||
| - | |File |Text |The data is treated as a Windows file path to a specific file and presents an icon with an underlying link with the value. Clicking on the link causes the system to attempt to download the file to the user’s device. See **Note 2** below.| | ||
| - | |URL | ||
| - | |||
| - | **Note 1** | ||
| - | |||
| - | An additional column named //Culture// is displayed when the Currency, Date, and DateTime options are selected. This column has a drop-down list of formatting options for the data type. This allows the presentation to be easily tailored to different locations. The current culture designation is for this value in this report. It cannot be set to change based on the user. So, a user in the United States and a user in Great Britain will see whatever format is selected even through their local cultures use different formats. | ||
| - | |||
| - | If you need to report date and time values in a specific format, it may be necessary to use the SQL Convert function in the SQL Select for the report and present the value as a text value. More information on the SQL CONVERT function, and available formats, can be found here: https:// | ||
| - | |||
| - | **Note 2** | ||
| - | |||
| - | Any files that are to be accessed using the “File” option must be in a folder location that can be accessed by Versago and Windows IIS. What this means is that files will need to be in a network share (e.g. [[file:/// | ||
| - | |||
| - | Also, keep in mind that any user that has access to the report can download any file listed in the report. If certain files should be available to certain group(s) of users, different reports may be needed. | ||
| - | |||
| - | |||
| - | ====== Link Conditions ====== | ||
| - | **Link Conditions** are used to control how, and when, an action is executed. Examples might include: | ||
| - | * Open a list of open invoices sub-report only if the customer’s account balance is not zero. | ||
| - | * Open a URL only if the UPS tracking number value is not blank. Otherwise, take no action. Conversely, use a different URL if the FedEx tracking number is not blank. Note that these conditions are on different link fields. | ||
| - | * Execute a Crystal Report if the document type is an invoice. Otherwise, take no action. Conversely, execute a different Crystal Report if the document type is a credit memo. Note that these conditions are on different link fields. | ||
| - | ===== Add/ | ||
| - | To add or modify Action Conditions: | ||
| - | - Move to the “Actions” step of the report wizard. | ||
| - | - Add a new action or select the row of an existing action. | ||
| - | - Click the [**Conditions**] button. | ||
| - | - The “Action Conditions” tool is displayed. | ||
| - | In this example, we want to display a sub-report of open invoices only if the current balance is $10,000 or greater. If the balance is less than $10,000 the sub-report should not be displayed when the action link is clicked. | ||
| - | {{ : | ||
| - | Create the condition formula as described below. **Do not enter the formula directly in the “Preview” area** as the condition will not function in the report. The preview is simply a way for you to see how the formula is constructed. | ||
| - | - Build the formula from left to right using the element tools as shown. | ||
| - | - The “Open Bracket” and “Close Bracket” are only required in complex conditions. | ||
| - | - Select the comparison value from the “Report Field” drop-down list. | ||
| - | - Select the comparison operator from the “Operator” drop-down list. | ||
| - | - Enter the first comparison value in “Value1.” | ||
| - | - Enter the second comparison value in “Value2” if the “between” operator is being used. | ||
| - | - Select the “Logical Operator” (AND, OR) if needed to add more condition information. | ||
| - | - Click [**Preview**] to see the completed condition statement. | ||
| - | - Click [**Submit**] to save the condition. | ||
| - | - Click the red <color # | ||
| ====== Large Datasets for Reporting ====== | ====== Large Datasets for Reporting ====== | ||
| Line 618: | Line 334: | ||
| The “refresh” process can be done using a scheduled job in SQL or setting up workflow in Third Wave’s Bizweaver application to execute the processing. | The “refresh” process can be done using a scheduled job in SQL or setting up workflow in Third Wave’s Bizweaver application to execute the processing. | ||
| - | |||
| - | ====== Files for Download through Versago ====== | ||
| - | |||
| - | As noted in the // | ||
| - | |||
| - | ===== MIME Types ===== | ||
| - | |||
| - | **Technical Topic** | ||
| - | |||
| - | Multipurpose Internet Mail Extensions (**MIME**) types identify the types of content that can be served to a browser or a mail client from a Web server. Since Versago is a browser-based application, | ||
| - | |||
| - | The most common file types (PDF, XLXS, TXT, DOCX, etc.) are already defined in IIS (the Windows web server). The general process to add/ | ||
| - | |||
| - | One MIME type that is not defined by default is for Outlook email messages. These files have the extension **.msg**. Specific steps to add this MIME type are found here: http:// | ||
| ====== Setting Static Filter Values ====== | ====== Setting Static Filter Values ====== | ||