Can I use Named Query Parameters to filter SQL Table in Vision?

Ignition
, , ,
1

I am using a Named Query to display a pivot table from my database in Vision. The user doesn't need to see all columns and I would like to filter the columns without manually hiding the unwanted ones.

Reproducible dummy set for example:

CREATE TABLE reportTable (userName VARCHAR(30), issueType VARCHAR(30), 
    systemName VARCHAR(30), downTimeMins int);

    INSERT INTO reportTable (userName, issueType, systemName, downTimeMins) VALUES
    ("devDude", "Hardware_Issue", "PistonCap", 55),
    ("maryData", "Network_Issue", "Firebase", 60),
    ("devDude", "Network_Issue", "PistonCap", 55),
    ('mikeMan','Hardware_Issue', 'Dell', 120),
    ('maryData', 'Network_Issue', 'Internal', 50),
    ('caraAccounts', 'Network_Issue', 'WiFi', 50),
    ('devDude', "Production_Issue", 'Nutellafy', 45),
    ("devDude", "Production_Issue", "PistonCap", 55),
    ("mikeMan", "IT_Issue", "SAP", 55),
    ("caraAccounts", "IT_Issue", "Sage", 35),
    ("maryData", "Production_Issue", "Firebase", 60),
    ("mikeMan", "IT_Issue", "SAP", 40),
    ('mikeMan','Production_Issue', 'Dell', 30),
    ('maryData', 'Network_Issue', 'Internal', 50),
    ("devDude", "Hardware_Issue", "PistonCap", 55),
    ("maryData", "Network_Issue", "Firebase", 60),
    ("devDude", "Network_Issue", "PistonCap", 55),
    ("devDude", "Hardware_Issue", "Sage", 55),
    ("mikeMan", "IT_Issue", "Firebase", 20);

I can use a hardcoded query to view a pivot table like this where I select the columns and values to view:

SELECT issueType, SAP,  Firebase,  PistonCap,  Sage, Dell
    FROM
    (
    SELECT issueType, systemName, downTimeMins
    FROM reportTable
    )AS SourceTable
    PIVOT 
    (
    SUM(downTimeMins)
    FOR systemName IN(SAP,  Firebase,  PistonCap,  Sage, Dell)
    )AS pivotTable;

But I would like for the User to select which columns to view from a limited range, dropdowns maybe. When I bind the parameters to the user selection it doesn't produce a result as I think the query is out of sequence for this purpose.

binding
binding1652×1284 171 KB

Maybe someone could point me in the right direction. Apologies if it's a dumb question, learning curve etc .. :+1:

2

No need to use parameters for trying to filter columns. Just bind on data property your table. The open the customizer and hide the columns you need to display based on the user selection.

image
image910×570 49.8 KB

The table seems to be empty but note some rows are created under "Column Attributes Data" property

image
image987×235 30.2 KB

I added some checkbox components with a custom property hidden that is negative from selected property

image
image929×754 76 KB

open Cell Update Binding, I used a checkbox selected property to bind selected the cell under hidden column and the name of column of the source dataset

image
image1159×462 48.6 KB

Select the row,use the + icon and on value column find he property you want to bind
It might look like this
image
image1273×580 76 KB

now from check boxes you can control to either show or not a column

2 Likes
3

Dynamically generate your "column attributes dataset" property to hide the extra columns.

1 Like
4

Thanks guys, I hadn't spotted the Column Attributes Data property before, I'll have a go at that.

5

Thanks @jespinmartin1 that's very helpful :+1: