SSRS Default Date Parameter Functions

Several months ago, I made a list of date functions I commonly used to set the default value of an SSRS Parameter. I put it on our internal network for my colleagues, and today figured I may as well publish it on my blog too.

Function Description
Today
(00:00:00)
=DateSerial(Year(Now()), Month(Now()), Day(Now()))
1st of Current Month
(00:00:00)
=DateSerial(Year(Now()), Month(Now()), 1)
1st of Last Month
(00:00:00)
=DateSerial(Year(Now()), Month(Now())-1, 1)
1st of Current Year
(00:00:00)
=DateSerial(Year(Now()), 1, 1)
Last of Current Month
(23:59:59)
=DateAdd(“S”, -1, DateSerial(Year(Now()), Month(Now())+1,1))
Last of Last Month
(23:59:59)
=DateAdd(“S”, -1, DateSerial(Year(Now()), Month(Now()),1))

As with all things, make sure you test before implementing to ensure it does exactly as you need.

If you have a favorite you use regularly, add it to the comments and I’ll include it in the post.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Rotating Graph Data Labels In SSRS

The default settings for Data Labels attached to graphs can cause issues with overlapping, which in turn reduces readability.

In order to rotate the labels, select your graph, then select a single data label (each of them will be highlighted and affected by any changes). Open the properties window whilst the data labels are selected, and expand the “Labels” title, then expand the “Label” tag in the Labels category. You’ll be presented with the below properties:

Graph Data Label Properties

As you may have guessed, the “Rotation” attribute controls the angle of the label. By default, this is set to zero, which will give you horizontal text.

A value of -90 to 90 can be selected. Any number below zero will swing the end of your text upwards, while any number above zero will tilt the end of your text downwards. The below is the result of using the above properties (-90 degrees):

Improved Graph Data Labels

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Stop X-Axis labels skipping values in SSRS

I came across an issue today involving a chart I’d added to a report. The chart was working as expected, but the values along the X-Axis were unclear, below is a screenshot of the graph with those values:

Graph Axis Label Issue

At a glance, the graph looks fine. Not until you take a good look at the values on the X-Axis do you see that there’s a problem. Spotted it? The values go from 1 to 4, and there’s a bar in between with no way of identifying if this is assigned to 2, 3, or both!

In order to resolve this, I went into the Axis Properties by clicking into the chart, right
clicking on the X-Axis, and selecting “Axis Properties…” at the very bottom of the list. This
will bring up the Category Axis Properties window, as below:

Graph Axis Properties

From this screen, tick “Scalar Axis” to force the axis to span all values that have data. For
example, if you have a chart with an X-Axis value of 1-5 available, and all of the available data falls in either 3 or 5, then 3, 4, and 5 will be displayed along the X-Axis, but not 1 and 2.

The result of ticking this box is now the below:

Graph Axis Label Issue Fixed

Much better! I can now see exactly which value each bar is assigned to. However, this has created another slight issue in that the values 0 and 11 are now displaying, even though the only available values for the X-Axis are 1 – 10. To get around this one, go back into the Axis Properties, but this time go to the second tab – “Labels”:

Graph Axis Labels Properties

Check “Hide first and last labels along this axis”. This will remove the first and last X-Axis
values from the list, so long as no data is assigned to them. In this example, no data will ever be assigned to 0 or 11, and will therefore be hidden – giving me exactly what I want:

Graph Axis Label Issue Fixed

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)