Crooked lines! Create a straight line graph in SSRS when having null or empty data points.

A "simple" report to display product usage for  multiple plants over a date range. Sounds simple enough..  If you look at the attached graph, and the red line ,  the line stops at the dates it does not have readings for, which make the trend line hard to understand. How you can get the software to do this?

 In previous version of SSRS Microsoft reporting tool (2005 I believe) , the software would "calculate the gaps" if you will, automatically; making the display more readable  and easier to understand . When developing using the 2012 version,I had to take the responsibility to calculate the empty points (in sql) to have a straight line where the software insisted on making it crooked and ugly. The base solution uses various cursors to calculate the "missing data points" and this worked greatly for this scenario but it might not work where speed is top priority. For here is how this was pulled off.:

1. First we need a cursor  to insert all possible periods, plants into a temp table
2. A really important step is to define which points will be displayed(plotted to the graph, the squares where there is data) I simply used a case statement to set a flag to 'S" if there was data and the item needed to be display(be plotted) and 'N' if otherwise. Also, we need to calculate the interval between plots that have data. I do it by using a case statement and simply counting the  periods we created above where there is no data . Also for this particular project , there was the need to hide/show some values dependent on whether  a period had been closed or not and since the number of products could be huge I had to create my own color pallet to display the line graph since SSRS just repeats the colors once there are so many lines displayed  to which I will not take into consideration for this post. This refers to the #Temp table you see below.
   
   
3. Now we redefine which records have markers.
   
   
4. Now we calculate the interval value
   
   
5. The cursor to calculate empty points.
   
   
   
      
6. And finally the result.
   

It was pretty laborious to put it together but once I figured by hand- literally with a calculator-  how to calculate the missing empty points, it was just a matter of trial and error until the final project came together.