Hi,
I need some assistance writing a query.
I have two fields, Country, and, Supplier.
I need a JQL query which I can use on the dashboard to show how many countries each supplier is in.
Each record is 1 country and 1 supplier.
Issue is also that 1 country can have the same supplier multiple times..
For example if Supplier A supplies to UK, USA, Canada
Supplier B supplies to France
etc.
Greatly Appreciated, thank you.
Community moderators have prevented the ability to post new answers.
Try SQL for JIRA:
select cf.country as "Country", count(*) as "Suppliers" from issuecustomfields cf inner join issues i where cf.issueid=i.id where i.JQL="..." group by cf.supplier
NOTE: In the SQL query above you have to provide a JQL function to filter the issues like (project in XXXX, YYYY). Otherwise it will not work because full scans on the issue table are not allowed.
Then use the complementary SQL for JIRA Reports and Gadgets plug-in to create (in minutes) a nice visualization (charts, tables, etc) for the SQL query.
note please that your custom fields: "country" and "supplier" are columns of the ISSUECUSOMFIELDS table. This is a nice feature of the "SQL for JIRA" plug-in... :)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
JQL is for finding issues, not for processing data.
But, I think there's an easy way to get numbers out - write a filter for "all the issues I am interested in". Use it in a "2 dimensional filter" gadget, selecting country as one axis and supplier as the other.
You'll get a grid telling you where each supplier is active in a country, and on how many issues (I think that's the goal here?)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi, yes I tried that, the issue was with that, is that the Supplier field is not a select list. It is our own type of field which is based on a text field.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This means that the Supplier field does not show and does not allow to be selected in a 2 dimensional statistic filter statistics
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Ah, it's not "statable" then. I think you're going to struggle with this, as text isn't really that countable.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Do you know of a query which can sort of remove duplicates ? e.g. how you have in SQL DISTINCT?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Nope, JQL *just* finds issues.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
By this I mean So that I can remove duplicate supplier records with the same country value, so that 1 supplier per 1 country shows.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Okay, thanks anyway though! :)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Sounds like a good housekeeping thing to do :-) There are some options, but I'm not sure how much they'd help. You can run JQL like "Supplier ~ Acme" and JIRA will try to catch some variations on the word Acme in your suppliers field. I've usually found this useful for housekeeping because it will get a good chunk of the words I'd like to de-duplicate. Won't catch them all, but it's better than reading them one at a time!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yeah housekeeping is always fun isnt it... lol. Unfortunately even stuff like ~ would not work.. Because Supplier names will all be the same.. So regardless they'd all show. But anyway I guess this is an adventure to figure out what to do! Thanks for your help :)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.