Create
cancel
Showing results for 
Search instead for 
Did you mean: 
Sign up Log in
Celebration

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root

Avatar

1 badge earned

Collect

Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!

Challenges
Coins

Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.

Recognition
Ribbon

Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!

Leaderboard

Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,460,656
Community Members
 
Community Events
176
Community Groups

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

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

1 answer

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

Fri 3-1.png

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

Fri 3-2.png

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*

Fri 3-3.png

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

Fri 3-4.png

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. :D

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)

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.

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

Hi @Justin Zhao

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

Like Stiltsoft support likes this

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

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
STANDARD
PERMISSIONS LEVEL
Site Admin
TAGS

Atlassian Community Events