Reporting SQL Query Results

[vaishali bansal]

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.

[Davin Studer · Accepted solution]

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]

This is what your wiki page will look like.

[vaishali bansal]

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]

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]

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]

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]

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]

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

[vaishali bansal]

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?

[Felix Grund (Scandio)]

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.