Data Views: – Introduction to Data Views

– Introduction to Data Views

Reference Contents > Data Views > – Introduction to Data Views

What are Data Views?

A Data View is a Caliach Vision definition of a way of collecting a particular set of data. Data is stored in the Caliach Vision database in Tables, which can be thought of as individual spreadsheets with a number of pre-defined columns and any number of rows. They are more complex than this but in essence you can imagine them as such, and in doing so you will not go to far wrong.

For example, a Part record is one row from the part table and each element of data you see on the Part Maintenance window is held in a column in the Part table (known internally as the PTMFILE table).

The sophistication of Caliach Vision however is in the way the table data can be linked, or more correctly said, related to, other tsble data. For instance a part can have a preferred supplier. The code in that column is related to a particular row in the supplier table. The supplier in turn has a relationship to an address row, and that in turn will be related to a country row. So while there is no country name column in the part table, through relationships within the data structure, a part can be associated with a particular country of supply.

In normal operation of the program all these relationships are built-in and data is presented without the need for any great understanding of how the where the data is stored.

However, there are times when the program, as standard, does not give you what you want and you need to customise the way you want data presented. This is particularly true when you want to present a graph. There are literally thousands of potentially related collections of data and many times that of sub-sets of that collective data. So it is unrealistic to expect the program to conjure up this data by itself so it is available to you at the click of a button. However, the next best thing is a framework for defining a data-set collection method, storing it and then re-using it repeatedly to collect just the data you are looking for. This is what is provided by Data Views.

So, for example, say you wanted a graph of stock value at the end of each month grouped by the default country of the preferred supplier of the part. This would normally be quite a complex data collection process but Data Views makes it easy. You design a Data View for the linking of the data elements and simply tell the graph design to use this Data View, by name. The Graph 2 system, when it sees a named data source, knows to run the named Data View and populate the graphs with data collected by it.

Besides Graphs, Data Views can be used also to populate lists on a window or a report.

How do Data Views Work?

WARNING: Caliach standard support is limited to the operation of the Data Views framework within Caliach Vision. The contents of Data Views and their design is specifically excluded from normal subscription support. Similarly, Caliach can give help with design of Data Views but only as a chargeable service.

When a Data View is run, it goes through the following steps:

1. It interprets the design and defines the variables needed for data collection.
2. It tests the design to make sure it can run without error, at least as far as it can without interaction with a server engine.
3. It collects the raw data into a result list using a number of alternative mechanisms, limiting the extent of collection if limits apply, and applying a final filter, if needed.
4. It consolidates the results where sub-totalling is needed.
5. It sorts the resulting consolidated data.
6. It calculates totals and other accumulated values that may be called for.
7. It top or tail truncates the results if a limit applies.
8. If cacheing is enabled, it stores the results data in the database for use by others.
9. It delivers the final results to the object (graph, list or report).

TIP: Limiting either record collection or the final sorted results is particularly useful for Key Performance Indicators where you are wanting to see, say, the last 6 Sales Order Dispatches, the 5 best performing sales regions or the top 20 stock movements by value.

See later for more details and explanation of Results Storage and Cacheing, but if a cache record is available steps 2 to 6 are replaced by reading the data directly from the history record.

Searching for Data

A Data View can have several Searches. A search is a data collection process that adds results to the result list in memory. If there is more than one the results of each are appended to the same result list. The use of more than one search can be useful if data is to be collected from two or more main tables, for example Sales Order Line Items and Sales History.

Data Views can be defined to use one of the following data acquisition mechanisms (it will choose the first available):

Search Interface: This is similar to standard report search interface, for example, the search part of the Masters — Parts — Print Reports window. This means that before the Data View runs the user will have to complete a Selection window so it may not be suitable for KPIs that typically need to function without user interaction. They are each specific to a design’s main table, and not all tables have them. They use the same Dynamic Query as as their equivalent reports do.

User Search: a user search is a named Custom Search which typically are available in standard report selection windows if the Custom Search button is available on the window. They are each specific to a design’s main table, and not all tables have them. They use the same Dynamic Query as as their equivalent reports do but unlike Search Interfaces there is no user interaction needed.

Dynamic Query: This must be available in the Statements.db database. They are the same as are used typically on report printing, Search Interface and User Search. They are complex multi-table data collection mechanisms from which Data View output can be drawn. If properly designed the are server-engine neutral.

Select Shortcut: These are complete pre-prepared SQL statements. They must be available in the Statements.db database, start with the keyword SELECT and contain a FROM clause. They are self-contained, generally fast in operation and server-engine neutral.

SQL Select Text: This is an individually written SQL select statement that can contain Join Shortcuts and/or Where Shortcuts from the Statements.db database. The resulting SQL must be appropriate for the Server Engine you are connected to and lead with a SELECT keyword. It is fast in execution.

Table and Joins: If none of the others are defined you can enter a main Table with up to 3 joined tables and a Listfile. These will be constructed into SQL selects to collect the data from which the Data View output is generated. Any Where clauses must be appropriate for the server engine you are connected to. Data columns from any of the 5 potential tables are available to the Data View.

TIP: Server Engines: The database servers Caliach Vision supports, SQLite, PostgeSQL, MySQL and it’s clone MariaDB, all speak different dialects of SQL particularly when functions are invoked. Objects in standard Statements.db typically accommodate this with DBMS-specific alternatives. But if these are not written correctly or if you use explicit SQL text statements that are locked to only one DBMS you will have problems when execution takes place with a different DBMS. You need to be specifically wary of this if users use different SQL Engines. Perhaps you are cloning your database to a local SQLite database and loading that into a Laptop. If your Data View design is not server-insensitive you will have problems. If at all possible make the SQL you write immune.

Search 1 always applies to the Main table of the Data View if it uses either a Serach Interface, User Search and/or a Table + Join. If using a Dynamic Query, Statement Shortcut or SQL Text the tables in use will be defined within them.

With Tables + Joins, up to 3 Joins can be made in a search. A join is where a related table row is found. If it is Left join then the join is optional so if no join row is found the data will still be included in the results. This is needed when the relationship is only optional.

A Listfile join is a special type of join where many records can be collected from the listfile join table. This is know as a many to many relationship. It is particularly useful if your driving search is from a Search Interface but the results you are looking for are not suitable for conventional joins. For example, you may want to select Customers in the normal Customer Search Interface and list their current Sales Order Line items. This can be done by making a listfile join on the SO line items file.

A Final Filter Calculation can optionally be applied to further restrict results after they have been delivered by the server. This is most useful when you wish to restrict the results on the basis of values in the joined file records. For instance, if the search main file is Sales History with a join to the Customer file, you may wish to restrict it to certain values of a Customer’s index code. This would not be possible on the main file search, but is on Final Filter because by then the customer’s data is available.

Custom Capability

Data Views can be used in customisation. All processing of a data view is performed by the oDataView object class. An object class needs to be instantiated into a variable of type Object or Object Reference. You can either do this in the variable definition or in a more controlled way using the following notation (in this case iDataObj is an object reference instance variable and the code is in the $construct of a window):

Do $clib.$objects.oDataViews.$newref() Returns iDataObj

To run a Data View you need to call the following method:

Do iDataObj.$RunDataView(pDataViewName, pReturnRef, pReturnMethod)

The method has no return value. It starts a Data View data collection process. pDataViewName is the name of the data view with history syntax if wanted (see later section). pReturnRef is an item reference to the object that will process the data (say a report instance) in pReturnMethod, the name of the method. If a user-interactive search is part of the data collection processing it is handed to the search window. Data cacheing is automatically controlled. pReturnRef.[pReturnMethod]() will be called to deliver the data collected.

You then need to create a method for receiving the results. This must be named the same as pReturnMethod and start with a $ symbol. So for example, in the $construct of a report you would have:

Do iDataObj.$RunDataView('TestView', $cinst, '$Results')

where TestView is the name of the Data View, $cinst is a reference to the report you are running, and $Results is the name of a class method that you have created to receive and process the data.

When you create $Result you need to create the following parameter variables which will contain the results of the data collection:

The following is an example of the code for a window with a headed list (named List) receiving the data in a method named $Result. You need to set up an instance variable for the list named iList:

Calculate iName as pName
Do $cinst.$title.$assign(con('Data View Results for : ',iName))
Calculate iList as pResultList
Do $cinst.$objs.List.$colcount.$assign(iList.$cols.$count())
Do $cinst.$objs.List.$calculation.$assign(pCalculation)
Do $cinst.$objs.List.$columnnames.$assign(pColumnNames)
Do $cinst.$objs.List.$columnwidths.$assign(pColumnWidths)
For %i from 1 to $cinst.$objs.List.$colcount step 1
    Calculate lAlign as strtok('pColumnAlign',',')
    Do $cinst.$objs.List.$setcolumnalign(%i,lAlign)
End For
Redraw {List}

Reports are slightly more complicated but there is a demonstration report class called rDataViewDemo to provide a framework for you. With it, for convenience the $userinfo class property is set to the Data View name. The code in the $construct method is as follows:

Do $ctask.tPrint.$ReportConstruct($cinst)
Do $clib.$objects.oDataViews.$newref() Returns iDataObj
Calculate lName as $cinst.$class().$userinfo
Do iDataObj.$RunDataView(lName,$cinst,'$Return') Returns lMessage
If len(lMessage)=0
    Quit method 
End If
Do $ctask.tEnv.$Ok(con('Report: ',sys(80)),kFalse,kTrue,kFalse,15482,lName,lMessage)
Do $cinst.$close()

The $destruct method cleans the memory:

Do iDataObj.$deleteref()

Then the $Return method receives the data form the Data View, sets up the fields and headings on the report and then prints the data. It uses the $SetReportFields oDataViews method that sets up report fields as long as there is one data field and one heading field (in this case named Field1 and 1009, respectively). It returns the row of the Data View design which holds the design and therefore you can use the name, description or any other element of the design. The code is as follows:

Calculate iList as pResultList
Do iDataObj.$SetReportFields(pResultList, pCalculation, pColumnNames, pColumnWidths, pColumnAlign, $cinst,'Field1', '1009') Returns iDesignRow
For %i from 1 to iList.$linecount step 1
    Calculate iList.$line as %i
    Do $cinst.$printrecord()
End For
Do $cinst.$endprint()

There is no need for a Do $cinst.$close() at the end as typically this is taken care of by the command or method used to run the report. The process of generating the data runs during the $construct of the report other than when the Data View contains a Search Interface. In this case the $Return method is run after the Search Interface has completed collecting the data. This means that while the Search Interface is open the report must remain in memory and can not be closed. The Print Report command can not be used as this closes the report before the Search Interface has been activated by the user. This interupted program flow requires Data View driven reports to be opened in a special way from a window using the following code:

Do $ctask.tPrint.$PreReportPrint('rDataViewDemo') Returns lOk
If lOk
  Do code method cReportDest/BeforePrint Returns lOk
  If lOk
    Do $ctask.tPrint.$OpenReport(sys(80))
  Else
    Do $ctask.tPrint.$RevertDest()
  End If
Else
  Do $ctask.tPrint.$RevertDest()
End If

The above example is available in the wDemoWindow under the PrintDemoDataView button. The name of the report class (rDataViewDemo) is set in the first line.

User and Group Access

You can restrict user access to individual Data Views with the User access and Group access settings. If both are left blank, any user can use the Data View design and any history it has generated. You can enter one or more user IDs and Group names, separated by commas, and if you do only users with those IDs or belonging to one of the groups will be permitted to use the Data View. So, for example if you were to set User access to CG,DAB and Group access to Sales,Accounts, then any user belonging to either the Sales or Accounts group plus the two users CG and DAB will get access.

The User Access and Group Access are not stored in the XML data structure, so they will not be saved on the server if they are uploaded to the Web depository. Similarly they will always be blank if downloaded from the server. They are however held in the the XML when exported to a file and imported from one.

When Graphs use Data Views, the graph design saves a copy of the Data View designs that it uses. If the user has access to the stored Data View design, this will be used rather than the self-contained design. This is to make possible the running of a graph after the Data View has been deleted or the user running the graph no longer has access to the Data View. In such circumstances the User and Group access settings are ignored, so tightening security on a data view may not stop graphs from functioning for users now excluded, but it will prevent them from benefiting from any changes to the Design View subsequently made.

Data View Results Storage and Cacheing

The Data Views system incorporates a mechanism for the storage of the results of a data collection. This is done in the main datafile in the DAVFILE data slot and is controlled by the Save Results settings on the Advanced tab of the Data View Maintenance window.

The storage of results has two useful purposes:

1. It allows the results to be cached which in simple terms means that if the Data View is run repeatedly, within a time limit, the collection of data is avoided and instead the results of the first collection is re-used. This is particularly useful when many users on the system tend to run the same Data View with the same search criteria around the same time, say on a Monday morning. Once the data is collected on the first run, other users get the results almost instantly.
2. It allows the results of dynamic data to be recorded and so can be viewed for comparison purposes in the future, again almost instantly. For example, a monthly stock valuation analysis can be recorded at the start of each month, and will remain on file for future reference or re-display.

There are three numeric settings for storage control:

If the Cache-time is zero no storage takes place at all. During the Cache-time period since last collection was completed, any user using the Data View will receive the results collected in the stored record.

For the subsequent Active-time period the data record that was previously the cache will be updated with a new collection of the Data View. After this time has expired the record will become permanent history until it is deleted automatically when the Auto-delete time in days has expired. The following examples illustrate the alternatives:

WARNING: Do not give a long cache-time to Data Views that contain Search interfaces as, if the cache-time is still valid, no search interface will be offered to the user, so he won’t get an opportunity to enter an alternative search selection. To do that he has to wait for the cache-time to expire.

WARNING: Don’t over record history. Although the design XML and the data collected is stored in a compressed state to minimise storage demand, you can quickly consume space in the datafile. It is most useful when the data collected is summary. For instance, it would not be sensible to hold large detailed reports, regularly taken, in this way as space requirements would unnecessarily reduce performance of the overall system for little advantage. To store large reports use other techniques such as printing a report to the disk destination and saving that in a known external location. You can view data storage statistics if you Print Results in File — System Manager — System Timing Test, and look for the Data View cache and history data collection DAVFILE.

TIP: Do not set the Cache value until after you have finished and tested the Data View, otherwise you may be misled into thinking changes are not being reflected in the results because you are viewing a cache record rather than a new build from the design.

For any selected Data View you can review and manage historical collected data using the Manage History button on the Advanced tab of the Data View Maintenance window. This opens the Data View Data Collection History Maintenance window. Here you can delete unwanted history records and review the data collected on previous occasion.

The stored collected data also holds the ledger period number and language that was applied to the data collection. These are controlled by three settings in the Data View design. You can force the Data View to a particular language, otherwise the Language Swap choice will be used at the time of generating the data. For Data View designs relevant to ledger data, you need to specify which ledger is appropriate and whether it applies to the current open ledger period. With these settings the history will save the appropriate ledger period number.

Explicitly Using Data View History

It may be useful to use Data View history data for comparison purposes, for instance in a graph where you want to compare current results with last month’s. This can be done when data view name is entered by appending a special syntax of extra characters in the form DataViewName?xxxxx the ? character tells the system that it must look for a data history set for the named data view rather than collecting current data for the data view. There are a number of options for the characters that follow the data view name:

So to use a recorded set of data from the data view StockValueMonthly taken 12 months back as a comparison on a layer of a graph you would enter StockValueMonthly?DM-12 in the Data property of the layer.

Compiled in Program Version 5.10. Help data last modified 4 Jan 2015 05:40:00.00. No class.

Reference Contents > Data Views > – Introduction to Data Views