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:
TEST
I tried adding colum/row groups but doing that creates a matrix like this
Desired View
first 4 attributes to be rendered on 1st row and next 3 attributes to be rendered on second row
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)
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..
When we run the report using 5 as the @MaxCols
value we get this..
and settings the @MaxCols
value to 4 gives us this.