Special Case Datasources

Custom Vantage Office Reports normally use the casual SELECT queries in SQL, but special cases for special reports are also needed and are used both for performance and functionality issues. In order to create these special case reports, one must edit the Report datasource of a report.

To create a specialized datasource, you must make your Original Report datasource a sub query under a new Main query that has only your desired columns.

For example:

Your original query is this, SELECT * FROM tblOriginalDataSource, that contains the strAccountID, strDescription, strAccountType, strAccountGroup, dblValue, and dblRate.

Your main query would looks like this, having only strAccountID, strDescription, and the sum of all dblValue grouped by their AccountID and Description.

--Main query

SELECT strAccountID, strDescription, SUM(dblValue, 0)

FROM (

--Start of Original datasource

SELECT * FROM tblOriginalDataSource

--End of Original datasource

) AS TempTable

GROUP BY strAccountID, strDescription

This is how your main query would look like in the casual SQL queries. However, Custom Vantage Office would require you to include Special Codes for these to take effect.

Here are the important Codes that must be included on the datasource.

--*SCSTART*--

Must be placed before the Original Report datasource

--*SC*--

Must be placed after the Original Report Datasource

These special codes should be in the exact format and are recommended to be at least one line apart from the original codes just to ensure that errors.