CODEWITHSUNDEEP
sql-servervisual-studio-2010reporting-services
Mark Micallef
Mark Micallef

Reputation: 2788

How can I get SSRS to create subheadings?

The Problem

I'm building an SSRS report which requires regular group headings on certain rows. The data returned from the database query includes a column with a bit flag indicating which rows need to be treated as group subheadings.

Here's a snippet of the source data:

enter image description here

Note the IsGroupHeading column, where the flag is set to 1 on the first row ("0401").

I want to produce output which looks like this Excel mockup:

enter image description here

So every time the report encounters a row where IsGroupHeading equals 1, it generates a bold group heading row followed by a row with column headings.

What's Happening

I've tried creating a row group in SSRS with the expression =Fields!IsGroupHeading.Value = 1 but I get unexpected results: (1) Only the first group heading is treated specially, and (2) the group heading row is repeated underneath the heading. The result looks like this:

enter image description here

Notice that the "0401" row is repeated under the group heading. In addition, only the first group heading ever gets this special treatment. The report simply ignores subsequent group headings and renders them as normal data rows.

The Question

I've spent hours trying to get this right and this is the closest I've been able to get it and my googling on row groups turns up pages mostly about creating subtotals, so I'm throwing this one out to the community hoping some SSRS experts can help me with this.

Upvotes: 1

Views: 1500

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

I'm going to assume that you're doing this in SQL and that all tariff numbers start with the group header tariff number (in this case, 0401).

Let's say your SQL currently looks like this:

SELECT TariffNumber, RowDescription, TariffRate, IsGroupHeading
FROM Tariffs

What we want to do is join this table on itself to give the group TariffNumber and RowDescription columns on each row to enable us to group on it. We also want to exclude the GroupHeader Tariff from the Details rows. So we get something like this:

SELECT TariffGroup.TariffNumber AS GroupNumber, TariffGroup.RowDescription AS GroupDescription, 
  TariffDetail.TariffNumber, TariffDetail.RowDescription, TariffDetail.TariffRate
FROM Tariffs AS TariffDetail
INNER JOIN Tariffs AS TariffGroup ON TariffGroup.TariffNumber = Left(TariffDetail.TariffNumber, CharIndex(TariffDetail.TariffNumber, '.')-1) AND TariffDetail.IsGroupHeader = 0

Now you just need to group on GroupNumber and you're done.

Upvotes: 1

Related Questions