Technical Articles
Dynamic Reference Line (for Date) in Webi Report
One of the BI folks I know was wondering if there was a way to create reference line for Date in Webi, for one of his requirements, with a way to select/enter a Date to be used. Below is the result (& workaround) I suggested, which worked like charm.
Sample data used
Step 1: Create a report with the above data set as source from Excel (we’ve 2 columns in the data set, Date and Case Count)
Step 2: Create the below variables
- Input Reference Date = “” (its a blank Text variable, we’ll create an Input Control type Entry Field on it, so users are able to enter a Date, for which a reference line is needed.)
- Reference Date =ToDate([Input Reference Date];”MM/dd/yyyy”) (This to convert the Input Reference Date from Text to Date format, so it can be used in the Line Chart)
Next we’ll be creating a bunch of dummy variables, which will help us to create that Reference Date Line in the Chart. Depending on the max value on Y axis and how the Reference Date Line needs to look like, you should create less or more Dot variables.
- Dot 1 =If([Date]=[Reference Date]) Then 100
- Dot 2 =If([Date]=[Reference Date]) Then 300
- Dot 3 =If([Date]=[Reference Date]) Then 500
- Dot 4 =If([Date]=[Reference Date]) Then 700
- Dot 5 =If([Date]=[Reference Date]) Then 900
- Dot 6 =If([Date]=[Reference Date]) Then 1100
- Dot 7 =If([Date]=[Reference Date]) Then 1300
- Dot 8 =If([Date]=[Reference Date]) Then 1500
- Dot 9 =If([Date]=[Reference Date]) Then 1700
- Dot 10 =If([Date]=[Reference Date]) Then 1900
- Dot 11 =If([Date]=[Reference Date]) Then 2100
Step 3: Create an Input Control on the Input Reference Date variable, with the Entry Field, as show in the below images.
Step 4: Create a Line Chart by assigning data as shown below (result Chart is shown as well).
Step 5: Update the Chart Title as needed
Step 7: Hide the Chart Legend
Step 8: Test the Reference Date Line by entering a different date in the Input Control (ex: 9/15/20). This is what it looks like, eventually.
Hope this helps some of you in the future! Please feel free to leave feedback, comments, and/or point to any errors in the blog, so I can fix them.