Reporting SQL Query Results

vaishali bansal April 2, 2014

I have installed sql plugin and my query runs fine. (I'm using sql query macro) How do I integrate this with chart macro to make better reporting? My query contains test case results and I want them to create pie/bar chart using that to show overall results and pass/fail history.

3 answers

1 accepted

1 vote
Answer accepted
Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2014

Insert a SQL Query macro and make sure the output format is html. You should see a table with the results of your query. Then create a chart macro and put the SQL Query macro in the body of the chart macro. It will create the chart based on the table output from the SQL Query. Within the chart macro adjust the properties of it to make the chart display as a pie chart ... specifically it is the "Type" parameter of the chart macro.

Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2014

This is what your wiki page will look like.

Like Kim Miles likes this
vaishali bansal April 3, 2014

Thanks a lot! This works. My SQL export has 6 columns, and last column is result (pass/fail). Rest of the columns describe testsuite and testcase id etc. How do I create a bar/pie chart indicating testsuite-wise results?

Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 3, 2014

Could you post an example or mockup of your data? Or maybe even a screen cap of the table output or a mockup of the table output?

vaishali bansal April 3, 2014

Here is the mockup of the final table that I have -

productName, testID, testcaseID, testName, testCaseName,Result

abc,1,100,a,b,pass

abc,1,101,a,f,fail

xyz,2,101,c,d,fail

and i want to create a chart that tells how many of abc/xyz were pass/fail etc.

I have been learning about charts here - https://confluence.atlassian.com/display/CONF50/Chart+Macro

and seems like the input tables have to be very basic or explicit for confluence to create a chart. This one chart that I want to see would require a count of productName-wise pass/fail, which I don't think is supported by current feature set. Is it?

vaishali bansal April 3, 2014

Yeah! This will give me the requisite chart. But then again, I will have to modify my query to display requisite table, right?

Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 3, 2014

Ok disclaimer I'm no reporting guru. But would the below format get you what you want?

Resulting in ...

Like I said, I'm no reporting master, so I'm kinda groping in the dark a bit here.

Davin Studer
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 3, 2014

Yup. There may be a different way to get it, but like I said I'm no reporting master and whenever I have to use the chart macro it is trial and error for me. :)

0 votes
vaishali bansal April 2, 2014

Thanks a lot! This works. My SQL export has 6 columns, and last column is result (pass/fail). Rest of the columns describe testsuite and testcase id etc. How do I create a bar/pie chart indicating testsuite-wise results?

0 votes
Felix Grund (Scandio)
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 2, 2014

You might want to look at the free Plugin PocketQuery. It has has the Google Chart API embedded and it's very easy to visualize data from a SQL database as chart. You can find detailed documentation here.

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events