JQL in PowerBI

[Inna S]

Hi, 

I'm facing a strange issue where a functioning Jira Cloud API call with JQL (in Jira UI, in the browser) fails in PowerBI on specific issue types only.

API endpoint is rest/api/3/search/jql?.

The JQL is simple:

?jql=type = "custom type name"

and it works fine in the browser, for example. 

It also works fine in PowerBI.

Then I change the issue type to another one, say "other custom type name".

This also works with the browser - brings relevant issues, but brings zero entries in PowerBI.

This happens with some custom issue types but not others. 

The issue type names consist of 2 or 3 words, - some work, others do not. 

Even the same issue type sometimes ok and sometimes not.

There are issues of the type in question in the given project and they are listed when query is less specific. 

I tried the issue type id, but this did not help either.

I tried using "type not in (<list of other types>)" and this also breaks on one of the other types. 

Any other ideas? 

Thank you all for looking into this.

[Inna S · Accepted solution]

I found the root cause of the problem: the scrip that collects the paginated API output.

Solution: fix the bug in the code.

Workaround that is good for my use case: reduce the page size to be lower than the total of the issues returned by the API call.

[Akash Singh]

@Inna S Are you using any PowerBI connector app or pulling in Jira data to PowerBI with the help of a custom script?

If you are using a Jira app which act as a PowerBI connector then I would highly suggest you to reach out to their support team and explain them the issue.

I couldn't see any problem with your usage of JQL, it is in adherence with the JQL Type field usage.

If you are using a custom script to fetch this data then I think the problem might be with URL encoding, try passing the JQL as parameter instead of appending it directly to the query URL.

[Inna S]

Hi @Akash Singh , 

not using any connector.

I wrote a short code in the PowerBI Query language and it works ok with one of the custom types. So I assume there is no problem with the code itself, including the url format.

The problem is with a couple of other custom issue types: Jira does not return any issues.

When no type is specified in the query and it only contains, say, project name and status, the issues of these problematic types are returned by Jira all right. 

There is no error, just empty result.

[Inna S]

Ok, found the problem:

the output of the API is paginated so the script does the collection of all the pages.

For this particular issue type, the page size is > total issues returned.

So the script, that does not account for this use case fails to produce the very first output page if it is the only one. 

Reducing the page size of the API call to the number, lower than the current total of the issues of this type resolved the problem.

As the number of issues is ever growing, I can live with this bug in the script. Anyone using more refined queries that might bring a low number of issues should pay attention to the processing of the paginated API result.

Thank you!