SSRS -- Create Multi Chart by Project with 3 charts per row and infinite rows ifs necessary . (not subreport)

Question

I have a report with the differnet projects and with columns of values, etc. The thing that i'm looking for and i failed many times is i want to create a auto multi circle chart by project having only 3 charts per row and infinite rows depending of the projects that i have.

I have taken a look to this example but it doesnt work in me report Dynamic control of number of charts in SSRS reports

Example

1. This is the visual chart example for one project Example Project A

What i want to take, is an automatic multi chart that copy and paste this example chart for each a project creating a grid with 3 columns (to limit the width of the SSRS) and the necessary rows to complete all projects

2. Result that i want Example multichart

Can someone help me on this? i'm stuck on this

Answer 1

You can do this without using subreports. You first determine a row and column for each project and then use a matrix to display the results.

I've mocked up some data for the purpose of this example which you will have to update to suit your needs.

DECLARE @t TABLE (Project varchar(10))
INSERT INTO @t VALUES ('A'), ('B'), ('C'), ('D'), ('E'), ('F'), ('G'), ('H')

DECLARE @prjData TABLE (Project varchar(10), N INT, U INT, C INT, S INT)
INSERT INTO @prjData  VALUES 
    ('A', 10, 20, 30, 40),
    ('B', 11, 21, 31, 37),
    ('C', 12, 22, 32, 34),
    ('D', 13, 23, 33, 31),
    ('E', 14, 24, 34, 28),
    ('F', 15, 25, 35, 25),
    ('G', 16, 26, 36, 22),
    ('H', 17, 27, 37, 19)

SELECT pd.*, rc.iCol, rc.iRow FROM @prjData pd 
    JOIN (
        SELECT 
            *
            , iRowNumber = ROW_NUMBER() OVER(ORDER BY Project)
            , iCol = ((ROW_NUMBER() OVER(ORDER BY Project)-1) % @MaxCols)
            , iRow = CAST(((ROW_NUMBER() OVER(ORDER BY Project)-1) / @MaxCols) as INT)
            FROM @t
    ) rc on pd.Project = rc.Project

The inner query (alias rc) works out the row and column number for each project and then we just join that to your existing data. table variable @t will be replaced by your own table containing a list of projects.

@MaxCols is a simple variable that you can use to adjust the number of columns if you need to, or your can just hardcode this value. In the output below I used '3'. I've not declared this in the query as this will be passed from the report as a parameter.

N, U C and S are to represent your 4 values in your chart.

This gives us the following results.

Now we have our dataset, add a matrix control, drag iRow from the dataset fields to the "Rows" placeholder, iCol to the "Columns" placeholder and in the "Data" placeholder insert a pie chart.

Resize the Chart cell to suit your needs...

Now click the chart and set the values, series etc as desired. Set the chart title to the Project field (in this case it's just a letter)

You report design will look something like this..

When you run the report you should get something like this...

Here is the same report again with MaxCols set to 4

Finally you can remove the redundant rows/columns if required