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?
Thanks in advance
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!
This is what I try... but does not work...
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)...
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!
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']) <table class="aui confluenceTable pocketquery-table"> <thead> <tr> #foreach ($column in $columns) <th>$!column</th> #end </tr> </thead> <tbody> #foreach ($row in $result) <tr> #foreach ($columnKey in $row.keySet()) #if ($columnsToFormat.contains($columnKey)) <td>$!numberTool.format("#,000.00", $row.get($columnKey))</td> #else <td>$!row.get($columnKey)</td> #end #end </tr> #end </tbody> </table>
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 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
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...
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.
It started as any story starts, on a normal, rainy day. Admin meets App, and her name was Klok2, and like any first relationship we were both trying to make it work but neither one knew what...
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!
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