Every once in a while, one comes upon a unique situation where the usual methods just aren’t sufficient to achieve the desired results. Recently, I came across a forum post where the developer needed to remove duplicate values from the results in a Tablix (SSRS 2008). His dataset returned a distinct listing of records, but this particular Tablix only showed certain columns from that dataset. And since all of the columns weren’t present in the Tablix, some duplicates were shown. At first, I figured that he could just remove the columns that he wasn’t displaying. However, he had multiple Tablix objects that used the same dataset and it needed those “extra details”. So, he couldn’t do that! And creating a separate dataset (causing more data to be pulled across the wire and more I/O processing) was not an option.
After looking at his problem thoroughly, it looked like the only way to resolve the problem was to group the details! So, in this article, I am going to show you one way of getting around this using groups.
Before I get to the report, let me show you a sample query that I’m going to use.
Script 1: Results
--Declare a table variable to hold the results
DECLARE @Results TABLE (ANumber SMALLINT, AVal1 VARCHAR(5), AVal2 VARCHAR(2))
--Insert some sample values (all unique together, but some identical ANumber and AVal1
INSERT INTO @Results SELECT 1, 'ABC', 'aa'
INSERT INTO @Results SELECT 2, 'bed', 'bb'
INSERT INTO @Results SELECT 2, 'bed', 'cc'
INSERT INTO @Results SELECT 3, 'frs', 'dd'
INSERT INTO @Results SELECT 3, 'frs', 'ee'
INSERT INTO @Results SELECT 4, 'tre', 'ff'
INSERT INTO @Results SELECT 4, 'tre', 'gg'
INSERT INTO @Results SELECT 5, 'yrt', 'hh'
INSERT INTO @Results SELECT 6, 'ccc', 'ii'
INSERT INTO @Results SELECT 7, 'xcc', 'jj'
INSERT INTO @Results SELECT 8, 'qqq', 'kk'
INSERT INTO @Results SELECT 9, 'pkwwp', 'll'
INSERT INTO @Results SELECT 10, 'eee', 'mm'
--No tricks up my sleeve here! Just return a distinct listing of ALL RECORDS
SELECT DISTINCT * FROM @Results
--Shows duplicate values (recs 2, 3 and 4)
SELECT ANumber, AVal1 FROM @Results
As you can see, ALL records are unique when we select all of the values. However, the records for ANumber (2, 3 and 4) with AVal1 are duplicates as shown in figure 1 below.
Figure 1: Duplicates
Now, let’s create a basic report that has uses the AdventureWorks database as a data source (don’t worry, the finished report attached below will have all of this done for you). I’m hoping that you know how to create or open a project in BIDS (Business Intelligence Design Studio).
Step 1: Create a new report in BIDS.
Step 2: Create a data source pointing to AdventureWorks database and name it “AdventureWorks”.
Step 3: Create an embedded dataset named “Results” that uses the “AdventureWorks” data source created in step 2.
Step 4: Copy and paste the script 1 above into your query window. Test it out! J
Step 5: Save your changes and go to your report design surface.
Step 6: Add two Tablix objects onto your design surface.
Step 7: Hit CTRL+D to show the Report Data pane and then drag ANumber, AVal1 and AVal2 over to Tablix1.
Step 8: Drag ANumber and AVal1 over to your Tablx2.
What just happened here is that we created two Tablix regions on the design surface. If you run the report right now, you’ll get all of the records in both datasets. You will be able to see that Tablix1 has all unique values, but Tablix2 has duplicates. If you’re following along, your results may look like those in figure 2.
Figure 2: Report Results before the Magic
Step 9: Go back to design mode. Right click on the ANumber field in your Tablix2 and add an adjacent group above (found under Add Group option).
Step 10: Select to Group on ANumber and click OK as shown in figure 3.
Figure 3: Add Adjacent Row Group Above
Step 11: Now just drag ANumber and AVal1 values up to the Group Row and delete the details row by right clicking on the details group in the Row Groups pane and selecting DELETE. Your results should look something like shown in figure 4.
Figure 4: Group All Values and Delete Details
Step 12: Save your report and preview your results. You results should look a little like that shown in figure 5 below (without the extra formatting of course).
Figure 5: Final Result
In summary, when you have no other options to removing duplicate values and you are already returning a distinct result set in your dataset, grouping all of the values displayed in your Tablix may be your best option. Granted, I only had two columns to group on, but none-the-less, it would be easy to do this across many columns.
For your convenience, you can download this report and add it to an existing project to play around with. Just note that my data source is pointing to the AdventureWorks database available on CodePlex. Change it if you don’t have that database. I hope that you have enjoyed this article. If you did, please rate it! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald.
Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works
Email: firstname.lastname@example.org | Blogs: SSRSGeek | SQLServerCentral | BIDN