I am importing “PlannedCapacity “ from google sheets mapped to SprintsID and Project in EazyBI. I want to have a report where I can show on a monthly and or quarterly basis the “PlannedCapacity” and Hours spent. project (in rows) and in columns showing capacity, hours spent and Original estimates worked on that specific month using the Time dimension. DO I need to import Monthly capacity per project ? or I can roll up the imported “PlannedCapacity” to month and quarters?
Hello @Zsofia_Kope
Welcome to eazyBI Community! Thanks for posting your question!
I do have a follow-up question regarding your current Google spreadsheet.
You mentioned that you want to map the Capacity against sprint ID.
When you say you want to show capacity by month or quarter - if a Sprint spans through two months (e.g., Jan 15 - Feb 5), how would you expect the capacity to be distributed when you select February as your Time filter in your report?
Best wishes,
Elita from support@eazybi.com
Hi I actually got it by using and MDX to roll up my 2 weeks sprints based on the start date. If the start date is in a specific month to add the sprint to that month. this si created in the SPrint dimension like this: Aggregate(
Filter(
[Sprint].[Sprint].Members,
NOT IsEMPTY ([Sprint].CurrentMember.get(‘Start date’))
AND
Format([Sprint].CurrentMember.get(‘Start date’), ‘yyyy-MM’)= ‘2026-01’
)
)
I do have another question. I have a in JPD PIV-123s and via Idealink I roll up MLSTN linked to it and that is in issues my Hierachy level4. ex. PIV-123 has MLSTN-1, MLTN-2 etc. MLSTN-1 includes all linked epics and work items from different projects. The problem I have I need to filter PIVs based on project groups I have in EazyBI.
1.I need to create a dimension that includes only PIV statuses. SO I can filter only based on PIVs that are in Delivery status. MY PIV status naming convention is different from JIRA issue stauses. and I want ot show the measures: Hours spent, Original estimates (from all issues under it to roll up) + I want to show the Demand hours that is a field on my PIV.
- Goal is stated on the PIV and I want to be able to filter and show based on Goals as well. ROWS: Project, PIVs, Goal. Filter Page: Project, PIV status. Columns: Hours spent, Original estimates and Demand hours. How can I do the above report?
Hello @Zsofia_Kope
Assuming you have successfully built your hiearchy, your goal now is to import your Idea’s Status and Idea’s goals.
Let’s assume that your customfield id for your hiearchy at the Idea level is “customfield_idforidea”. You should replace the ID I’ve used in JS below, according to what you applied when you built the hiearchy.
For PIV/Idea Status:
You will find the dimension to be imported from Issue link field dimensions section in the import options (Advanced settings)
[[jira.issue_link_field_dimensions]]
name = "PIV Status"
source_dimension = "Status"
issue_key_column = "customfield_idforidea"
group = "Linked issue dimensions"
For Idea Goals. Replace customfield_idforidea with the actual ID you chose when building your hiearchy. And replace customfield_NNNNN with your Goals field id.
You will find this field to be imported from the import options -Custom fields section
[jira.customfield_goal_from_idea]
name = "Goal (from Idea)"
data_type = "string"
dimension = true
update_from_issue_key = "customfield_idforidea"
javascript_code = '''
if(issue.fields.customfield_NNNNN) {
return issue.fields.customfield_NNNNN.value;
}
'''
If this does not work, please reach out to us, referencing this post and sharing the site name and account name where you are building this.
Best,
Elita from support@eazybi.com