Report Based On Multiple SQL Queries

[iRadiate]

This is more of a general question that I need a point in the right direction.

I have a UX component with several Viewbox controls. Each viewbox control has it's own SQL query based on different unrelated tables. The UX basically acts as a summary page of the Viewbox info.

I want to create a single report that will mimic the data on the summary page. So, I need a report that is based on multiple SQL queries that are coming from different tables and not joined in any way ... I need each Query displayed separately.
Creating a report on a single query is easy but I don't know how, if it is even possible, to do it for multiple queries.

Anybody have any idea?

[Russell Craycraft]

Do something like this:

SELECT [ID]
,[Title]
,[Date]
,[Description]
,NULL AS [This]
,NULL AS [That]
,NULL AS [thisDate]
,NULL AS [otherDescription]
FROM table1

UNION ALL

SELECT NULL AS [ID]
,NULL AS [Title]
,NULL AS [Date]
,NULL AS [Description]
,[This]
,[That]
,[thisDate]
,[otherDescription]
FROM table2;

If you have ChatGPT, you can input each of your queries for each viewbox into ChatGPT and say "make this one query showing all fields. Tables aren't related" and it'll do it for you.
image.png

[CharlesParker]

might be much faster (render time) to create a view with everything you need, should allow you to see the data returned is what you need before you get to building that report!

[Russell Craycraft]

I agree with Charles. I'm not sure how long it'll take, but you might want to do as much as you can in the database. If it's a massive amount of data, you may even schedule something to update a table every x minutes and use slightly older data. It all depends on the amount of data and use case.

[madtowng]

How slow are the queries ?
It would be a shame to have to query everything again instead of combining the loaded data into one summary viewbox.

[iRadiate]

Thanks guys, I will look at creating a view. The queries are not large ... returning only the last record for 9 unique treatment units. Something like this:

*************************** WATER ************************
SELECT     OutputWaterQaId,
    MachineDisplayName,
    MachineId,
    Day,
    Abs(DATEDIFF(day, GetDate(), Day)) AS ElapsedDays,
    PrimaryPhys,
    BackupPhys,
    Approved
FROM QaOutputWater
WHERE OutputWaterQaId IN (SELECT MAX(OutputWaterQaId) FROM QaOutputWater GROUP BY MachineId)  
ORDER BY MachineId

*************************** ENERGY ************************
SELECT     OutputPlasticQaId,
    MachineDisplayName,
    MachineId,
    Day,
    Abs(DATEDIFF(day, GetDate(), Day)) AS ElapsedDays,
    PrimaryPhys,
    BackupPhys,
    Approved
FROM QaOutputPlastic
WHERE OutputPlasticQaId IN (SELECT MAX(OutputPlasticQaId) FROM QaOutputPlastic GROUP BY MachineId)  
ORDER BY MachineId

​

I don't have a ChatGPT account, maybe it's time

[madtowng]

I haven't tried yet, but this (build 8806) suggests there is no need to make another call to the sql server.
Getting the data from the various viewboxes into a state variable is relatively easy.

Capture.png

[peteconway]

If they are not joined in any way then why this " GROUP BY MachineId" - in both queries - the MachineID being used as an argument is the common join - you should be using the Nested SQL Query - query type in the Viewbox Builder. It's the most powerful part of the viewbox's data tools.

[iRadiate]

I haven't tried yet, but this (build 8806) suggests there is no need to make another call to the sql server.
Getting the data from the various viewboxes into a state variable is relatively easy.

Capture.png

I am currently on build 8540 and I am very gun shy about updates. But this is definitely something I can look into when I do decide to update.
Thank Gregg.

[iRadiate]

If they are not joined in any way then why this " GROUP BY MachineId" - in both queries - the MachineID being used as an argument is the common join - you should be using the Nested SQL Query - query type in the Viewbox Builder. It's the most powerful part of the viewbox's data tools.

Hey Pete, nice to see you are still around

The reason for the GROUP BY MachineId is that the query will not return the last record for each MachineId if this is not included. See below.

This returns last record for each machine.

Query1.jpg

This does not return last record per machine, only the first last record it encounters.

Query2.jpg


To try and explain what is going on, there are 9 radiation treatment units at our centre. The Physics department performs various regular QA checks (output, energy, mechanical, etc) on each one of these treatment units.
Every type of QA check has it's own entry form which gets stores in its own SQL table.

This data is summarized on a UX component using a viewbox to perform the SQL query for each type of QA showing only the last QA that was performed. This works great! No problem in the UX component. See example below ...

Summary.jpg


Now I wanted to have a button to click and generate a report with the same summary information contained in the UX above.
The problem of course, is that the UX is based on several viewbox SQL queries and I don't see how to combine the data in the queries into a single query that I can use in a report.
So that is what I am struggling with. I can generate a single report for each case above, but that's not really what I'm looking for.

[Russell Craycraft]

If this works in SQL you can easily create a view with it, but I agree that MachineId does look like the join field (at least with the 2 tables provided). If you want to provide all of the SQL Statements I can put them into a query for you real quick to try, then create a statement to create a view for you, or I can send you a Zoom link, and walk you through how I'd do it (Not to insult your intelligence by any means, I'm just helping from the lowest level. I haven't even spent the time learning ViewBoxes yet).

(SELECT OutputWaterQaId,
MachineDisplayName AS OutputWaterMachineDisplayName,
MachineId AS OutputWaterMachineId,
Day AS OutputWaterDay,
Abs(DATEDIFF(day, CURDATE(), Day)) AS OutputWaterElapsedDays,
PrimaryPhys AS OutputWaterPrimaryPhys,
BackupPhys AS OutputWaterBackupPhys,
Approved AS OutputWaterApproved
FROM QaOutputWater
WHERE OutputWaterQaId IN (SELECT MAX(OutputWaterQaId) FROM QaOutputWater GROUP BY MachineId)
)

UNION ALL

(SELECT OutputPlasticQaId,
MachineDisplayName AS OutputPlasticMachineDisplayName,
MachineId AS OutputPlasticMachineId,
Day AS OutputPlasticDay,
Abs(DATEDIFF(day, CURDATE(), Day)) AS OutputPlasticElapsedDays,
PrimaryPhys AS OutputPlasticPrimaryPhys,
BackupPhys AS OutputPlasticBackupPhys,
Approved AS OutputPlasticApproved
FROM QaOutputPlastic
WHERE OutputPlasticQaId IN (SELECT MAX(OutputPlasticQaId) FROM QaOutputPlastic GROUP BY MachineId)
)

-- Unsure of how you want to set this up, but MachineId may need/not need to be prefaced with Outupt'type'MachineId
-- ORDER BY MachineId;

[madtowng]

I understand being shy about updates.
You can "cheat" by testing with Community edition.
I haven't tested the code below with your data, but if it doesn't work, I'm sure the adjustments will be simple.
I just did a last check. It looks like your viewboxes have 3 different formats.
I adjusted the code to combine the first 2 viewboxes.
Let me know what you think about the other viewboxes.

var vb1PTR = {dialog.object}.getControl('vb1');
var vb2PTR = {dialog.object}.getControl('vb2');
​
var reportArray  = vb1PTR.data;
reportArray.push( vb2PTR.data);
{dialog.object}.stateInfo['reportArray'] = reportArray;
​​​​​​​

[iRadiate]

...I can send you a Zoom link, and walk you through how I'd do it (Not to insult your intelligence by any means...)

You can't insult what I don't have .. haha! I appreciate all the help I can get.

I tried to use your suggestion with the UNION ALL previously but it doesn't quite get what I want. It's close but it ends up combining the data from the second query into the same field column names as the first query.

Union All.jpg

I've attached the viewbox queries I'm trying to use on a single report.

[Lance Gurd]

The viewbox is a very powerful tool, with some great examples. Have a look at the home tab of a newly created viewbox. Near the bottom there is a hyperlink to load sample viewbox. From the list that comes up choose the Advanced: List of orders..... This will create the data and layout of a very powerful viewbox, with examples of expand and collapse of sections as well print an HTML report of the viewbox layout without any callbacks for the data. I am sure with a little work the data from each of your viewboxes can be collected and linked into one in the same manner

[madtowng]

You might want to add another column to each part of the union all query, in this case I would add 'QaOutputWater' as 'fromtable' to the first query, and 'QaOutputPlastic' as 'fromtable' to the second query.
This obviously gives you a way to determine where it's from.