Differences

This shows you the differences between two versions of the page.

--- versago3:dataview_setup [2022/09/07 10:41]
dlee created
+++ versago3:dataview_setup [2023/07/14 12:38] (current)
dlee [Stored Procedure]
@@ Line 5: / Line 5: @@
 {{ :versago3:dv_setup01.png?direct&1000 |}}
-===== Report and Data Source Information =====
-The first set of values is used to describe the report, define where the data will come from, and how the data will be obtained.
+==== Information ====
-{{  :versago:reports3.png?400  |}}
+  * **Name** is a __unique__ name for this data view.
+  * **Title** is displayed in the Versago user menus
+  * **Category** is the grouping mechanism used in the default Versago menu structure.
+    * Previously defined categories can be selected from the drop-down list. A new category can be created by typing the desired value and selecting Add from the drop-down.
+  * **Description** is an optional internal explanation of the data view.
+  * **Instruction** is displayed at the top of the user data view page.
+==== Availability ====
-  * **Report Name** is a __unique__ name for this report.
+  * **Roles** define the group of users that have access to the data view.
-    * This value is what is displayed in the Versago menus and at the top of the report page.
+  * Activate the **Public** option if the data view should be available prior to a user logging in.
-  * **Description** is optional but can be useful for explaining the purpose or contents of the report. It is only displayed on this page.
+  * Activate the **Do not show in menu** option when you have a report that you do not want to be displayed in the standard Versago menu structure.
-  * **User Instructions** is displayed at the top of the report page. This is optional but can be helpful in providing guidance to the user on how to navigate the report.
+    * The most common use of this option is when the report is only used as a lookup tool in Versago Forms, or as a sub-report in other Versago Reports.
-  * **Report Categories** are the grouping mechanism used in the default Versago menu structure.
-    * Previously defined categories can be selected from the drop-down list.
+==== Presentation Format ====
-      * 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.
-  * **Database** is the database connection to be used for the report.  See the **//[[versago:database_connection_configuration|Database Connections Configuration]]//** page for information on configuring database connections.
-      * 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 **//[[report_object_types|Report Data Source Types]]//** page for information on how the different types are used.
-===== Availability =====
+  * **List or Chart** format is the default “row & column” presentation.
+  * **Enable Paging** is used in conjunction with **Page Count** if you want to ensure that a specific number of records are displayed on each page.
+  * The **Schedule** format presents the data view in a calendar. See the **//[[versago3:dataview_schedule#Schedule]]//** page for additional configuration information.
-{{  :versago:reports4.png?400  |}}
+==== Connection ====
-  * **Roles** define the group(s) of users that can use this report.
+  * Select the **Connection Name** from the list of configured connections. See the **//[[versago3:connections|Connections]]//** page for information on configuring database connections.
-    * Click in the blank area of the “Role” field to see a list of roles and select as needed.
+      * When a connection is selected, the associated database name is displayed to the right.
-      * A report may be associated with multiple roles.
+  * Select the **Data Source Type**
-      * Click the <color #ed1c24>**X**</color> on a role to remove it from this report.
-  * Select the **Public** option if this report should be displayed in the menu section of Versago’s initial landing page prior to a user logging in.
-<WRAP center round important 80%>
+===== Data Source Types =====
-**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.
-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.
+{{  :versago3:dv_setup02.png?200  |}}
+==== SQL Query ====
+When this option is selected, a SELECT SQL statement can be entered manually or pasted from another source such as SQL Server Management Studio.
+{{  :versago3:dv_setup03.png?direct  |}}
+  * Enter the SELECT statement in the “Editor” area.
+  * 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 “Results” area.
+=== SQL Statement Restrictions ===
+  * An **Order By** clause cannot be used except in cases where the statement contains a TOP operator. The ordering (sorting) is handled by the data view's Field Display settings
+  * **Computed values** in the SQL statement must be assigned an alias.
+==== B1 Query ====
+This option should only be used with connections to SAP Business One. This option allows to select user queries built in SAP Business One.
+==== Stored Procedure ====
+In some rare instances it may be necessary to use an SQL Stored Procedure as the data source. This approach is not recommended if any other option is available.
+Store procedure parameters must be configured in the **//[[versago3:dataview_filters|Filters]]//** page.
+<WRAP center round important 100% >
+**Stored Procedure Limitations**
 </WRAP>
+Versago uses the view sys.dm_exec_describe_first_result_set_for_object to determine the store procedure's result set schema.
+However, there are limitations with the system view that restricts the use of some store procedures in Versago.
+The limitations include the use of temp tables and dynamic sql in the store procedure.
+Use the following query to determine if your store procedure will be suitable to be used as a Versago data source:
+<code>
+SELECT name, system_type_name
+FROM sys.dm_exec_describe_first_result_set_for_object(OBJECT_ID(@StoredProcedureName), 0)
+</code>
+====View====
+This option allows the use of a database View as the data source
+==== Crystal Report ====
+A Data View can be configured to display a Crystal Report. When this option is selected, the Crystal Report definition file is uploaded to the Versago site and used as the presentation method.
+<WRAP center round important 80% >
+The data source used in the Crystal Report definition **must** be the same as that of the Versago Data View connection.
+</WRAP>
+  * Select Crystal Report as the data source type.
+  * A dialog box is presented where a Crystal Report definition file (.RPT) can be selected.
+The Crystal Report source file is copied from the original location to the folder “C:\Program Files (x86)\TWBS\Versago\Websites\Versago Administration Web Site\Reports.” A number is prefixed to the file name to ensure it uniqueness. If the Crystal Report needs to be modified, you can either modify this file or modify it elsewhere and re-register the new version in Versago.
+---
+=====**Need to review**=====
+===SQL Server===
+When designing a Crystal Report that will use a __SQL Server__ database as its data source, the report connection type must be **OLE DB (ADO)**. This connection type has three options for the data provider: **SQL Native Client**, **Microsoft OLE DB Driver for SQL Server**, and **Microsoft OLE DB Provider for SQL Server**.  Any of these options should work with Versago.
+===HANA===
+When designing a Crystal Report that will us a __HANA__ database as its data source, the report connection type be **ODBC (RDO)**.  **{{ :versago:b1h_90_ht_setupdsforcrl.pdf |This document }}** explains how to configure this type of connection for use with HANA.  Two options are described in the document.  Be sure to use the **B1CRHPROXY32** option.  Here is a sample connection string to use as a starting point.
+<code>
+DRIVER={B1CRHPROXY32};SERVERNODE=<YourServer>:30015;DATABASE=<YourDB>
+</code>
+Replace <YourServer> and <YourDB> (without the < > symbols) with the appropriate values.
-  * Select the **Do not show in menu** option when you have a report that you do not want to be displayed in the standard Versago menu structure.
-    * The most common use of this option is when the report is only used as a lookup tool in Versago Forms, or as a sub-report in other Versago Reports.
-      * Use of this option is typically not needed if you are using custom menus for all non-admin users.
-===== Presentation Format =====
-{{:versago:reports5.png?400|}}
-  - **Report/Chart** format is the default “row & column” presentation.
+----
-  - **Enable Paging** is used in conjunction with **Page Count** if you want to ensure that a specific number of records are displayed on each page.
+**[[versago3:dataview#Add/Edit Data View]]**
-    - If this option is selected, then the desired number of records must be entered in the “Page Count” field that follows.
-    - If this option is not selected, Versago will present the data in a continuous flow as the user scrolls down the page.
-  - **Calendar** presentation (introduced in Versago 2.2). See the **//[[versago:calendar_report_configuration|Calendar Reports Configuration]]//** page for additional information.
+**[[versago3:Home|Home]]**

Wiki

User Tools

Site Tools

Differences

Page Tools