Timeline chart (gantt chart) functionality on SAP Lumira designer
In this blog post, I have explained how to achieve timeline chart (gantt chart) functionality in SAP Lumira designer by a workaround (applicable for design studio and web intelligence)
After a long analyze of timeline chart component in Lumira (without using third party component), I ended up with having a workaround to achieve this functionality to meet our customer requirement.
This blog post would be helpful for developers and technical consultants.
To show list of projects with a bar/line indicating when a project planned to start and when it is planned to complete as shown in below table.
Points to note:-
- In this example, I’m showing only projects within 10 years timeline.
- We have PPROJECT_ID, PLANNED_PROJECT_START_DATE and PLANNED_PROJECT_END_DATE in our database.
- HANA studio and Lumira Designer used for illustration (though same can be achieved by using Universe, Bex query, Web intelligence)
- To create 22 variables (variables can be more or less based on requirement)
- To customize scorecard in Lumira designer by using variable values
Step 1 in Detail (Creating HANA variables)
To create variables in HANA view (which has all other information) as shown on below picture. Note: only Year is considered from start date and end date (“YYYY/MM/DD” is converted to “YYYY” in all formula by using string function)
Referring above picture, let’s assume that the variable values are, curYear=2019, curYear1=2020, curYear2=2021 …. curYear9=2028 and Project “XYZ” start year is 2020 and end year is 2022.
Variable sdates is starting year of each project
Variable edates is ending year of each project
Now the variable Year1_Flag condition checks if variable curYear value is between start date and end date of project “XYZ”. If condition is true, the flag will be “Y” else “N”.
In this case, the below variable values would be for project “XYZ” is,
Year1_Flag=”N” for 2019
Year2_Flag=”Y” for 2020
Year3_Flag=”Y” for 2021
Year4_Flag=”Y” for 2022
Year5_Flag=”N” for 2023
Year6_Flag=”N” for 2024
Year7_Flag=”N” for 2025
Year8_Flag=”N” for 2026
Year9_Flag=”N” for 2027
Year10_Flag=”N” for 2028
Now we have got flag “Y” for 2020, 2021 and 2022. Hence by using these flags, we need to apply some formatting on scorecard in order to achieve this functionality.
Step 2 in detail (customizing Lumira scorecard to timeline chart/Gantt chart)
1. Open lumira designer and add data source (HANA view which created as said in step1) and add all created variables to data source
2. Add scorecard component into report layout and map only Flag variable (Which provides “Y” and “N” values) to its column and generate initial scorecard. It will look like below.
In design mode
In run mode
3. Now apply below highlighted setting for all column which has flag (Y/N) in scorecard.
4. Un-check “Show text” as highlighted below for all column which has flag (Y/N)
5. Importantly bind Year variable (cur_year, curYear1, curYear2) in order to each column header as highlighted below (Current year to last year)
6. Here I have done my customization and the final output is below.
Note: The same can be achieved in web intelligence and even variables can be created inside webi.
Columns are fixed. It will not automatically be adjusted to maximum date. We need to decide the max number of year/date based on requirement.
As seen in Picture 1.8, I have achieved the functionality as I needed. However we can customize our own settings as we required like colors, bar width, background colors, etc., In addition we can use all other features of scorecard along with this one.
Regards!!! Vijai Muniraj