CODEWITHSUNDEEP
reporting-servicesssrs-2008-r2
Jatin
Jatin

Reputation: 45

How to have a custom date in SSRS chart?

I am having a column chart and on its horizontal axis I have date. I want to have custom way of displaying date. The month should appear only once with the date and it should reappear only when the month changes. Below is the snapshot of what I wish to do. The available date options does not have it, so I am assuming, will have to put custom formula. enter image description here

Upvotes: 0

Views: 1089

Answers (1)

R. Richards
R. Richards

Reputation: 25161

SSRS out of the box can get you very close. You can group your values on the X axis. Below is what that looks like in design mode. Notice the 2 rows in the Category Groups pane, off to the right. (Here one shows <Expr>, the other day).

enter image description here

The first row in this pane groups the second row. When you preview/run the report, whatever adjustments you make in the first row will show just below the labels along the X axis. Preview at the end.

Since you only want to show the month name abbreviation in the grouping, set the Group expression, and Label value to the following expression. This just gets the first 3 character from the month name for the given day.

=Left(MonthName(DatePart(DateInterval.Month, Fields!day.Value)), 3)

Enter those using the Category Group Expression dialog (below). You get to this by clicking the down arrow on the <Expr> row above, and choose Category Group Properties...

enter image description here

enter image description here

Open the same dialog, but for the day row in the Category Group pane, and use the following expression for the Label. This just shows the day number.

=DatePart(DateInterval.Day, Fields!day.Value)

While still in that dialog, go to Sorting, and make sure day is being used. We don't want to sort by the day number, it will mix the months together (not good).

Adjust the Y axis labels the way you want (by thousands), add data labels, and formatting. In preview mode that report should look something like this. Colors will vary.

enter image description here

Now, the month abbreviations are centered in their group. I don't know if that is a big problem for you. Still, looks okay, it will work across more than 2 months, and still looks very presentable.

Hope this helps! May be a good start in the right direction.

Upvotes: 1

Related Questions