Report Data Sources
by
, 24-Feb-2013 at 11:47 AM (12207 Views)
This blog contains information on choosing and changing the data source in the Data Access report writer.
Data Source?
During the creation of a new report a data source needs to be selected. A data source makes it possible for the report writer to access the rows of data in tables. Currently 3 data source categories are available; DataFlex, ODBC and RDS.
DataFlex
For the DataFlex database the data source is either the list of tables (usually named filelist.cfg) or individual tables.
Opening a workspace (.ws / .sws) is the best way to select tables from the data source. Selecting a workspace automatically loads the paths to the data (DataPaths) as well as the reference to the list of tables (usually filelist.cfg) as this is defined in the .ws file. If the workspace' data paths contain multiple folders or if the filelist is not in the same folder as the data files the environment is setup correctly automatically. Of course assuming the information in the .ws file is correct.
If there is no workspace available the best and most used option is selecting a list of tables (filelist). The data source is then set to the list of tables (usually filelist.cfg) and opening tables relies on the folder the list of tables file is in. If the files are in multiple folders or the filelist is in a separate folder this option results in having difficulties to add tables to the report. That's why a workspace is a much better option.
Selecting individual tables is really meant for those situations where the tables are not listed in the list of tables or where tables need to come from multiple folders and these folders are not all listed in the workspace file. Or where the same table appears multiple times in different folders. The first selected table will be set as the data source. The biggest downside of this method is that more work needs to be done if the paths are changing as each table needs to be re-connected.
Besides the previous 3 options it is possible to select an INT file. INT file are used to connect a DataFlex application to an foreign database such as Microsoft SQL, DB2, Pervasive.SQL etc. The use of INT files for the report writer offers a flexibility of having to define one report and work with different databases for each of the deployed situations. However, the biggest downside of this method is SPEED! Using an INT file is much slower and I wouldn't want that. With an INT it is impossible for the report writer to use SQL optimalizations. With the ability to change a data source on at application integration level it is not an option you need.
ODBC
An ODBC data source is located via a DSN (Data Source Name) reference (User, System or File DSN) or via DSN less connection as the data source. User, System or File DSNs are maintained via the Microsoft ODBC Manager. Before you can use one of these for a new report the DSN needs to be created. After selecting the DSN its name will be stored in the connection string. In a DSN less connection the connection string contains all the necessary information (such as driver, user and password information, database etc) to access the database and its tables, views or stored procedures. Each ODBC driver has its own keywords and specifications.
RDS
The third category is RDS. This abbreviation stands for Runtime Data Source and all table descriptions are made during the report definition and stored in the report. No external references are present. The use of RDS only make sense if the report is used via integration and therefore it is not an option available in the standard edition.
Execution
A report can be ran from the designer (developer or standard edition) or as part of an application. This is called report integration. The developer edition contains the classes to make integration possible and allows the owner of the license to distribute the reports with a fat client application. The next paragraph deals with this. If the report is distributed and the end-user has a developer or standard edition of the report writer they can (often need) to change the data source in the reports and re-save the reports. After a change of the data source it is always wise to check the database to correct mismatches between the database information stored in the report and the real information. If the report uses an ODBC database and a DSN name is stored it is not needed to change the data sources of the reports, just make sure the DSN contents is correct and the table layout matches the stored information.
Integration
If the report is used as part of an application it is most likely that the application does not use the same data source information as what is stored in the report when this was created or updated. A report stores the data source name (location) to know how to get access to the data and to be enable the option to check the structure of the tables against what was know when the report was created. The data may even be retrieved from a location independent of the application.
ODBC, RDS or DataFlex?
Does it matter what data source is used in the report? Yes it does matter as you need to do more or less (or nothing) to change.
DataFlex
Lets first focus on the usuage of the DataFlex database. If the data files are located in the workspace of the application and there is just one set of tables with the same name the report class does all the data source replaces based on a property named pbAutoLocateDFFiles.
If the data source is a list of tables file (this is a file whose name is seen as the filelist (see function IsFileList)) the filelist name of the workspace is taken and replaced at execution time. If the workspace does not have a filelist (DF_FILELIST_NAME attribute is blank) an error is reported but as far as I know each DataFlex application MUST have a list of tables file.
If the data source is a table name (so individual tables were selected for the report) the name of each table is retrieved, the path is stripped off and an attempt is made to find a file with that name in the workspace paths via an internal function named FindTablePath. This function enumerates all paths in the workspace' datapath (psDataPath property). If any of the tables cannot be found an error is returned.
This automatic path correction does work for the main report and all sub-reports and nested sub-reports. As might not be known, the DataFlex report writer can have an "unlimited" number of sub-report levels.
If the workspace contains multiple tables with the same name (different folders) the automatic path correction might not work (depends on the workspace settings). In that case code needs to be written by the developer that does a similar activitiy as the auto locate of DataFlex files but corrected to the workspace. Study the private AutoLocateDFFiles method for the code that needs to be written.
ODBC
If the datasource is an ODBC datasource the change of data sources at integration level might not need to be done at all. It depends on what technique is used. In a DSN less connection it is probably needed to change the connection string contents while a user, system or file DSN only require the presence of the DSN and the correct configuration of the DSN. If the report integration code is generated by the integration wizard code can be generated (ChangeODBCDataSource) in which the data source can be modified. Modification means the change of psDataConnection and psDatabaseName.
In the following code snippet the connection string of the report and each of its possible sub-reports is read and changed. The report in this case used an DSN less connection string. The server name is replaced with information from an open table in the system. If the login information requires a change the code can be extended.
RDSCode:Procedure ChangeDatabaseConnection Handle hoReport String sServerFromTable sConnectionString sServerFromReportDatabaseConnection sSubReportId Integer iSubReports iSubReport Get TableConnectionString Customer.File_Number to sConnectionString Get ServerFromConnectionString sConnectionString to sServerFromTable If (sServerFromTable <> "") Begin Get psDatabaseConnection of hoReport '' to sConnectionString Get ServerFromConnectionString sConnectionString to sServerFromReportDatabaseConnection Move (Replace (sServerFromReportDatabaseConnection, sConnectionString, sServerFromTable)) to sConnectionString Set psDatabaseConnection of hoReport '' to sConnectionString Get SubReportCount of hoReport '' to iSubReports Decrement iSubReports For iSubReport from 0 to iSubReports Get SubReportId of hoReport '' iSubReport to sSubReportId Get psDatabaseConnection of hoReport sSubReportId to sConnectionString Get ServerFromConnectionString sConnectionString to sServerFromReportDatabaseConnection Move (Replace (sServerFromReportDatabaseConnection, sConnectionString, sServerFromTable)) to sConnectionString Set psDatabaseConnection of hoReport sSubReportId to sConnectionString Loop End End_Procedure Function DriverIndex String sDriverName Returns Integer String sCurrentDriverName Integer iDriver iDrivers Get_Attribute DF_NUMBER_DRIVERS to iDrivers For iDriver from 1 to iDrivers Get_Attribute DF_DRIVER_NAME of iDriver to sCurrentDriverName If (Uppercase (sDriverName) = Uppercase (sCurrentDriverName)) Begin Function_Return iDriver End Loop Function_Return 0 End_Function Function TableConnectionString Handle hTable Returns String Integer iDriver iConnections iConnection String sConnectionString sConnectionID Get_Attribute DF_FILE_LOGIN of StopPln.File_Number to sConnectionString If (Uppercase (Left (sConnectionString, 8)) = "DFCONNID") Begin Get DriverIndex "MSSQLDRV" to iDriver Get_Attribute DF_DRIVER_NUMBER_CONNECTION_IDS of iDriver to iConnections Decrement iConnections For iConnection from 0 to iConnections Get_Attribute DF_DRIVER_CONNECTION_ID of iDriver iConnection to sConnectionID If (sConnectionString contains sConnectionID) Begin Get_Attribute DF_DRIVER_CONNECTION_ID_STRING of iDriver iConnection to sConnectionString End Loop End Function_Return sConnectionString End_Function Function ServerFromConnectionString String sConnectionString Returns String String sServerPart Integer iServerPos iSemiColonPos Move (Pos ('server=', Lowercase (sConnectionString))) to iServerPos If (iServerPos > 0) Begin Move (Pos (';', sConnectionString, iServerPos)) to iSemiColonPos If (iSemiColonPos = 0) Begin Move (Length (sConnectionString)) to iSemiColonPos End Move (Mid (sConnectionString, iSemiColonPos - iServerPos, iServerPos)) to sServerPart End Function_Return sServerPart End_Function
With an RDS data source there is nothing that can and should or needs to be done in changing. The table description is stored in the report and no external references are present that require actions to be coded.
Summary
The correct selection of a data source is important and it is not difficult to change a data source at integration level, only a bit of time to study how the system is build is needed. We are open for suggestions to improve the system.