How do I repeat columns of data in SSRS?

fcsIT
fcsIT used Ask the Experts™
on
Does anyone know how to make SSRS repeat columns based on data?

Specifically, referring to the attached screenshot, I need the Date and Hrs. columns as shown in the screenshot to repeat based on how many times a given client has had an appointment for whatever specified date range (usually a month).  I have the query from my predecessor who built the attached report using Python, but I don't know Python, and major changes have to be made to it, so I'm recreating it in SSRS, so I know the query returns the right data.  I just have to get SSRS to format it correctly.

Any help is very appreciated!!

Report example
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Project Leader
Commented:
Hello,

Here is an example.
Suppose you have a table "TestTable" with below structure and data:

TestTable (clientname varchar(100),
    birthdate smalldatetime,
    DateField date,
    HrsField time)

insert into testtable values ('first', '1977-03-10', '2012-04-12', '9:30'),
('first', '1977-03-10', '2012-04-12', '10:30'),
('first', '1977-03-10', '2012-05-12', '9:30'),
('second', '1977-03-10', '2012-04-12', '9:30'),
('second', '1977-03-10', '2012-06-12', '9:30')

1. Add a new report with below query:
Select clientname, birthdate, DateField, HrsField,
      'Visit ' + convert (varchar, Row_Number() over ( partition by clientname order by clientname) ) as VisitNumber
from testtable

Note that I have added a running number of visit per client (VisitNumber in the output). If you have a similar column already in the table, you may use that instead of this computed column.

2. Choose "Matrix" as Report style.

3. Add Client Name and Birth Date (and any other static column that you want to display) to "Rows" section.

4. Add DateField and HrsField to "Details" section.

5. Add VisitNumber to "Columns" section.

6. Complete the wizard and change column lables as you want.

If you want to merge adjacent cells, select those cells, right click the selection and choose "Merge Cells".
To split merged cells, choose "Split Cells".

Thanks,
Harish

Author

Commented:
I'm working to add the over (partition by...) statement to the SQL behind the report, but continue getting errors saying "The multi-part identifier 'columnName' could not be bound."

Any ideas?

Author

Commented:
Sorry, disregard that comment.  I fixed it.  I'm still working on your suggestion for the report.  (Thanks for it too by the way!)

Author

Commented:
You sir, are a genius!!!  Thank you!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial