ssrs: Group field without messing up the table

Question

In a table I have a repeating field that I want to group by but without messing up the table.

The data I have is the following:

and i want to get to this:

As you can see, group A is repeated and it's what I want. The problem is that it groups all the cities A into a single group and the table is messed up. Can you help me?

Answer 1

As per my comment you will need something to order the rows by then it's a rather clunky solution to get what you need. There will probably be a much more elegant way but this will work.

The script reproduces you data, adds a row id and adds a groupid column.

You can use the group ID column to group your data by in the report even if it's not visible.

declare @t TABLE(client varchar(10), product varchar(10), [value] int, city varchar(10))

INSERT INTO @t VALUES
('A', 'ASD', 13, 'A'),
('B', 'QWE', 40, 'A'),
('G', 'SDF', 31, 'A'),
('F', 'ERT', 12, 'B'),
('D', 'DFG', 2, 'B'),
('G', 'GFHJ', 4, 'C'),
('B', 'TY', 13, 'C'),
('V', 'URTY', 29, 'A'),
('C', 'ERT', 33, 'A')

DROP TABLE IF EXISTS #r
CREATE  TABLE #r (client varchar(10), product varchar(10), [value] int, city varchar(10), RowID int IDENTITY(1,1), GroupID int)

-- this just adds a sort order for the data as #r has an identity column
INSERT INTO #r (client, product, [value], city) 
    SELECT client, product, [value], city FROM @t 

-- some variables to track the previous city and group counter
DECLARE @g int = 0 -- counter for row group
DECLARE @prvCity varchar(10) ='' -- previous city

WHILE EXISTS (SELECT * FROM #r WHERE GroupID IS NULL) -- for any rows not updated
    BEGIN -- put the rowid into @r and city into @curCity
        declare @r int = (SELECT MIN(RowID) FROM #r WHERE GroupID is null)
        declare @curCity varchar(10) = (SELECT city from #r WHERE RowID = @r)

        IF @prvCity != @curCity -- if the city changed
            BEGIN 
                SET @g = @g + 1 -- increment the group
                SET @prvCity = @curCity -- set previous city to current city
            END

        UPDATE #r SET GroupID = @g WHERE RowID = @r -- update the temp table
    END

-- final output dataset query
SELECT * FROM #r

Here is the report design including the row groups

and here is the final output