SSRS Line Chart Dynamic Max Y Axis Based on X Axis Value

Question

I have looked a lot of places to see if this is possible, but so far come up empty.

What I am trying to do is make the Y axis of a line chart based on the largest value and smallest value in the X axis. I've thought of how best to ask this, I am simplifying here. There's more to what I am doing but this is the part I can't figure out.

Click to view example: Set Y Axis to X Axis Values

Here is a small sample of the dataset. it's very basic. https://1drv.ms/x/s!AsF_vHid6sbi30XMcIVeA5vqItsF?e=9CmOZk

There is no series group, and the category group is based on week beginning value. The calculation is the sum of referral counts. I did try writing expression to reference the MAX(SUM(Fields!RefCount.Value, "CategoryGroupName")) and various other similar expressions, but since it is out of the scope of the Y axis, it won't work.

Thanks for any help or advice.

can you edit your question to show what the design looks like now including the category and series settings, it's hard to visualise otherwise. Also a sample of the dataset would be useful. — Alan Schofield
Thanks for getting back to me Alan. I am actually going on vacation today and don't have access to my work computer. I will revisit this next week. Appreciate the feedback. — Keith Giannelli
Post a new comment when you get back so I get notified. Have a great time! — Alan Schofield
I'm baaaaaaaaaaaaaaaaaaaack! I added a copy of the chart with illustration of what I am trying to do, and a sample for the dataset, though it's super basic. Hopefully this makes more sense. Thanks again! @AlanSchofield — Keith Giannelli

Alan Schofield Alan Schofield · Accepted Answer · 2020-07-01T17:19:43

I created a random set of data, it changes each time you run the report so good for testing. It adds 50 to all the values so we know we will never get a zero (for testing).

I used the following dataset query

DECLARE @t TABLE (ReferralWeek date, RefCnt int)

INSERT INTO @t VALUES 
('2020-06-21', 50+(rand()*500)), ('2020-06-21', 50+(rand()*500)), ('2020-06-07', 50+(rand()*500)), ('2020-06-07', 50+(rand()*500)), ('2020-03-29', 50+(rand()*500)), 
('2020-06-21', 50+(rand()*500)), ('2020-05-03', 50+(rand()*500)), ('2020-05-24', 50+(rand()*500)), ('2020-06-14', 50+(rand()*500)), ('2020-04-12', 50+(rand()*500)), 
('2020-04-19', 50+(rand()*500)), ('2020-04-12', 50+(rand()*500)), ('2020-06-21', 50+(rand()*500)), ('2020-04-26', 50+(rand()*500)), ('2020-06-21', 50+(rand()*500)), 
('2020-04-19', 50+(rand()*500)), ('2020-06-07', 50+(rand()*500)), ('2020-05-10', 50+(rand()*500)), ('2020-06-21', 50+(rand()*500)), ('2020-05-10', 50+(rand()*500)), 
('2020-05-31', 50+(rand()*500)), ('2020-06-14', 50+(rand()*500)), ('2020-05-17', 50+(rand()*500)), ('2020-04-12', 50+(rand()*500)), ('2020-04-26', 50+(rand()*500)), 
('2020-05-03', 50+(rand()*500))
SELECT * FROM @t

I then created a new report, added line chart, set values to SUM(RefCnt) and Category Group to ReferralWeek.

Next I right-clicked the Category Group and chose "properties" from here I could see the name was "Chart1_CategoryGroup". This is the scope expression we will need to calculate the vertical axis range.

Next I right-click the vertical axis and chose "properties".

I set the interval type to "number" and then set the expressions for the min and max values to the following...

=MIN(SUM(Fields!RefCnt.Value, "Chart1_CategoryGroup"))

and

=MAX(SUM(Fields!RefCnt.Value, "Chart1_CategoryGroup"))

Note we used the category group name as the scope name.

This gets us part of the way but the interval has to be calculated too or the highest number wont; be show on the axis, so if he highest number was 605 then the axis might show 600 with the last point slightly above it.

We can fix this by calculating the interval. It might not 100% with this simple example but should be close enough.

The interval was set to the following expression.

=(
    MAX(SUM(Fields!RefCnt.Value, "Chart1_CategoryGroup"))
    - 
    MAX(SUM(Fields!RefCnt.Value, "Chart1_CategoryGroup"))
)
/ 10

You can adjust how many labels you want by adjusting the '10' divisor.

This gives us the following

BEWARE! By using the smooth line chart and depending on the data, your curve may now fall off the bottom of the chart. Within your requirements, there is nothing you can do other than add some padding to the minimum value or switch to a non-smoothed chart.

Here's an example of what you might get. (the beauty of a random number generator for testing ! )

SSRS Line Chart Dynamic Max Y Axis Based on X Axis Value

1 Answers