reporting services - SSRS report with column group is huge and mostly empty -
i can't find way report want. data has form:
varchar clientname datetime calldate varchar callreason
i'm trying display in following fashion ( "|" represent column in report):
| client 1 | client2 | client 3 |more client columns |01/02/14 | client saying blah|04/07/14 | printer not working|01/01/14 | testing| |05/06/14 | keyboard died |05/07/14 | still not printing | | |08/07/14 | more complaining | | |
what have right kinda same thing, every row in database ends different row in report, creates gigantic report full of empty cells. data simple query select * thetable calldate>somefilter
so actual question be: how can group data in order have column group details section doesn't group other columns' details.
i want : (note: random copy-pasted data, actual data have duplicate dates not text)
i have (the report goes further down , right):
here did , think require.
sample data ..
create table sorep1 ( clientname varchar(30) ,calldate datetime ,callreason varchar(100) ) insert sorep1(clientname, calldate, callreason) values ('client 1', '2014-08-01', 'some reason 1') , ('client 1', '2014-08-02', 'some reason 2') , ('client 1', '2014-08-03', 'some reason 3') , ('client 2', '2014-08-02', 'client 2 reason 1') , ('client 2', '2014-08-04', 'client 2 reason 2') , ('client 3', '2014-08-01', 'client 3 reason 1') , ('client 3', '2014-08-03', 'client 3 reason 2')
added dataset retrieve data include row number based on client , date
in picture below query ..
select row_number() on (partition clientname order calldate) rownum , clientname, calldate, callreason sorep1;
add matrix object display , row group on rownum column, column group on client name.
i had format date column display date only. format property = mm-dd-yyyy
the result ..
Comments
Post a Comment