JIRA Studio tips

I used to work with Atlassian JIRA Studio, which is from technical perspective the most developed project management environment. It provides immensely rich capabilities for iteration planning and for tracking. I can agree that sometimes it's very compicated to use it, since some of features need skillful approach to use them properly. As you can see in Atlassian case studies customers sometimes very happy to have professional environment for their professionals.

Customer's CEO persuaded that JIRA Studio saved up to 20% of time. But I'm pretty sure that with compliant approach you can have even greater advantage. Especially if you are technical professional and not scared with exploiting it.
From management perspective the most informative thing is your dashboard. JIRA Studio is quite tolerant in providing information to everybody in team, but to collect it effectively is really important. And dashboard is either your favorite place already or... you should take care to make it your favorite place.

So, pay some attention to the switch on the left top. Managing dashboard allows you adding and customizing different portlets. Many of them are useful, but first thing you have to do is to remove surplus: keep here only information about 1-2 projects you really interested in.
After you have released space you may want to populate it with useful information: It's better to replace projects portlet with single project portlet:

My favorite portlets are
Created vs Resolved Issues - this very good overall statistic for any project. Just try to guess how deep is rabbit's hole.
Average Time in Status and Average Age - This allows you to define to keep in balance QA and development teams.
Bamboo Plan Summary - yes, sure I always can view Bamboo's dashboard, but how good is to have everything on one page.
Recent Changesets - good monitoring tool if developers lean to demonstrate their laziness.

The disadvantage is that all this things (that belong to unexplored functionality) is badly supported and extremely slow. So after your Studio project exceeds 3000 issues you have to wait very long before you'll get dashboard statistics. Fortunately, dashboard can be interrupted anytime and there are workarounds to get summary you need.
The key is to use {sql} and {sql-query} tags. This is not only very flexible mechanism to retrieve information from JIRA, but also unrelenting security violation (oops ...) This queries allows you to get any info directly from database. Even if it's restricted accordingly to security rights. For instance if you are restricted to view document named Server access policies. All you need is to create page and put this wiki markup:

{sql:dataSource=ConfluenceDB|output=wiki|table=false}
select bc.body from content c inner join bodycontent bc on c.contentid = bc.contentid
where c.title = 'Server access policies'
order by c.version desc limit 1
{sql}

Where ConfluenceDB is database name where all wiki pages are contained.

But how to get information about Db objects? Well, you have to grow in postgre and learn JIRA database objects. The best way is too post this wiki page for everybody's disposal

Let's use [DB Schema|http://confluence.atlassian.com/display/JIRA/Database+Schema] and [PostgreSQL|http://www.postgresql.org/docs/manuals/] to retrieve information from JIRA Studio.

h5. How to get all databases
{sql:dataSource=JiraDS}
select datname from pg_database where datistemplate <> 't'
{sql}

h5. How to get all tables? {run:autorun=false|hideRun=false}
{sql:dataSource=JiraDS|output=wiki}
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' and tablename not like 'sql_%'
order by tablename
{sql}
{run}

h5. How to get all table columns? {run:autorun=false|hideRun=false|replace=tableName:jiraissue}
{sql:dataSource=JiraDS|output=wiki}
SELECT attname , pg_type.typname, atttypmod FROM pg_attribute, pg_type WHERE
typrelid=attrelid AND typname = '$tableName' and attname not in ('tableoid', 'cmax', 'xmax', 'cmin', 'xmin', 'oid', 'ctid');
{sql}
{run}

h5. How to get all tables from Confluence? {run:autorun=false|hideRun=false}
{sql:dataSource=ConfluenceDS|output=wiki}
SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg_%' and tablename not like 'sql_%'
order by tablename
{sql}
{run}

h5. How to get all table columns from Confluence? {run:autorun=false|hideRun=false|replace=tableName:content}
{sql:dataSource=ConfluenceDS|output=wiki}
SELECT attname , pg_type.typname, atttypmod FROM pg_attribute, pg_type WHERE
typrelid=attrelid AND typname = '$tableName' and attname not in ('tableoid', 'cmax', 'xmax', 'cmin', 'xmin', 'oid', 'ctid');
{sql}
{run}

For instance if you wish to know how many tasks assigned to your people and what are those tasks let's use this wiki markup

Opened tasks:
{sql:dataSource=JiraDS|output=wiki}
SELECT pkey, issuestatus, summary, assignee, reporter FROM jiraissue where
assignee in ('john', 'viktor', 'ashoka', 'kolyan', 'boss', 'vlado', 'kim')
and issuestatus <= 3
{sql}

How many tasks assigned with references to people:
{sql:dataSource=JiraDS|output=wiki}
SELECT '[~' || assignee || ']' as Assignee, Count(*) FROM jiraissue
where assignee in ('john', 'viktor', 'ashoka', 'kolyan', 'boss', 'vlado', 'kim')
and issuestatus <= 3
group by assignee
{sql}

The latter part is undocumented functionality of JIRA, so it might be changed if JIRA Studio is updated.
JIRA Studio is quite avarice to give information about itself. To get all information you have to trigger some exception inside: This differs from version to version but in most cases errors are connected with issue navigation. Fresh errors can be retrieved from here

There are many more tricks to make JIRA Studio to a dream collaboration tool.

Grateful acknowledgments to Dmitra and Mykola R. who helped me very much in collecting information about JIRA Studio tips and tricks.