CODEWITHSUNDEEP
sqlsql-serverreporting-servicesssrs-2008
Si8
Si8

Reputation: 9225

How to add column in a SSRS table based on criteria

I am trying to create a SSRS report which has a table to list some data pulled from a query. The current table in SSRS looks like this:

LAST NAME     FIRST NAME   TITLE    START          END
morrow        diane        pcp      12/15/2009     1/15/2011
morrow        diane        pcp      5/15/2011      6/12/2013
morrow        diane        pcp      7/9/2013       12/11/2014
doe           jane         obgyn    10/12/2013     9/12/2014
ainswor       michael      opt      3/14/2008      4/23/2011
ainswor       michael      opt      6/6/2012       9/2/2014

My SQL query just gets each record and adds it as a row.

What I am looking to do is, in my SSRS table, I would like to use a function and expand the START and END column based on how many times each record appears, so for example, I would like to modify my SSRS table to display like the following:

LAST NAME     FIRST NAME   TITLE    START          END           START       END         START       END
morrow        diane        pcp      12/15/2009     1/15/2011     5/15/2011   6/12/2013    7/9/2013    12/11/2014
doe           jane         obgyn    10/12/2013     9/12/2014
ainswor       michael      opt      3/14/2008      4/23/2011     6/6/2012    9/2/2014

Is that possible in SSRS? Or do I have to modify the SQL query?

UPDATE:

What I have so far in my Matrix: enter image description here

The output is this: enter image description here

For the last entity there are two entries:

DateOfLastCredentialing      CredentialingExpiration
2007-12-24                   2010-12-23
2013-10-14                   2014-04-24

But as you can see it is only showing one.

How do I fix it so it shows both or as many each entity has?

Upvotes: 0

Views: 2519

Answers (1)

kyzen
kyzen

Reputation: 1609

You can accomplish the dynamic columns in SSRS by using a Matrix (a type of Tablix), though you may need to modify your query so that you have a convenient field to create your column grouping on. If you've never created a matrix in SSRS before, you might consider using the wizard (Visual Studio Project = Add New Report, Report Builder = Insert Table Wizard), and putting your start/end date grouping field into the Column groups, and whatever other fields you need into the row groups. Take a look at the table that the wizard creates to get an understanding of how the column grouping behaves.

Upvotes: 2

Related Questions