Date Labels on x axis in SSRS Line Chart

Hi All,

What is the best optimised options to display the date labels with bi-weekly intervals starting date Date from one month to three months future from the current date?

As per above requirements, I have built the below line graph query and graph, currently it only displays the date labels as per data but I need to display date labels with bi-weekly intervals starting date from one month to three months future from the current date. Also another issue found, if there are few more date labels then does not show all labels on horizontal axis.

Select


/* Set Week End date as Sunday */

DATEADD(DAY, 7 - DATEPART(WEEKDAY, t.TaskBaseline0FinishDate), CAST(t.TaskBaseline0FinishDate +1 AS DATE)) as[WeekEnd]

,count(t.TaskBaseline0FinishDate) as Baseline
,count(t.TaskFinishDate) as Finish

from
MSP_Task_UserView t  INNER JOIN MSP_Project_UserView p on 
p.ProjectUID=t.ProjectUID
Where 1=1
AND t.ProjectUID='5D3285EE-B699-4718-B85D-53DD14A37DE5'
Group by DATEADD(DAY, 7 - DATEPART(WEEKDAY,

t.TaskBaseline0FinishDate), CAST(t.TaskBaseline0FinishDate

+1 AS DATE))
Order by DATEADD(DAY, 7 - DATEPART(WEEKDAY,

t.TaskBaseline0FinishDate), CAST(t.TaskBaseline0FinishDate+1 AS DATE))

Thanks in advance for your help.

July 27th, 2015 12:37pm

Hi Badal,

Based on my understanding, the dataset contains five records. You want to display x-axis labels with bi-weekly intervals, right?

In your scenario, you can open Horizontal Axis Properties, select Scalar for Axis type. Then specify the Minimum and Maximum value for axis range. Specify interval as two weeks. Please refer to screenshots below:

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu

Free Windows Admin Tool Kit Click here and download it now
July 28th, 2015 3:32am

Thanks Qiuyun for your response.

One thing I have missed to mention that I also need to add the Vertical Stripline based on current date. I can add the strip line without any issues to above solution but cannot display the todays date using the title property of the chart Stripline.

Do you know what could be the issue? Thank you.

NB: Report Parameter shows the current date based on date function expression now()

July 28th, 2015 8:50am

Hi Badal,

You want to display a strip line based on current date and display corresponding title as returned by now() function, right?

As we tested in our environment, the strip line title specified by parameter value can display in the chart. You can select the horizon axis, then open ChartStripLine Collection Editor. Please refer to screenshots below:

If you have any question, please feel free to ask.

Best regards,
Qiu

Free Windows Admin Tool Kit Click here and download it now
July 29th, 2015 2:13am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics