CODEWITHSUNDEEP
reporting-servicesgraphsql-server-2008-r2reportbuilder3.0
Colin McNulty
Colin McNulty

Reputation: 135

SSRS: How to reduce the number of data labels shown on a graph?

If I run a report running on SSRS / SQL Server 2008 R2, that charts data over a year, with data labels turned on, it looks like this, which is fine:

Data Labels over 1 year

But when I change the time period of the report to run over 5 years, it looks like this, which is a mess:

Data Labels over 5 years

Can someone tell me if there is a setting or something to change the maximum number of data labels show. You can see that SSRS has automatically filtered out many of the dates along the X axis, so that it looks ok; I want the same to happen with the data labels.

Many thanks.

Upvotes: 1

Views: 5301

Answers (7)

Kyle
Kyle

Reputation: 1

Something to note: If this is for a continuous series you cannot use "RowNumber" as SSRS will only allow distinct values and not aggregates. To limit the value based on a date stamp on the x-axis: Right click series, show data labels, right click the data label in the chart, data label properties, and change the value to something like this: =iif(Minute(Fields!date_stamp_calculated.Value) = 0, Fields!TrailingTotal.Value, Nothing).

This looks for everything once an hour, you could beef up the if statement to include multiple intervals throughout the hour.

Upvotes: 0

Steve Griffiths
Steve Griffiths

Reputation: 11

I wanted to change it to show a data label for every other data point, and used the below code:

Highlight the data point and in the properties box Visible enter

=IIF(RowNumber(Nothing) Mod 2 = 0, True, False)

This worked perfectly for me.

Upvotes: 0

coblenski
coblenski

Reputation: 1189

I wanted to change it to show a data label for every other data point, and used the below code:

Highlight the data point and in the properties box "Visible" enter

=IIF(RowNumber(Nothing) Mod 2 = 0, True, False)

Upvotes: 0

Nahid
Nahid

Reputation: 105

Calculate row counts which suit in your report. This would be used for limitation for complete shown data labels. For Example: (countrows("Dataset1")>20) 20 is the best number of records suits all data labels. Then set another criteria for how many labels which can be shown in high density labels.

For Example: RowNumber("Dataset1") mod 3 = 0

=iif((countrows("Dataset1")>20),iif((RowNumber("Dataset1") mod 3 = 0),Fields!PercentageOff.Value ,Nothing), Fields!PercentageOff.Value )

Upvotes: 4

Boone
Boone

Reputation: 137

I just wanted to share an alternative, as I just ran into this issue myself. For my chart, I have a Series group of "Budget Name", where there are 2 budgets and 1 "Actual". I wanted to display only the value of the Actual, so I used the following expression for the series label data: =iif(Fields!Budget_Name.Value="ACTUAL",SUM(Fields!Amt.Value),Nothing)

Upvotes: 2

Ricardo Vasquez
Ricardo Vasquez

Reputation: 31

There is an option if you are using ssrs 2008 or later, the label visible property can be validated with an IIF or any other logical operator.

Upvotes: 3

Daryl Wenman-Bateson
Daryl Wenman-Bateson

Reputation: 3939

There is no option to limit the quantity of labels.

The only work around I can suggest is to remove the labels and add a tooltip with the value. It requires the user to roll over each point, but results in a usable report.

Upvotes: 1

Related Questions