Aggregate Sprint By Name

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.

  1. First, you might want to import criteria for grouping sprints as a property using additional data import: Additional data import into Jira Issues cube - eazyBI for Jira.
    For example, import planned start period as string, program increment, or some other attribute indicates which sprints should be grouped.

  2. Then, in Sprint dimension, chose to add a custom hierarchy: Create reports - eazyBI for Jira.

And here is a training video on additional data import and how to build a custom hierarchy from those data:
Screenshot 2021-05-10 at 17.06.36
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:

  1. In Source Data (where you already have Jira as a data source), select “Add new source application”.

  2. 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",
      "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 spint_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 spint_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": "spint_week",
      "data_type": "string",
      "dimension": "Sprint",
      "dimension_level": "Sprint",
      "property": "Week cycle"
    }
  ]
}
  1. Adjust the REST API source parameters (URL, authentification) to match your Jira instance (Import from REST API - eazyBI)

  2. Import data.

  3. In the report, Sprint dimension, choose to add custom hierarchy and select property “Weekly cycle” (Create reports - eazyBI for Jira).

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: