Setting up the Standard Graphic Column in the Graphic Report

Purpose/Symptoms: This article addresses an issue encountered when setting up a Standard Graphic Column in a Graphic Report. While creating the Graphic Report is generally straightforward, some steps can be non-obvious. Users may find that the Standard Graphic Column, after initial setup with a single dataset, only displays one specific piece of a borehole at a time, synchronized with the current record, instead of showing the complete lithology picture for different boreholes.

Common Initial Setup & Problem Description:

Typically, the first step users take is to create a dataset for the object in the Graphic Report, such as a "Standard Graphic Column". For example, a dataset named "Test Dataset" might be created with fields like PROJECT, SITE_ID, SAMPLE_ID, DEPTH_FROM, DEPTH_TO, and LAYER_CHEMICAL.

An example of T-SQL code to create such a dataset by hardcoding a universal output is:

IF OBJECT_ID('tempdb..#Test_Dataset') IS NOT NULL 
    DROP TABLE #Test_Dataset;

CREATE TABLE #Test_Dataset
(   PROJECT varchar(10),    
    SITE_ID varchar(10),
    SAMPLE_ID varchar(10),    
    DEPTH_FROM int,
    DEPTH_TO int,    LAYER_CHEMICAL varchar(5));

-- Example Data Insertion

INSERT INTO #Test_Dataset (PROJECT, SITE_ID, SAMPLE_ID, DEPTH_FROM, DEPTH_TO, LAYER_CHEMICAL)

VALUES 
    ('ERIS', 'ES06', 'S0918451', 1, 2, 'LIM'),
    ('ERIS', 'ES06', 'S0918452', 2, 3, 'LIM'),
-- ... (additional data representing multiple boreholes) ...
    ('ERIS', 'ES06', 'S0917568', 18, 19, 'SAP'),
    ('ERIS', 'ES06', 'S0917569', 19, 20, 'SAP'),
    ('ERIS', 'ES07', 'S0917570', 1, 2,  'BRK'),
    ('ERIS', 'ES07', 'S0917571', 2, 3,  'SAP'),
-- ... (additional data) ...
    ('ERIS', 'ES07', 'S0917585', 18, 19, 'SAP'),
    ('ERIS', 'ES07', 'S0917586', 19, 20, 'BRK'),
    ('ERIS', 'ES08', 'S0917587', 1, 2, 'BRK'),
    ('ERIS', 'ES08', 'S0917588', 2, 3, 'SAP'),
-- ... (additional data) ...
    ('ERIS', 'ES08', 'S0917598', 14, 15, 'SAP'),
    ('ERIS', 'ES08', 'S0917599', 15, 16, 'BRK')

SELECT * FROM #Test_Dataset

After creating this dataset, users usually proceed to the "Designer" mode of the Graphic Report:

1. In the Data Tab, they specify the created dataset (e.g., "Test Dataset") as the "Primary Data Source", with the "Column name" reading from a field like LAYER_CHEMICAL.
   
    
2. In the Scaling tab, DEPTH_FROM and DEPTH_TO are assigned to the "Primary scaling column" and "Secondary scaling column" fields, respectively.
   
    

However, testing the report at this stage reveals the unexpected behaviour: the column shows only a limited section of a borehole.

Cause:

The issue arises because the Graphic Report's Standard Graphic Column often requires a "Master" dataset to properly contextualise and filter the data for display, especially when navigating between different entities, such as boreholes. The single-dataset configuration lacks the necessary hierarchical data structure.

Solution:

The data must consist of at least two datasets:

• One "Master" dataset
• One or more datasets that reference this "Master" dataset

Important Note: The dataset used for the "Standard Graphic Column" (e.g., #Test_Dataset) does not need to be the "Master" dataset. Instead, it will populate based on the SITE_ID (or a similar key field) selected from a "real" Master dataset. This Master dataset typically contains a listing of projects and SITE_IDs.

Steps to Implement the Solution:

1. Create a "Master" Dataset: Add a new dataset that will serve as the "Master". Let's call it QueryCollars. This dataset should list the unique borehole identifiers (e.g., SITE_ID) that you want to navigate through. Here's an example of a hardcoded SQL SELECT statement for the QueryCollars dataset:
   
   SELECT 'ERIS' AS PROJECT, 'ES06' AS SITE_ID, 'BH' AS SITE_TYPE, 0 AS START_DEPTH, 100 AS END_DEPTH
   UNION
   SELECT 'ERIS' AS PROJECT, 'ES07' AS SITE_ID, 'BH' AS SITE_TYPE, 0 AS START_DEPTH, 200 AS END_DEPTH
   UNION
   SELECT 'ERIS' AS PROJECT, 'ES08' AS SITE_ID, 'BH' AS SITE_TYPE, 0 AS START_DEPTH, 300 AS END_DEPTH;
2. Configure Dataset Relationships in Designer Lists:
   • In the Graphic Report "Designer" mode, go to the "Datasets" tab within "Designer Lists".
   • Ensure your new QueryCollars dataset is present. It should ideally be designated as the "(Control)" or "Master" dataset.
   • The original dataset (e.g., "Test Dataset") should be listed, and its "Master" property should reference QueryCollars. This establishes the parent-child relationship where "Test Dataset" is filtered by QueryCollars.
     
     
3. Verify Existing Settings: No alterations should be necessary in the "Data" or "Scaling" tabs for the original "Test Dataset" configuration if it were set up as described in the "Common Initial Setup" section.
4. Save and Test: Save the Graphic Report object with these changes and execute it for testing.

Expected Outcome: After implementing the master-child dataset structure, using the navigation arrows in the Graphic Report toolbar will correctly move from one borehole to another, displaying the complete lithology picture on the Standard Graphic Column for each selected borehole.

Conclusion:

Employing a master-child dataset structure, where the detail dataset (used for the graphic column) is linked to a master dataset (controlling the selection, e.g., SITE_ID), is nearly always required for the correct display and navigation of graphical columns and charts in this reporting environment.

Want to learn more?

Online Help Manuals - Click here for the latest version

Learning Management System - Click here to login or here to request access