In my case the position is fixed and there is no possibility for user to sort the data. The problem is only the second of the two SELECT statements that are joined by the UNION works in ARI DataTables. The first SELECT always yields ZEROs. Any ideas? Here is the SQL I am using, maybe i am doing something wrong.

SELECT DISTINCT
LocRegion,
COUNT(DISTINCT uSimID)
FROM Simulators
WHERE SimType LIKE "FFS"
UNION
SELECT DISTINCT
LocRegion,
SUM(DISTINCT uSimID)
FROM Simulators
WHERE SimType LIKE "FFS"
GROUP BY LocRegion

Send SQL dump with sample data for "Simulators" table by email so we can investigate the problem.

Regards,
ARI Soft

I just send a sample SQL dump under the heading "How to Make Pie/Bar Charts from ARIDataTables". Both these threads are dealing with the same SQL table. Thanks.

Use the following query:



Regards,
ARI Soft

Thank you very much. With a little massaging and a few variations on the UNION idea I ended up with a solution that works well. Rather than using the UNION I ended up with 2 distinct module instances (one with data and the other with total) placed one above the other on the article to look like a single table. It looks reaaly good. Great product.

Another minor or simple question arises as I play with the look of the table on the page. I am having some trouble getting the columns to show the way I like. I want the PIE chart on top and the table to show below. The PIE chart is enabled on the first query and ARI DataTables module instance defined by this query.

SELECT
LocRegion,
COUNT(DISTINCT uSimID) AS Count
FROM Simulators
WHERE SimType LIKE "FFS"
GROUP BY
LocRegion

Plus, I have a 2nd module instance for only the total, and it has NO Pie chart enabled. Its query is set as follows,

SELECT
'Total', SUM(T.Cnt)
FROM (
SELECT
COUNT(DISTINCT uSimID) AS Cnt
FROM
Simulators
WHERE
SimType LIKE "FFS") T

The 2nd ARI DataTable is aligned to show immediately below the first on my article, so I want to set the column widths to make all data align and uniform. I am getting the data to show correctly using these queries. However, aligning the columns creates problems.

First, can I use the column settings to set the column widths and still use the Pie Chart? I am finding that when I set anything under the column settings the Pie chart disappears. Is this expected? If not, how do I make sure the Pie chart stays?

Secondly, how do I reference the 2nd column in the 2nd query above? I tried referencing column 2 as SUM, as SUM(T.Cnt) and Cnt but nothing seems to take. I know the reference is incorrect because i am also trying to set the column to center the total value of the 2nd query remains left justified.

I hope you follow what I am asking. If not, I can send a screen grab or resend the link and new account details.

Ken