SSRS - Dynamic Data Bars

Question

I need to achieve the below reporting requirement -

Report

I need to dynamically increase the width of my data bar based on the start and End date. I understand that data bar requires a metrics value that it plots on the graph. So not sure this could be done using the data bar.

I also tried coloring the background color of the text boxes (for Timeline column) using expressions. Is that the right way?

Thanks, Amrita

Answer 1

I was able to solve the above problem using the below approach.

To start with, I created two calculated fields in my data set derived from the StartDate and EndDate fields from the source -

StartYrMn = Year(Fields!Start_Date.Value)*100 + Month(Fields!Start_Date.Value)

EndYrMn = Year(Fields!End_Date.Value) *100 + Month(Fields!End_Date.Value)

The above would help me compare the date values for the current year month.

Next, in report design region, I created a matrix using GroupName as the Parent group and ActivityName as its Child group. (Alan has descriptively shown this in the above example. Please refer the same.)

Due to SSRS restrictions, I could not create a field to get me all the month names, so i had to hard code all the months. After the above changes the report should look something like this : Report Design

Once the above is done, we need to now write the Fill expressions for each of the Month textbox like below to check if the currentYrMn falls in betwwen the StartYrMn and EndYrMn.

=Switch( Fields!StartYrMn.Value <= Year(Today()) & "01" And 
Fields!EndYrMn.Value>= Year(Today()) & "01" And Fields!GroupName.Value= "Group1",
"DarkBlue", 
Fields!StartYrMn.Value<= Year(Today()) & "01" And 
Fields!EndYrMn.Value>= Year(Today()) & "01" And Fields!GroupName.Value= "Group2",
"Gold")

The above expression is for the January month. Similarly, convert the same for other months.

Lastly, I removed the border lines from the middle of the data cells ( using the Border settings of the textbox) so that my colors appear to be spread across the month range and look like data bars.

With the above approach, my final report would look something like the below -

Final Report

Answer 2

I would approach this using a matrix and set the background of each cell accordingly.

To do this we need a set of dates to work with. In your case, as you only seem to go to month level, my date table only contains months and years.

To start, create a new report and add a dataset with the following query. You will obviously have to adjust this to suit your database but you should get the idea. I'll explain the code shortly.

DECLARE @dates TABLE(Year int, Month int)
INSERT INTO @dates VALUES
(2018,1), (2018,2), (2018,3), (2018,4), (2018,5), (2018,6),
(2018,7), (2018,8), (2018,9), (2018,10), (2018,11), (2018,12),
(2019,1), (2019,2), (2019,3), (2019,4)


DECLARE @t TABLE (GroupName varchar(10), ActivityName varchar(10), StartDate date, EndDate date, RGB varchar(10))

INSERT INTO @t VALUES
('Group 1', 'Activity 1', '2018-01-03', '2018-12-14', '#ED7D31'),
('Group 1', 'Activity 2', '2018-02-03', '2018-06-14', '#AFABAA'),
('Group 2', 'Activity 3', '2018-01-03', '2018-04-14', '#9DC3E8'),
('Group 2', 'Activity 4', '2018-06-03', '2018-07-14', '#2E75B5'),
('Group 2', 'Activity 5', '2018-08-03', '2018-12-14', '#C55B11'),
('Group 2', 'Activity 6', '2018-03-03', '2018-07-14', '#F4B184'),
('Group 3', 'Activity 7', '2018-10-03', '2019-03-14', '#596DF2'),
('Group 3', 'Activity 8', '2009-01-03', '2019-02-14', '#3d7080')

SELECT * 
    FROM @dates d
       LEFT JOIN (
                SELECT 
                    *
                    , (Year(StartDate) * 100) + Month(StartDate)  as StartYrMn
                    , (Year(EndDate) * 100) + Month(EndDate)  as EndYrMn
                    FROM @t
                ) a
                    ON ((d.[Year] * 100) + d.[Month]) BETWEEN StartYrMn AND EndYrMn
    ORDER BY ActivityName, d.Year, d.Month

This first part is our dates table. I've just created this within the dataset but you probably want to have a permanent database table for this.

The second part re-creates your sample data, I've extended this to cross year boundaries (there are some 2019 dates in there now) so it will still work when data does not fall within a single year. I also added an RGB column to store the colours we want (there are other ways to do this but this is the simplest)

The final SELECT statement takes the basic activity data, calculates a StartYrMn and EndYrMn column that just hold the values such as 201812 for December 2018 for example. This is done in a sub-query

We then take our dates table and LEFT JOIN to our sub-query to produce a set of data that contains 1 row for each year/month and activity. You can run the above code in SSMS to see the results.

OK, that's the data side done...

Next, add a Matrix item to your report.

Set Row Groups for GroupName and ActivityName (GroupName needs to a the parent of ActivityName).

Now add columns groups for [Year] and [Month]. Again [Year] should be the parent of [Month]

You report design should look something like this (except for the month expression which I cover next.

If you run the report now you will get month numbers in the column headers so to fix that, change the Month expression to be

=FORMAT(DATESERIAL(2017, Fields!Month.Value, 1), "MMM")

You can ignore the 2017, we just needed to create a date to get the month name from. You could use 1999 it would still work.

Now all that is left to do is colour the background of the cells. Set the background colour of the Data cell to the following expression.

=IIF(IsNothing(Fields!ActivityName.Value), nothing, Fields!RGB.Value)

All we do here is say that if there is no data, leave the cell blank, else set the colour to the RGB value from our dataset.

That's it!

Your final result should look something like this...

If you can't get it to work. I suggest you start with the exact steps above using the dataset I provided and then slowly make changes to fit your database.

Answer 3

you use expression in textBox properties.