Hi, Is there a way to create this aggregation automatically, instead of create member my member? I want to aggregate all the sprints with the same start date.
Hi @Gisela_Nogueira,
You can add custom hierarchy that would group sprints by matching string or numeric properties (sprints with the same property value are grouped) in Sprint dimension.
-
First, you might want to import criteria for grouping sprints as a property using additional data import: Additional data import into Jira Issues cube.
For example, import planned start period as string, program increment, or some other attribute indicates which sprints should be grouped. -
Then, in Sprint dimension, chose to add a custom hierarchy: Create reports.
And here is a training video on additional data import and how to build a custom hierarchy from those data:
Presentation slides: Power-up-reports-with-additional-data-import.pdf (5.9 MB)
Please see this community post on a similar use case and solution how to import Sprint properties from REST API:
Best,
Zane / support@eazyBI.com
Here is an example of REST API that would import Week cycle as Sprint property.
This Sprint property will pick the Sprint start date and show it to the Monday of that week, and import the property with this pattern: %yW%U, %b %d %Y, for example, 20W39, Sep 28 2020 - year, week number, and week start date)
To use this code:
-
In Source Data (where you already have Jira as a data source), select “Add new source application”.
-
Chose the option to “import definition” and paste in the code below:
Import definition for new data source
{
"application_type": "rest_api",
"application_params": {
"source_params": {
"url": "http://jira811.internal:8810/rest/agile/1.0/board?type=scrum",
"pagination": "offset_limit",
"incremental": null,
"incremental_stop_import": null,
"authentication_type": "basic",
"username": "admin",
"content_type": "json",
"custom_javascript_code": "\n\n// retrieves all sprints within one board and will go from the last sprint and rank decreasingly closed sprints\nvar sprints = [{\n sprint_id: null,\n sprint_week: null \n}];\nvar allSprints = [];\nvar allSprintsLoaded = false;\nstartAt = 0;\nmaxResult = 50;\nif (doc.type == \"scrum\") {\n\n do \n {\n result = getDocument( \"/rest/agile/1.0/board/\" + doc.id + \"/sprint?startAt=\" + startAt + \"&maxResults=\" + maxResult, {ignoreErrors: \"404\"});\n if (result && result.values ) {\n allSprints = allSprints.concat(result.values);\n allSprintsLoaded = result.isLast;\n startAt = startAt + maxResult;\n }\n }\n while (!allSprintsLoaded); \n \n if (allSprints ) { \n for(var i = 0; i < allSprints.length; i++) {\n var sprint = allSprints[i];\n if (sprint && sprint.originBoardId == doc.id ) {\n \n if (sprint.startDate) {\n date = new Date(Date.parse(sprint.startDate));\n date.setUTCDate(date.getUTCDate() + 1 - (date.getUTCDay()||7));\n sprints.push({\n sprint_id: sprint.id,\n sprint_week: strftime(\"%yW%U, %b %d %Y\", date)\n });\n }\n }\n }\n} \n\n}\nreturn _.uniq(sprints);",
"json_data_path": "$.values",
"offset_parameter": "startAt",
"limit_parameter": "maxResults",
"limit_value": 50
},
"extra_options": {
"regular_import_frequency": null,
"regular_import_at": "",
"time_zone": "Helsinki"
}
},
"source_cube_name": "Issues",
"columns": [
{
"name": "sprint_id",
"data_type": "integer",
"dimension": "Sprint",
"dimension_level": "Sprint",
"key_column": true,
"skip_missing": true
},
{
"name": "sprint_week",
"data_type": "string",
"dimension": "Sprint",
"dimension_level": "Sprint",
"property": "Week cycle"
}
]
}
-
Adjust the REST API source parameters (URL, authentification) to match your Jira instance (Import from REST API)
-
Import data.
-
In the report, Sprint dimension, choose to add custom hierarchy and select property “Weekly cycle” (Create reports).
Here are several reports in eazyBI Demo Training account created based on this custom hierarchy:
Sprints by weeks balance
Sprints by weeks velocity
Sprints by weeks current cycle story points burn-down
Best,
Zane / support@eazyBI.com
Hi, @zane.baranovska,
The code show an error when I import, I think the javascript is not complete, can you post the code?
@Gisela_Nogueira, the given import definition for the new data source already contains the JavaScript code to format the values and some parameters for the REST API. I just tested the import definition and the JavaScript, works as expected.
Where you have placed the “import definition”? And what error do you get?
Hi, @zane.baranovska after changing the informations of my instance, show this error when I click in Continue:
Thanks @Gisela_Nogueira!
It looks like the Community formatter changed a couple of JavaScript fields to URL links. I update the post and fixed the code in the import definition.
And here is the JavaScript part separately that transforms retrieved data in needed format for data mapping:
// retrieves all sprints within one board and will go from the last sprint and rank decreasingly closed sprints
var sprints = [{
sprint_id: null,
spint_week: null
}];
var allSprints = [];
var allSprintsLoaded = false;
startAt = 0;
maxResult = 50;
if (doc.type == "scrum") {
do
{
result = getDocument( "/rest/agile/1.0/board/" + doc.id + "/sprint?startAt=" + startAt + "&maxResults=" + maxResult, {ignoreErrors: "404"});
if (result && result.values ) {
allSprints = allSprints.concat(result.values);
allSprintsLoaded = result.isLast;
startAt = startAt + maxResult;
}
}
while (!allSprintsLoaded);
if (allSprints ) {
for(var i = 0; i < allSprints.length; i++) {
var sprint = allSprints[i];
if (sprint && sprint.originBoardId == doc.id ) {
if (sprint.startDate) {
date = new Date(Date.parse(sprint.startDate));
date.setUTCDate(date.getUTCDate() + 1 - (date.getUTCDay()||7));
sprints.push({
sprint_id: sprint.id,
spint_week: strftime("%yW%U, %b %d %Y", date)
});
}
}
}
}
}
return _.uniq(sprints);
@zane.baranovska
Hi, Thanks, now show the group but when I open the hieraquy don´t show all the sprints but the number in the hieraquy sum all the sprints.
Wrong:
Week cycle should group all the sprints imported with a parameter where the week has the same name. There could be some filtering in the report when you do not see specific members.
Could you check if there are no filter rows option hiding some rows? It seems some sprints do not show up in the report. You can also collapse and expand the Week to get back the members if there are some removed manually from the report.
If you have any problems getting the report work please send the report definition to support@eazybi.com
Daina / support@eazybi.com