combobox filter "not in table"

Stimulsoft Dashboards.WIN discussion
joris.wils
Posts: 11
Joined: Tue May 26, 2020 1:56 pm

combobox filter "not in table"

Post by joris.wils »

So let's say I have a table with salespeople selling products:

Person Product
-------- ----------
Sales1 ProductA
Sales1 ProductB
Sales2 ProductA
Sales2 ProductB
Sales3 ProductA

So I would like to see that "Sales3" did not sell "ProductB" and in SQL I can do that with this query

Code: Select all

Select t1.person from table t1 where t1.person not in ( Select distinct t2.person from table t2 where product='ProductB")
My Question is: How could I use a combobox filled with products from a producttable to use in the query to see that "Sales3" did not sell "ProductB" ( as selected in the combobox )
And then display in a table on the dashboard.
Lech Kulikowski
Posts: 6198
Joined: Tue Mar 20, 2018 5:34 am

Re: combobox filter "not in table"

Post by Lech Kulikowski »

Hello,

Sorry, maybe we did not exactly understand your question. Could you explain your issue in more detail? Which result do you need to see?

Thank you.
joris.wils
Posts: 11
Joined: Tue May 26, 2020 1:56 pm

Re: combobox filter "not in table"

Post by joris.wils »

Hi Lech
I'll try again ;-)
So let's take the access-db attached
sales.accdb
(456 KiB) Downloaded 236 times
You will see that I have a table with salespersons selling products.

I would like to know what person did not sell "Product B" and I can do that with the query in that access db called "Who did not sell product B"

Now I want to put that in a dashboard with a combobox containing all products that exist in the table "sales" so I can select "product B" as a filter for the table-element in the dashboard.
I don't want the table-element to show all records with "product B", but I want to show the output like in the accessquery "Who did not sell product B".

Normal behaviour for the combobox would be:
Who did sell "product B"?
But I want:
Who did not sell "product B"
Lech Kulikowski
Posts: 6198
Joined: Tue Mar 20, 2018 5:34 am

Re: combobox filter "not in table"

Post by Lech Kulikowski »

Hello,

You can add an additional source that will contain all Products list and then use it for filtering.

Thank you.
joris.wils
Posts: 11
Joined: Tue May 26, 2020 1:56 pm

Re: combobox filter "not in table"

Post by joris.wils »

Okay, I don't think I explained it well enough.

I'll give another example. I have software products that are installed on certain computers logged in a database.

I'm looking to find computernames that do NOT have a certain software title installed.
I can do that with the following normal stimulsoft report. But right now I want the Dashboard equivalent of that.

Normal report and database attached. Please take a look at the report and select "Office 365" in the preview.
Attachments
Software is not installed on.mrt
(8.62 KiB) Downloaded 212 times
joris.wils
Posts: 11
Joined: Tue May 26, 2020 1:56 pm

Re: combobox filter "not in table"

Post by joris.wils »

database attached
Attachments
software.accdb
(512 KiB) Downloaded 217 times
HighAley
Posts: 8430
Joined: Wed Jun 08, 2011 7:40 am
Location: Stimulsoft Office

Re: combobox filter "not in table"

Post by HighAley »

Hello,

Our tool is made for creating reports base of data.
It's impossible to create a report based on data that does not exist.
Could you create a SQL query that will return necessary data?

Thank you.
joris.wils
Posts: 11
Joined: Tue May 26, 2020 1:56 pm

Re: combobox filter "not in table"

Post by joris.wils »

Yes the query is in the report I attached earliers.

Code: Select all

select * from computers where veld1 not in (select veld2 from softwareinventory where veld1=@softwareproduct)
The normal report is working perfectly fine.
I just want the dashboard equivalent of that.
HighAley
Posts: 8430
Joined: Wed Jun 08, 2011 7:40 am
Location: Stimulsoft Office

Re: combobox filter "not in table"

Post by HighAley »

Hello,

Here is a SQL query that can get necessary data without parameters.

Code: Select all

select combine.sw, combine.comps, swi2.Id
from
	(select 
		distinct swi.veld1 as sw, computers.Veld1 as comps
		from 
		Softwareinventory as swi, computers ) as combine
	left join Softwareinventory as swi2
	on combine.sw = swi2.Veld1 and combine.comps = swi2.Veld2
where swi2.Id is null
You could use it with a dashboard.

Thank you.
joris.wils
Posts: 11
Joined: Tue May 26, 2020 1:56 pm

Re: combobox filter "not in table"

Post by joris.wils »

That's what I was looking for. Thank you ;-)
Post Reply