Usercase : Hospital configurations, such as rules for identifying hospital stays, may vary. To avoid updating the dashboard for each customer, a parameter table can store these rules, which are then integrated into the snippet automatically upon page refresh.

Step 1: Create an parameter Table in ClickHouse

Begin by creating a table in ClickHouse to store parameters. It is important not to use the 'log' engine; instead, use 'MergeTree' as the engine for this purpose. Below is an example SQL statement for creating the custom_parameters table:

CREATE TABLE custom_parameters

(

 `ID` String,   

 `parameter` String,

 `text_value` Nullable(String),

 `creation_date` Datetime,

 `comment` String

)

ENGINE = MergeTree

PARTITION BY toYYYYMM(creation_date)

ORDER BY ID

SETTINGS index_granularity = 8192;

Step 2: Visualize the Table in BI4H

Navigate to BI4H visual and add a simple grid to a page. Populate this grid by including all fields from the custom_parameter table.

Step 3: Configure Editing Capabilities

1. Enable Allow Editing and set ID as the Primary key in the Edition section.
2. Enable Allow Editing for the parameter field in the Edition section.
3. Enable Allow Editing for the value field.
4. Enable Allow Editing for the comment field.

Step 4: Enable Grid Interactivity

After configuring the fields, proceed to the interactivity tab. In the editing section, enable the following options:

1. Allow Updating
2. Allow Adding
3. Allow Cloning
4. Allow Deleting
5. Set Batch as the editing mode

Step 5: Create a Query Snippet to Retrieve Hospitalization Values

To obtain all values related to hospitalization, create a query snippet as follows:

select text_value from custom_parameters where parameter = 'STAY_TYPE_DOS'

Step 6: Generate a Table Snippet for Hospitalization Statistics

Next, create a table snippet that utilizes the results of the query snippet from Step 5. The following is an example of such a query:

select * from cis_ims where stay_type_dos in (sn_hospitalisation())

Step 7: Build a Widget Based on the Table Snippet

Finally, use the table snippet created in Step 6 to develop a widget. 

A video to show you how to use it.