How to format google chart table in pocketquery macro

I've been testing pocketquery macro and is very nice offering a lot of possibilities for dashboarding... but I need to be capable to format numbers and dates in the different charts, mainly in tables...

A must would also be the possibility to place icons in the tables...

How to do that?  wink

Thanks in advance

6 answers

1 accepted

0 votes
Accepted answer

I suceeded to format one column (not necessarely the first one...) following the available doc... but I have tables with more than one numeric table needing formatting and after trying everything including googleling I remain unsucessful...

Your help will be appreciated!  wink

Hi Cuto! First of all, welcome to PocketQuery and thank you for your interest!! In general, your key to success will most probably be custom PocketQuery templates. With these at hand, you'll be very powerful in designing the output of your query. When it gets more advanced, however, you'll require some programming skills in Velocity/HTML/JavaScript/CSS. Have you seen the article on PocketQuery templating? If the information in this and the docs is not enough, we can surely help you with this. In that case we'll need some more information on your query and how your result should look like in the end. Feel free to provide wink. Regards, Felix (Scandio)

This is what I try... but does not work...  sad

 

<script> 
PocketQuery.chart('Table',{
page:'enable',
pageSize:30,
allowHtml: true,
showRowNumber: true,
format: [{
     type: 'NumberFormat',
     column: 2,
     pattern: '#,#00.0#',
     negativeColor: 'red',
     negativeParens: true        
    },{
     type: 'NumberFormat',
     column: 3,
     pattern: '#,#00.0#',
     negativeColor: 'red',
     negativeParens: true        
    }]
});
</script>

Interesting. Could you please provide: (1) your SQL code and (2) describe with examples and the column type how your numbers are stored in your database. And (3): is this MySQL?

 

Hi Felix! It was not possible to reply last Friday as I have reached the maximum replies that the site allows me for the moment...

This is the result I have (copy & paste) in my Google Chart Table using PocketQuery... As you can see the format is only applied in the column (Dotation)...

 LIGNELIGNE_DESCBUDGET_VOTEDOTATIONSAP_COMSAP_PAYSAP_S_PAYSAP_DISP
1L-2103Communications826000470,000.00173308.211000.18172308.03652691.79
2L-238Assurances1139811,396.0000011398

In reply to your questions:

1) SQL ->

SELECT 'L-' || BV.LIGNE AS LIGNE,BV.LIGNE_DESC,BV.BUDGET_VOTE AS BUDGET_VOTE,
SUM(CASE WHEN SD.DOTATION IS NOT NULL THEN SD.DOTATION ELSE 0 END) AS DOTATION, SUM(CASE WHEN SAP.ENGAGE IS NOT NULL THEN SAP.ENGAGE ELSE 0 END) AS SAP_COM,
SUM(CASE WHEN SAP.PAYE IS NOT NULL THEN SAP.PAYE ELSE 0 END) AS SAP_PAY,
SUM(CASE WHEN SAP.RESTE_A_PAYER IS NOT NULL THEN RESTE_A_PAYER ELSE 0 END) AS SAP_S_PAY,
SUM((CASE WHEN BV.BUDGET_VOTE IS NOT NULL THEN BV.BUDGET_VOTE ELSE 0 END) - (CASE WHEN SAP.ENGAGE IS NOT NULL THEN SAP.ENGAGE ELSE 0 END)) AS SAP_DISP
 
FROM ORI_BUDGET_2016_BV BV
LEFT JOIN (SELECT 'L-' || LIGNE AS LIGNE, SUM(DOTATION) AS DOTATION FROM ORI_BUDGET_2016_SD GROUP BY 'L-' || LIGNE) SD ON 'L-' || BV.LIGNE = SD.LIGNE
LEFT JOIN (SELECT 'L-' || LIGNE AS LIGNE,SUM(ENGAGE) AS ENGAGE, SUM(PAYE) AS PAYE,SUM(RESTE_A_PAYER) AS RESTE_A_PAYER FROM ORI_BUDGET_2016_SAP GROUP BY 'L-' || LIGNE) SAP ON 'L-' || BV.LIGNE=SAP.LIGNE
 
GROUP BY BV.LIGNE,BV.LIGNE_DESC,BV.BUDGET_VOTE
ORDER BY BV.LIGNE

2) Data in the database is stored as "NUMBER".

3) It is an Oracle 11g database

 

I need to apply the format '#,##0,#0' and to allign to the right in every numbered column to have my "dashboard" accepted...

 

Thanks again for your support!

Hi Cuto,

Please try to use the $numberTool helper in your Velocity template, it's available again in PocketQuery 2.0.4. Worked for me with this template:

#set($columnsToFormat = ['myColumnToFormat1', 'myColumnToFormat2'])
&lt;table class="aui confluenceTable pocketquery-table"&gt;
	&lt;thead&gt;
		&lt;tr&gt;
		#foreach ($column in $columns)
			&lt;th&gt;$!column&lt;/th&gt;
		#end
		&lt;/tr&gt;
	&lt;/thead&gt;
	
	&lt;tbody&gt;
		#foreach ($row in $result)
			&lt;tr&gt;
			#foreach ($columnKey in $row.keySet())
				#if ($columnsToFormat.contains($columnKey))
					&lt;td&gt;$!numberTool.format("#,000.00", $row.get($columnKey))&lt;/td&gt;
				#else
					&lt;td&gt;$!row.get($columnKey)&lt;/td&gt;
				#end
			#end
			&lt;/tr&gt;
		#end
	&lt;/tbody&gt;
&lt;/table&gt;

Just replace the values in $columnsToFormat with the column names of your columns you want to format.

Let me know if this works!

Regards, Felix (Scandio)

Hi Felix!

 

Yes, your velocity template is working well and I've found the way to align the numbers to the right!  wink

Anyway, it is not possible to have the same working with the Google Charts Table?

Thanks again!

 

Hi Cuto! Honestly, I have to say I don't know. I'd have to experiment with it myself and I currently can't find the time for it. I might do it another time. Or you try to find a way. You should try on the Google Charts page completely without PocketQuery. Try to reproduce the issue as simple as possible. Only when this was achieved, you should consider PocketQuery again. Please let me know about any further progress. You have the chance to contribute to the PocketQuery community wink

Hi Felix! Thanks again!

Yes, I have succeeded in applying format to all the columns using the Google Charts code within Pocketquery. I have also succeeded in calculating totals, in including icons and in applying css... big progress!!! I promise to share code here once I succeeded in what is still left, that is how to block the last row including the totals (I am considering that should be using the handling events of Google Charts but I do not know how to code that... reason because I opened a new post here...) and finally, how to export Google Charts here to pdf for "printed out" reporting... that in my case is essential...

Regards!

 

 

Glad to hear about your progress Cuto! Since Google Charts processes some Ajax Requests and only when the page has loaded, the results of the charts won't be included in PDF exports by definition. I know this is quite sad, but it simply doesn't work. If you require PDF export, you'll need to use something else. You could try to generate charts with the Chart macro around the PocketQuery table. Or switch back to custom PocketQuery templates without charts.

Suggest an answer

Log in or Sign up to answer
Community showcase
Published Oct 31, 2018 in Marketplace Apps

Marketplace Spotlight: Zephyr

Hello Atlassian Community! Each month, we run a series of Spotlights to highlight Marketplace vendors and apps that our team thinks this Community would find valuable. In last month's Spotlig...

355 views 0 1
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you