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
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 macroGo 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.
Thank you very much for the tips and advice, Katerina Rudkovskaya Stiltsoft. I'll try what you sent me out now.
Hi @Katerina Rudkovskaya _Stiltsoft_ ,
I'm having issues putting the following SQL Query in the Table Transformer.
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)
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.
It looks like you're new here. Sign in or register to get started.
