reporting-servicespowerbissrs-tablixreportbuilder

Power BI report Builder - Divide overflowing matrix row


I have a dynamic row of attributes associated with customer. Number of attributes for each customer may vary from 3 to n.

I am trying to display it in a row but if number of attributes increase from 3 to 10 data over flows from right to left on a page. For example

Attribute 1 | Attribute 2 | Attribute 3 | Attribute 4 | Attribute 5 | Attribute 6 | Attribute 7

Is it possible to start a new row for attributes after fixed number of columns, lets say 4?

Attribute 1 | Attribute 2 | Attribute 3 | Attribute 4
Attribute 5 | Attribute 6 | Attribute 7

I found similar question Table not overflowing properly - Power BI Report Builder but steps mentioned in answer are not clear.

Edit:

Design view:

enter image description here

Report View enter image description here

TEST

I tried adding colum/row groups but doing that creates a matrix like this enter image description here

Desired View

first 4 attributes to be rendered on 1st row and next 3 attributes to be rendered on second row


Solution

  • The way I would normally do something like this is to calculate the row and column numbers in the dataset query, then you can use these values as the row and column groups.

    The following query creates some sample data, just ID and Attribute and then returns that same data along side the RowN and ColN columns which hold the row and column numbers respectively.

    I've also parameterised this so you can easily adjust the number of generated columns.

    The sample data contains data for 3 unique IDs, each with a different number of attributes.

    Note: the attributes are named such they can be ordered alphabetically, you may have to adjust the three instances of the ORDER BY clause to get the order correct with your real data (if it matters).

    DECLARE @t TABLE (ID int, Attribute varchar(20))
    
    INSERT INTO @t VALUES 
    (1, 'Attribute 01'), (1, 'Attribute 02'), (1, 'Attribute 03'),
    (2, 'Attribute 01'), (2, 'Attribute 02'), (2, 'Attribute 03'), (2, 'Attribute 04'), (2, 'Attribute 05'), 
    (3, 'Attribute 01'), (3, 'Attribute 02'), (3, 'Attribute 03'), (3, 'Attribute 04'), (3, 'Attribute 05'), (3, 'Attribute 06'), (3, 'Attribute 07'), (3, 'Attribute 08'), (3, 'Attribute 09'), (3, 'Attribute 10'), (3, 'Attribute 11')
    
    DECLARE @MaxCols int = 5
    
    SELECT 
        ID, Attribute,
        RowN = (ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Attribute)-1) / @MaxCols,
        ColN = (ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Attribute)-1) - (((ROW_NUMBER() OVER(PARTITION BY ID ORDER BY Attribute)-1) / @MaxCols) * @MaxCols)
     FROM @t
    

    This gives us the following results (clipped for brevity)

    enter image description here

    Now we can create a simple matrix as per the design below. (Note the two row groups and one column group) You can remove the RowN column (but not the group), I left it in for clarity..

    enter image description here

    When we run the report using 5 as the @MaxCols value we get this..

    enter image description here

    and settings the @MaxCols value to 4 gives us this.

    enter image description here