I have a certain requirement to get issues (bugs, regression, regression-auto) reported by customer (“Customer Reported[Radio Buttons]” = Yes or “CustomerIssueID[Short text]” is not empty) for which the fixversion is not “CWExceptionsNotReproducible” on a year on year basis along with the number issues fixed (resolution not in (“Won’t Fix”, “Won’t Do”, Cancelled, Dropped, Deferred, Limitation, Obsolete))
I have created the report attached herewith
This gives me a result, which is different from what I get if I create the same using Lookerstudio
I did JQL verification and the looker studio data is correct and what I see in eazyBI is wrong, seems like some filter options are getting messed up.
Also, the performance was really bad
Data from eazyBI
Data from LookerStudio
for validation
JQL to see data from JIRA for reported :
Project in (CAMWorks,CAMWorks_Intf) AND issuetype in (bug, Regression, Regression-Auto) AND (“Customer Reported[Radio Buttons]” = Yes or “CustomerIssueID[Short text]” is not empty) and fixVersion not in (CWExceptionsNotReproducible) AND created >= startOfYear(-9)
JQL for resolved :
Project in (CW,CWR) AND issuetype in (bug, Regression, Regression-Auto) AND (“Customer Reported[Radio Buttons]” = Yes or “CustomerIssueID[Short text]” is not empty) AND resolved>= startOfYear(-9) AND resolved <= endOfYear(-9) AND fixVersion !~ CWExceptionsNotReproducible AND resolution not in (“Won’t Fix”, “Won’t Do”, Cancelled, Dropped, Deferred, Limitation, Obsolete)
Note : We wanted to exclude all issues marked with fixversion CWExceptionsNotReproducible from both reported as well as resolved count
Similarly all resolutions marked in (“Won’t Fix”, “Won’t Do”, Cancelled, Dropped, Deferred, Limitation, Obsolete) should not be considered for the resolved list
below is the chart definition
{
"cube_name": "Issues",
"cube_reports": [ {
"name": "Customer Issues",
"folder_name": "TRYOUT",
"result_view": "table",
"definition": {"columns":{"dimensions":[{"name":"Measures","selected_set":["[Measures].[Issues created]","[Measures].[Issues Reported by Customer]","[Measures].[Customer Issues Fixed]"],"members":[]}]},"rows":{"dimensions":[{"name":"Time","selected_set":["[Time].[Last 10 years]"],"members":[{"depth":0,"name":"Last 10 years","full_name":"[Time].[Last 10 years]","calculated":true,"drillable":true,"expanded":true,"drilled_into":false}],"bookmarked_members":[]}],"nonempty_crossjoin":true},"pages":{"dimensions":[{"name":"Project","selected_set":["[Project].[All Projects]"],"members":[{"depth":0,"name":"All Projects","full_name":"[Project].[All Projects]","drillable":true,"type":"all","expanded":true,"drilled_into":false},{"depth":1,"name":"CAMWorks","full_name":"[Project].[CAMWorks]","drillable":true,"key":"CW","parent_full_name":"[Project].[All Projects]"},{"depth":1,"name":"CAMWorks_Intf","full_name":"[Project].[CAMWorks_Intf]","drillable":true,"key":"CWR","parent_full_name":"[Project].[All Projects]"}],"bookmarked_members":[],"current_page_members":["[Project].[CAMWorks]","[Project].[CAMWorks_Intf]"]},{"name":"Issue Type","selected_set":["[Issue Type].[Bug Regression]"],"members":[{"depth":0,"name":"Bug Regression","full_name":"[Issue Type].[Bug Regression]","calculated":true,"drillable":true,"dimension":"Issue Type","expanded":true,"drilled_into":false}],"bookmarked_members":[],"current_page_members":["[Issue Type].[Bug Regression]"]}]},"options":{"nonempty":true},"view":{"current":"table","maximized":false,"table":{}},"calculated_members":[]}
} ],
"calculated_members": [{"name":"Issue CustomerIssueID","dimension":"Measures","formula":"[Issue].CurrentHierarchyMember.get('CustomerIssueID')","format_string":""},{"name":"Issues Fixed","dimension":"Measures","formula":"--Aggregate(\n-- Except([Resolution].Members,\n-- {[Resolution].[Won't Fix],\n-- [Resolution].[Won't Do (10001)],\n-- [Resolution].[Won't Do],\n-- [Resolution].[Cancelled],\n-- [Resolution].[Dropped],\n-- [Resolution].[Deferred],\n-- [Resolution].[Limitation (12)],\n-- [Resolution].[Limitation],\n-- [Resolution].[Obsolete (10004)],\n-- [Resolution].[Obsolete]\n-- }\n-- ),\n-- [Measures].[Issues resolved count]\n--)\n--\nAggregate(\n Filter(\n [Resolution].[Resolution].Members,\n NOT [Resolution].CurrentMember.Name MATCHES \"Won't Fix|Won't Do \\(10001\\)|Won't Do|Cancelled|Dropped|Deferred|Limitation \\(12\\)|Limitation|Obsolete \\(10004\\)|Obsolete\"\n ),\n [Measures].[Issues resolved]\n)\n","format_string":""},{"name":"Customer Issues Fixed","dimension":"Measures","formula":"--Sum(\n-- Filter(\n-- Descendants([Issue].CurrentMember,[Issue].[Issue]),\n-- (\n-- [Issue].CurrentMember.Get('Customer Reported') MATCHES \"Yes\"\n-- OR\n-- (\n-- NOT IsEmpty([Issue].CurrentMember.Get('CustomerIssueID'))\n-- AND [Issue].CurrentMember.Get('CustomerIssueID') \u003c\u003e \"INTERNAL\"\n-- )\n-- )\n-- ),\n-- (\n-- [Measures].[Issues Fixed]\n-- )\n--)\n\n--\nSum(\n {\n [Customer Reported].[Yes],\n Filter(\n [Customer Reported].[Customer Reported].Members,\n NOT IsEmpty([Issue].CurrentMember.Get('CustomerIssueID'))\n AND [Issue].CurrentMember.Get('CustomerIssueID') \u003c\u003e \"INTERNAL\"\n )\n },\n [Measures].[Issues Fixed]\n)","format_string":""},{"name":"Bug Regression","dimension":"Issue Type","formula":"Aggregate({\n [Issue Type].[Bug],\n [Issue Type].[Regression],\n [Issue Type].[Regression-Auto]\n})","format_string":""},{"name":"Issues Reported by Customer","dimension":"Measures","formula":"--Sum(\n-- Filter(\n-- Descendants([Issue].CurrentMember,[Issue].[Issue]),\n-- (\n-- [Issue].CurrentMember.Get('Customer Reported') MATCHES \"Yes\"\n-- OR\n-- (\n-- NOT IsEmpty([Issue].CurrentMember.Get('CustomerIssueID'))\n-- AND [Issue].CurrentMember.Get('CustomerIssueID') \u003c\u003e \"INTERNAL\"\n-- )\n-- )\n-- ),\n-- (\n-- [Measures].[Issues created]\n-- )\n--)\n--\n--\n-- optimized formula\nNonZero(\n Sum(\n Filter(\n Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),\n [Customer Reported].CurrentHierarchyMember IS [Customer Reported].[Yes]\n OR (\n NOT IsEmpty([Measures].[Issue CustomerIssueID])\n AND NOT (\n [Measures].[Issue CustomerIssueID] = \"INTERNAL\"\n OR\n IsEmpty([Measures].[Issue CustomerIssueID])\n )\n )\n ),\n [Measures].[Issues created]\n )\n)","format_string":""},{"name":"Last 10 years","dimension":"Time","formula":"Aggregate([Time].[Year].DateMembersBetween('10 years ago', 'today'))","format_string":""}]
}
Customer Issues Fixed :
Sum(
{
[Customer Reported].[Yes],
Filter(
[Customer Reported].[Customer Reported].Members,
NOT IsEmpty([Issue].CurrentMember.Get('CustomerIssueID'))
AND [Issue].CurrentMember.Get('CustomerIssueID') <> "INTERNAL"
)
},
[Measures].[Issues Fixed]
)
Issues Reported by Customer :
NonZero(
Sum(
Filter(
Descendants([Issue].CurrentHierarchyMember, [Issue].[Issue]),
[Customer Reported].CurrentHierarchyMember IS [Customer Reported].[Yes]
OR (
NOT IsEmpty([Measures].[Issue CustomerIssueID])
AND NOT (
[Measures].[Issue CustomerIssueID] = "INTERNAL"
OR
IsEmpty([Measures].[Issue CustomerIssueID])
)
)
),
[Measures].[Issues created]
)
)
Issues Fixed :
Aggregate(
Filter(
[Resolution].[Resolution].Members,
NOT [Resolution].CurrentMember.Name MATCHES "Won't Fix|Won't Do \(10001\)|Won't Do|Cancelled|Dropped|Deferred|Limitation \(12\)|Limitation|Obsolete \(10004\)|Obsolete"
),
[Measures].[Issues resolved]
)