How to group similar data together in SSRS

Question

How to group similar data together in SSRS

kennyhuang0108

Right there with Babe

Points: 733
More actions
November 6, 2018 at 4:46 pm

#380798

Hi,
I created a report and it shows below:
Office Name Employee #
ABC - OFFICE1 2
ABC - OFFICE2 3
ABC - OFFICE3 4
ABCD - OFFICE 1 6
ABCDE - OFFICE 5
How can i group office name with ABC altogether and display as below
Office Name Employee #
ABC 9
ABCD - OFFICE 1 6
ABCDE - OFFICE 5
I hope it makes sense and thank you for your help.
sincerely,
Kenny

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

It would be infinitely clearer if you posted a proper CREATE TABLE script, with INSERTS to populate the table for your sample data. It's really easy to misinterpret the data as you have it right now. Besides, if you provide consumable data, you're much more likely to get a tested answer.

kennyhuang0108 Right there with Babe Points: 733 More actions · Answer 2

Sorry. I dont know anything about create table script. Because i was just using the dataset someone created for me.
When i drag and drop into design area, it already displayed something like below.
I just hope i can group the office beginning with ABC -XXXXXX altogether.

Office Name Employee #
ABC - OFFICE1 2
ABC - OFFICE2 3
ABC - OFFICE3 4
ABCD - OFFICE 1 6
ABCDE - OFFICE 5

thank you so much for your thelp

pietlinden SSC Guru Points: 63503 More actions · Answer 3

In SSRS, you can right-click on your dataset, and then choose "Add Calculated Field" and then do something like
=LEFT(Fields!Protocol.Value,3)

Then you can group by that calculated field inside your report.

kennyhuang0108 Right there with Babe Points: 733 More actions · Answer 4

Thank you Pietlinden.

I tried right click on my dataset. All i see is only "General" option in Dataset Properties. There is no "Add Calculated Field" . thank you.

pietlinden SSC Guru Points: 63503 More actions · Answer 5

Are you authoring your report or just consuming it? Maybe you don't have rights to add columns?

kennyhuang0108 Right there with Babe Points: 733 More actions · Answer 6

I guess i am just consuming it. So is there anything else i can do in SSRS besides "Add Calculated Field" ? thank you.

pietlinden SSC Guru Points: 63503 More actions · Answer 7

Can you go back to the person that created the dataset and ask them to add it? (super annoying, but I have no idea how else to do it!)

meichmann SSCommitted Points: 1900 More actions · Answer 8

kennyhuang0108 - Tuesday, November 6, 2018 4:46 PM
Hi,
I created a report and it shows below:
Office Name Employee #
ABC - OFFICE1 2
ABC - OFFICE2 3
ABC - OFFICE3 4
ABCD - OFFICE 1 6
ABCDE - OFFICE 5
How can i group office name with ABC altogether and display as below
Office Name Employee #
ABC 9
ABCD - OFFICE 1 6
ABCDE - OFFICE 5
I hope it makes sense and thank you for your help.
sincerely,
Kenny

If you created the report, you can group the data through the table wizard.

drag the Office field into the row group, then any other fields into the values section.