2

I have a query that returns the following fields:

GrantCode

EmplID

EmployeeFullName

AssignedUnit

DaysOff

HourlyRate

CalendarEventStart

CalendarEventEnd

NumberOfHours

StatuteNumber

ViolationDescription

I want to have a stepped SSRS report that shows: enter image description here the calendar start time, end time and number of hours under the employee name info and then another grouping that shows the citations that were issued during the time period. I can't quite figure out how to get the SSRS groups set up to display in this manner.

I currently have all the data in a single query/data set but can separate it if that will make the reporting easier.

Any suggestions? Thanks! Leslie

Leslie
  • 3,604
  • 7
  • 38
  • 53
  • you could build a sub report and use the EmpID as the toggle and the argument to the sub report that breaks out the detail. – briskovich Dec 19 '18 at 23:01

1 Answers1

4

Add a tablix to your report. Put in the tablix the fields Citation Number, Statue Number, Violation Description, Number of Citations. Then go to Row Groups > Add Group > Parent Group chose for group by the EmplID and check the Add group header, then hit OK.

Afterwards click on the most left column in your tablix and chose Insert Column > Inside Group - Right. Do this a few times and put in these colums the fields EmployeeFullName, Assigned Unit, HourlyRate, DaysOff, StartTime, EndTime.

Then go to your Row Groups and click on the drop down by (Details) and chose Group Properties > Visibilty and check Display can be toggled by this report item:. Here you have to chose the name of the textbox where the EmplID field is. Now the detail data get toggled by the EmplID.

If you want other data to display just add another column and put your fields or expressions in this column. A new column before the two dotted lines is on the group level and column after the two dotted lines are for the detail section.

Strawberryshrub
  • 3,301
  • 2
  • 11
  • 20
  • Thanks so much!! I will go try this right now :) ps I love your username! – Leslie Dec 20 '18 at 14:48
  • Thanks. Did the job for me. The only thing I will add is that I am new to Report Server and so I did not know how to add a tablix. Tablix is a table, which is available in the toolbox. If you were new to this like I was then you will also need to add the necessary components to Visual Studio - see the Microsoft [site](https://learn.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15) - get the data tools update under Tools, Extensions and Updates, for Community 2019. – B5A7 Jan 19 '22 at 02:53
  • The other missing element - repeating the header on every page when using groups - see this SA [link](https://stackoverflow.com/questions/11285923/tablix-repeat-header-rows-on-each-page-not-working-report-builder-3-0). – B5A7 Jan 19 '22 at 22:34