How do I put the results from an SQL Query into a chart?

[Justin Zhao]

Hi everyone, I'm trying to put the results of an SQL Query into a chart. I've included screenshots of my expected outcome and my outcome below.

Regards,

Justin

Expected Outcome

https://ibb.co/4V1TGGY

Outcome

https://ibb.co/fHD6bdR

[Katerina Rudkovskaya [Stiltsoft]]

Hi @Justin Zhao,
 
As I see from your screenshot, you are using the Chart from Table macro: it means that you have our Table Filter and Charts for Confluence app installed for your instance.

The mistake that you’ve got speaks for itself: the macro doesn’t see the table inside its body.
It is not very clear how you are trying to process the SQL query, but the pattern should be as following:

Wrap your table in the Table Toolbox macro



Go to the Table Toolbox macro settings and wrap your table subsequently in the Table Transformer macro and in the Chart from Table macro.



The Table Transformer macro helps you to apply SQL query to your table (transform it and perform different calculations).

For example, here I just add 20 to the “Number” column:
SELECT T1.'Fruit',
T1.'Number' + 20 AS 'New Number'
FROM T*



The Chart from Table macro allows you to build a chart based on the transformed table.



Hope it helps your case.

If you are stuck with a query for the Table Transformer macro, you may always refer to our support portal. It is confidential, so you may freely attach a screenshot of your original table, describe what exactly you need to achieve, and we’ll provide you with a suitable code.

[Justin Zhao]

Thank you very much for the tips and advice, Katerina Rudkovskaya Stiltsoft. I'll try what you sent me out now. :D

[Justin Zhao]

Hi @Katerina Rudkovskaya [Stiltsoft] ,

I'm having issues putting the following SQL Query in the Table Transformer.

Regards,

Justin

USE cafeorder
DECLARE @DateTimeNow DATETIME = dbo.GETDATE_SYDNEY()
SELECT 
 substring(DATENAME(month, OrderDateTime), 0, 4) + '-' + substring(cast(year(OrderDateTime) as CHAR( 4)),3, 2) AS [Month], 
 sum(TotalCost) AS [Total Order Value],
 MONTH(OrderDateTime) as [Month],
 YEAR(OrderDateTime) as [Year]
FROM 
 PatronsOrder
where YEAR(OrderDateTime) >= YEAR(GETDATE()) -4 and MerchantId >= 1000 and PrintJobStatus = ('P') AND IsCancelled = 0 --and OrderDateTime < DATEADD(month, DATEDIFF(month, 0, @DateTimeNow), 0)
GROUP BY DATENAME(month, OrderDateTime), MONTH(OrderDateTime), YEAR(OrderDateTime)
order by YEAR(OrderDateTime), MONTH(OrderDateTime)

[Katerina Rudkovskaya [Stiltsoft]]

As I've mentioned before, let's move to the support portal.Give us a screenshot of your source table (without any macros and with visible headers). We'll recrete it in our test environment.Then describe what you want to achieve with the SQL query. The Table Transformer macro is based on the AlaSQL query, so the syntax may be different from yours.

[Justin Zhao]

Hi @Katerina Rudkovskaya [Stiltsoft] ,

I've been trying to sign up to the support portal, but I'm not getting an email to sign up with. Any fix around that?

Regards,

Justin

[Katerina Rudkovskaya [Stiltsoft]]

Hi @Justin Zhao, 

I see that you've already raised the ticket. I suppose you could find the message, right?

[Justin Zhao]

Hi @Katerina Rudkovskaya [Stiltsoft] ,

I've managed to find the message. I'm currently trying to provide the information required to solve the problem.

Regards,

Justin