Differences

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

--- versago3:dataview_setup [2022/09/07 11:39]
dlee
+++ versago3:dataview_setup [2023/07/14 12:38] (current)
dlee [Stored Procedure]
@@ Line 47: / Line 47: @@
   * 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.
+  * If the statement is correct, the first 50 records of the dataset are displayed in the “Results” area.
-===SQL Statement Restrictions===
+=== 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 data view's Field Display settings
+  * **Computed values** in the SQL statement must be assigned an alias.
-  * 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 //**[[#supplemental field controls|Supplemental Field Controls]]**// for additional information on sorting.
+==== B1 Query ====
-  * 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.  An example is found >> here.
-  * **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 =====
+This option should only be used with connections to SAP Business One. This option allows to select user queries built in SAP Business One.
-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.
+==== Stored Procedure ====
-  - Select the Crystal Report object type.
+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.
-  - A dialog box is presented to select the desired Crystal Report.
-{{  :versago/reports23.png?352x137  }}
+Store procedure parameters must be configured in the **//[[versago3:dataview_filters|Filters]]//** page.
-  - 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.
-{{  :versago/reports24.png?166x82  }}
+<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.
-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 added in front of the file name to ensure it always unique. 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.
+Use the following query to determine if your store procedure will be suitable to be used as a Versago data source:
-<WRAP center round tip 80%>
+<code>
-General information about configuring Crystal Reports for use with Versago and Bizweaver is found **//[[Configuring Crystal Reports|here]]//**.
+SELECT name, system_type_name
-</WRAP>
+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.
-=====B1 Saved Query=====
+<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>
-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.
+  * Select Crystal Report as the data source type.
+  * A dialog box is presented where a Crystal Report definition file (.RPT) can be selected.
-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// document.
+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.
-  - 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.
+=====**Need to review**=====
-  - 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.
+===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.
-=====Stored Procedure=====
+===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.
-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.
+<code>
+DRIVER={B1CRHPROXY32};SERVERNODE=<YourServer>:30015;DATABASE=<YourDB>
+</code>
-  - Select the “Stored Procedure” option.
+Replace <YourServer> and <YourDB> (without the < > symbols) with the appropriate values.
-  - 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/additional column names are returned, you must step through the report configuration process again to be sure these changes are accounted for in the “Display” step.
-=====View=====
-The “View” option has been deprecated since a SQL View can be referenced in the SQL Select option.
 ----
+**[[versago3:dataview#Add/Edit Data View]]**
 **[[versago3:Home|Home]]**

Wiki

User Tools

Site Tools

Differences

Page Tools