WINSALES Support Document - sup107
The Report Database Utility Instruction Guide
The Report Database Utility (wsreportdb.exe) was developed to provide customers a tool to access their data from a WINSALES 5.0 database for reporting purposes using tools like Crystal Reports or Microsoft Access to create reports. The Report Database Utility will not run on an old WINSALES database (versions 2.0, 3.x and 4.x).
WINSALES SECURITY: For security purposes you can block WINSALES Security Groups from running the Report Database Utility. In the ADMINISTRATOR under EDIT + SECURITY GROUPS select a group and add or remove the Report Database Utility from the enabled list as appropriate and SAVE. Also in Security Groups you can block the user’s in a group from running the Report Database Utility from the TOOLS menu. Select the group, then on the enabled side go under WINSALES + MENU ITEMS + TOOLS and remove Report Database Utility from the list of selections and this selection will be disabled for users in that group.
RUNNING THE PROGRAM: You can run the Report Database Utility within WINSALES from TOOLS, from the WINSALES program group (users in SQL Server may schedule the update stored procedures after creating those in the first run of the program). When you run the program the Connect To Database window appears for you to log into the database. Clicking OK you will see one of the screens below depending on your WINSALES database style (Microsoft Access or SQL Server).
MS Access 2000 WINSALES Database
Click the image for larger view
Users working with Microsoft Access WINSALES databases will have the Report Database Path defaulted to the name of your WINSALES database “_Report.mdb” that will be created in the same directory as the WINSALES database. If you are custom building your own reports, you will to make sure that your reports are using this default mdb name and path when they go to look for the data file to refresh upon. IF you purchased the package of pre-developed reports, the default path and mdb file name is “C:\ program files\ winsales\ reports\ reportingdb_report.mdb” This is the name and path that the reports will be seeking to find the generated data set to refresh upon If you want to change the location of where the Advanced Reporting utility saves the generated data file you can do this but it will require that you also change the path and name in the pre-developed reports as well. The changing of the name and path in the pre-defined reports requires that you have access to a licensed copy of the Crystal reports program. It is suggested that you save the generated data file in the same location for each report and use the same naming convention for each report to try and simpliy the administration of your reports. Once the location is set click the CREATE REPORT DB button and the program will launch creating the MDB file. The MDB file even for a large (250mb and up) WINSALES database should not take more then half an hour.
MS SQL Server WINSALES Database
Click the image for larger view
Users working Microsoft SQL Server databases have the option to create a Microsoft Access 2000 database, or create views and tables on the SQL Server to store the report database. To create the Acess database select the database type of ACCESS REPORTING DATABASE and specify a path to create the MDB file.
To create the tables and stored procedures on your SQL Server the user running the Report Database Utility program on a SQL Server database must be a SQL Server Login with the server role of System Administrator. For more information on this issue see www.winsales.com/support/sup221.doc
The tables and stored procedures that are created on the SQL SERVER are by default enabled for the PUBLIC group. Use SQL SERVER ENTERPRISE MANAGER to make modifications as desired. If you have to recreate the tables and stored procedures the program will set the security again for access by the PUBLIC group only..
To create the SQL Server tables and stored procedures:
1. Click REMOVE to remove all the report tables, views, and stored procedures from previous runs of the program. This is especially important to remember if there have been structural changes to the database (field names changed, or new fields added).
2. Once the REMOVE process has completed click PREPARE to create the stored procedures in SQL Server for the program to run to create the VIEWS and TABLES that will contain the report data.
3. Once the PREPARE process has completed click CREATE REPORT DB to run the stored procedures that were creted by the PREPARE selection.
Program Notes & Specifications
Create Microsoft SQL Server stored procedures to regenerate "reporting" tables on
an "as needed" basis. The stored procedure can be schedule to run at various intervals
(like once per day) using the SQL Server Administrator. The SQL Server reporting tables
currently requires the WinSales database to also be stored in the same SQL Server database.
Create a Microsoft Access database containing "flattened" reporting tables. The Microsoft
Access database can be generated from a SQL Server or Access WinSales database.
The utility works by expanding all "classes" defined in the database such that each attribute
is a single column in a reporting table. Attributes in a class that have defined "Allow Many" will
be stored in a table named in the form "wsrpt__X_".
You can identify all reporting "tables" by their prefix "wsrpt_". The tool creates views
"wsrpt_activity" and "wsrpt_customer" in SQL Server for the related base tables. The tools
make copies named "wsrpt_activity" and "wsrpt_customer" in the Access reporting database.
All tables (views) created assume that the report writer will "join" together any table that
he/she needs to create the appropriate report. All tables (except wsrpt_activity and wsrpt_customer)
has an "ID" attribute as all or part of the primary key. The "wsrpt_activity", "wsrpt_customer", and “wsrpt_notes”
view/tables are identical to the corresponding "activity", "customer", and “notes” WinSales tables.
All WinSales reference attributes (joins) are stored as only the ID. The ID attribute is in the
form "ref__id" where is the class that ID references. The report
writer must create the join to show meaning "user-friendly" data from the referenced class.
The Microsoft Access reporting database is completely standalone from its original WinSales
database. For example, it could be sent to report writer who could use it alone to create reports
without needing any other WinSales software or databases. This reporting Access database
should only be used as a "source" and not used to store views or reports. The utility process
"deletes" and "recreates" the database as part of its operation. The purpose to avoid some
garbage collection and other poor performance issues with Access.
The utility attempts to make fully "legal" SQL names from the original class and attribute names.
Most special characters and punctuation have been removed from the names.
The reporting process is repeatable in what it creates unless class or attribute names are
changed. The reporting process will use the new names if appropriately cleaned and preped prior to regenerating a reporting database.
The Microsoft Access reporting database will split any class with more than 255 attributes
into multiple tables to get around an Access limitation of 255 fields per table. The extended
tables for the additional attributes are in the form "wsrpt_N" where N is a digit.
The utility follows the following rules when creating the report database and duplicate field names are encountered in a class:
1) For simple attributes, duplicate names are n where n = 1,2,3..
2) For join attributes:
a) If alias available, the name will be _id
b) Else if display as attribute, the name will be _id
c) Otherwise, the name will be ref__id
*The name will be appended with a digit if there are duplicates in the view.
3) These rules apply even if they are "allow many" (multiple respsones) allowed.
On a large SQL Server database (3gb) the SQL Server > SQL Server stored procedure takes about 15 minutes to execute running directly on the server (Win2000 Dell power edge 2400), performance is not greatly affected by running the process from a client machine. The equivilant SQL Server > Access generation takes less then 10 minutes. Most WINSALES customer database will take under 15 minutes to complete the process.
Numeric data is not masked in the conversion to the report database. For example $235.00 in WINSALES will come across as 235.00 in the report database. The report you write on the data source will be used to mask the data.
Date and Timestamp fields types are converted to date values in the report database. A WINSALES date like 3/17/03 stored in the WINSALES database as 37697 will be converted to the 3/17/03 value in the report database. A timestamp value like 6/1/02 10:38:57 PM that is stored in the WINSALES database as 37408.94372 will be converted to 6/1/02 10:38:57 PM in the report database.
Report Database schema - SUP110
SQL Server security - SUP221
Converting SQL Server notes table NoteText from binary to text - SUP222
Contact Silent Partner for more information.