eazybi - Sum of values

[JIRA Admins]

Hi all,

we are looking into buying eazyBI, and it relly looks great. I am busy runnign a test version with an export of my project data. This project includes a custom field made up of 2 dynamic pulldowns for users to choose what kind of advertising material was created, for example STORE MATERIAL > POSTER.

I would now like to run a report over time to see how many of each type of material were produced. So far I have only managed to get a list of all issues and the type of material produced printed next to it. But I really want the types of material on the left, and the number produced over time, ideally sub-divided. Hope I am making sense and someone can help me.

I know this is the Atlassian board and not the eazyBI platform, but I have already managed to quite a lot of eazyBI relevant and helpful information here.

Thanks,

Luan

[Pablo Beltran]

I know this is the Atlassian board and not the eazyBI platform

Yes, it looks like a very generic JIRA question.

Have you looked at the SQL for JIRA plugin?  It brings standard SQL wrapping the JIRA API, so this kind of queries are trivial by using agrregation functions SUM, COUNT, etc.

Your query would be similar to:

select cf.types_of_material as "Material", count(cf.types_of_material) as "Units" 
from issues i inner join issuecustomfields cf on cf.issueid = i.id
where i.JQL='...'
group by cf.types_of_material
order by "Units" desc

You have to provide a i.JQL='...' condition like the projects you want to report,  i.e  

i.JQL='project in (PROJA, PROJB)'

or wahtever other JQL condition.

Finally, if you want to create a nice visualization for the query above you may use the complementary SQL for JIRA Reports and Charts free plug-in.

[Pablo Beltran]

I forget to mention that SQL can come back to JIRA as JQLs. For example, you might want to view on the JIRA Issue Navigator the materials with more units than some threshold or having no units like in the example query below: issue in sql(" select cf.types_of_material as "Material", count(cf.types_of_material) as "Units" from issues i inner join issuecustomfields cf on cf.issueid = i.id where i.JQL='...' group by cf.types_of_material having "Units" = 0 ") If you save it as filter it could be reused by the other JIRA users. It would list the issues that might require some special attention, for instance.