Dynamic Reference Line (for Date) in Webi Report

[mhmohammed]

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 6: Change the color of Dot 1, Dot 2,... Dot 11 (as many Dot variables we have) to Black (or any other color you want the Reference Line to be), using the Format Data Series option as below. Right click on each of the Dot variable from the Chart legend, and select Format Data Series.

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.