Five Obstacles to Reporting

Obstacle 1 - Missing Data: Most reporting tools use the CW "Reporting API" and "reporting views" for data access; by design, both return subsets only of all data elements available. In contrast, CFAR uses ODBC conections to the CW databases; these support access to all tables and fields.

Obstacle 2 - Cryptic Table and Field Names: Cryptic table and field names make it difficult to find the data you want to include in a report. For example, to find the ticket number field for a service ticket in Manage, you need to know that the table name is SR_Service and the field name is SR_Service_RecID. To make tables and fields more discoverable, CFAR assigns descriptive names to each of these data elements. (Note: If you are a database expert and prefer the "real" table and field names, CFAR provides a setting to turn these on.)

Obstacle 3 - Normalized Data: Even when a field you want is accessible and has a descriptive name, it can still be hard to find due to the way databases work. Almost all relational databasees are normalized; this means they are structured to reduce data redundancy and improve data integrity. Unfortunately normalization often makes it more difficult to find certain kinds of fields when making a report. Luckily CFAR has a way to "de-normalize" the databases so that related fields that are actually in two different tables appear in what looks like one.

Obstacle 4 - JOINS: When you want to include fields from more than a single table in a report, you must write a JOIN to specify how the two tables are related to each other. SQL experts know how to do this, but usually the casual user does not. CFAR addresses this by recording all links between all tables in its meta-database - then uses this information to automatically write the JOIN code when needed.

Obstacle 5 - Database-centric View of Data: Descriptive names and automatic JOINS make it much easier to find and select tables and fields you want in a report. Even so, it can still be overwhelming to locate the correct tables and fields when working with a large database. For example, CW Manage has over 26,000 fields in over 700 tables and 900 views.

The problem is that the table and field display still reflects a "database-centric" view of the data. Unfortunately, most users don't think of the application in terms of how the data is stored. Rather, they see the application as being organized around distinct data entry screens. To leverage this understanding, CFAR uses "application views" that more closely resemble the content of the ConnectWise application's screens. Using these views makes it much more likely that the user will discover the correct tables and fields for a report. (Note: If you are an expert, you can turn on "database view" to display tables and fields as they actually exist in the databases.)