# Classic Chart Dynamic Filtering

**URL:** <https://forum.inductiveautomation.com/t/classic-chart-dynamic-filtering/36733>\
**Category:** Ignition\
**Tags:** database\
**Created:** [July 15, 2020, 5:46pm UTC](https://forum.inductiveautomation.com/t/classic-chart-dynamic-filtering/36733 "2020-07-15T17:46:18Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![dion.botha](https://avatars.discourse-cdn.com/v4/letter/d/46a35a/32.png) [@dion.botha](https://forum.inductiveautomation.com/u/dion.botha)\
**Post date:** [July 15, 2020, 5:46pm UTC](https://forum.inductiveautomation.com/t/classic-chart-dynamic-filtering/36733/1 "2020-07-15T17:46:18Z")

</div>

Hi Guys,

We have a testing machine and I am using a classic chart to scatter plot test data points. I also have a table in the window which shows a list of all orders.

I would like it such that if no order is selected in the table, it shows all data and if one (or more?) orders are selected, that it filters the data accordingly.

Would appreciate any advice on how to achieve this? I tried using an if statement in my named query but I don’t think you can use the if statement based on passed parameters - only on data columns.

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [July 15, 2020, 6:07pm UTC](https://forum.inductiveautomation.com/t/classic-chart-dynamic-filtering/36733/2 "2020-07-15T18:07:52Z")

</div>

I have done something very similar to this (prior to Named Queries), however, I think the way to achieve this is to structure the query in a way you can give a generic value to the query and it return all data.

I will use MS SQL because you haven’t specified and it’s what I am the most versed in as an example:

```auto
SELECT *
FROM OrderTable
WHERE OrderID LIKE ?

```

If you structure the query in this manner then setting the parameter to the wildcard character (in the case of MS SQL it’s ‘%’ will return all data from the table, but if you set the parameter to a specific value then only that data will be returned.

Obviously this is a simple example and not the only way to achieve what you’re looking for but with the information provided its about as good as I can do.

Another option would be to filter the database via script and manually loading the data in to the chart.

I’m still on 7.9.3 so named queries are not available for me yet. One day I’ll be able to convince them to let me upgrade it, but alas not yet.

Some information that will help others better answer your questions:

What version of Ignition are you using?  
What database are you using (MS SQL, MySQL, etc…)

---

<div class="post-metadata">

**Author:** ![dion.botha](https://avatars.discourse-cdn.com/v4/letter/d/46a35a/32.png) [@dion.botha](https://forum.inductiveautomation.com/u/dion.botha)\
**Post date:** [July 15, 2020, 6:30pm UTC](https://forum.inductiveautomation.com/t/classic-chart-dynamic-filtering/36733/3 "2020-07-15T18:30:52Z")

</div>

Hi Thanks for your reply,

Currently using V8.0.11, not updating to current release version (8.0.14) as I have seen several posts stating that some weird things happening - just FYI.

MS SQL Server.

I am trying to use named queries instead wherever I can.

I tried using LIKE as you have suggested but it is not returning anything if I don’t specify the order number…

---

<div class="post-metadata">

**Author:** ![dion.botha](https://avatars.discourse-cdn.com/v4/letter/d/46a35a/32.png) [@dion.botha](https://forum.inductiveautomation.com/u/dion.botha)\
**Post date:** [July 16, 2020, 10:35pm UTC](https://forum.inductiveautomation.com/t/classic-chart-dynamic-filtering/36733/4 "2020-07-16T22:35:02Z")

</div>

I feel like there should be a simple solution to what I am trying to do and I am just going about things in a strange way…

---

<div class="post-metadata">

**Author:** ![kaushik.shah](https://avatars.discourse-cdn.com/v4/letter/k/34f0e0/32.png) [@kaushik.shah](https://forum.inductiveautomation.com/u/kaushik.shah)\
**Post date:** [July 17, 2020, 12:47am UTC](https://forum.inductiveautomation.com/t/classic-chart-dynamic-filtering/36733/5 "2020-07-17T00:47:19Z")

</div>

It would probably want to fetch something as this :

```auto
SELECT *
FROM Order
WHERE id = CASE WHEN :id = -1 THEN id ELSE :id END

```

Here you would want to pass -1 to your named query when selecting all the orders or id for the one that is selected.  
I haven’t tested building SQL tuple with IN function in Named query but I know it can certainly work with scripting.

---

<div class="post-metadata">

**Author:** ![dion.botha](https://avatars.discourse-cdn.com/v4/letter/d/46a35a/32.png) [@dion.botha](https://forum.inductiveautomation.com/u/dion.botha)\
**Post date:** [July 17, 2020, 3:37pm UTC](https://forum.inductiveautomation.com/t/classic-chart-dynamic-filtering/36733/6 "2020-07-17T15:37:29Z")

</div>

Oh cool, it sounds like I can use parameters in a case statement thanks I will give this a try!

---

<div class="post-metadata">

**Author:** ![dion.botha](https://avatars.discourse-cdn.com/v4/letter/d/46a35a/32.png) [@dion.botha](https://forum.inductiveautomation.com/u/dion.botha)\
**Post date:** [July 17, 2020, 8:53pm UTC](https://forum.inductiveautomation.com/t/classic-chart-dynamic-filtering/36733/7 "2020-07-17T20:53:51Z")

</div>

Thanks, I got it working to where I don’t need to pass -1 if no row in the order table is selected:  
`WHERE [Order_Number] = CASE WHEN :lotId LIKE '' THEN [Order_Number] ELSE :lotId END`