Blank Out Duplicate Column Values in SQL Reporting Services

Blank out duplicate column values in SQL Reporting Services

It is probably best to simply use groups, but if you want to keep it this way, try:

=IIf(Previous(Fields!Col1.Value) = Fields!Col1.Value, Nothing, Fields!Col1.Value)

Or you can set the textbox's HideDuplicates property to the containing group name (in your case, presumably Tablix1_Details)

How to show blank record in sql if duplicate rows exists

You can do this:

;WITH CTE
AS
( 
   SELECT EmployeeID, EmployeeName, Department,
     ROW_NUMBER() OVER(PARTITION BY Department ORDER BY(SELECT 1)) rownum
  FROM table1
)
SELECT
  EmployeeId,
  EmployeeName,
  CASE rownum
    WHEN 1 THEN Department
    ELSE ''
  END AS Department
FROM CTE
ORDER BY EmployeeID;

SQL Fiddle Demo

Note that: Try not to do these sort of formatting stuff in SQL. SQL is not about formatting.

shows blanks for repeating values in a result set

Wrap your existing query in CTE adding ROW_NUMBER OVER PARTITION BY your columns, which will create RNs for each group of values. In outer query just use CASE to select values where GRP_RN = 1 and empty string otherwise.

WITH CTE AS 
(
    Select  distinct top 100000 
        o.EventSetName,       
        o.EventSetDisplay,
        o.EventSetDescription,
        o.ChildSetName,
        ROW_NUMBER() Over (Order By f.ChildSetName) RN,
        f.DocumentDispSequence,
        f.SectionDispSequence,
        o.ObsSetDispSequence,
        null  as NullColumnNeedsName,
        ROW_NUMBER() OVER (PARTITION BY o.EventSetName, o.EventSetDisplay,o.EventSetDescription ORDER BY f.ChildSetName) GRP_RN
    From   ##ObsSetLevel o,
    INNER JOIN ##Final f ON f.ChildSetName = o.EventSetName and o.EventSetName = @variableName
)
SELECT
    CASE WHEN GRP_RN = 1 THEN o.EventSetName ELSE '' END AS EventSetName,
    CASE WHEN GRP_RN = 1 THEN o.EventSetDisplay ELSE '' END AS EventSetDisplay,
    CASE WHEN GRP_RN = 1 THEN o.EventSetDescription ELSE '' END AS EventSetDescription,
    other columns
FROM CTE  
Order By RN asc, DocumentDispSequence asc, SectionDispSequence asc, o.ObsSetDispSequence asc

PS: I have also corrected your use of old-style joins. That usage is outdated more than 20 years ago with introduction of SQL-92 standards. You should avoid using them.

SSRS row group shows duplicate values for certain column group

I tried to create the matrix again after Hannover Fist mentioned it worked with the report wizard. It turned out that the problem was with the Expression for the Value in the Text Box Properties of the cell.

By default the Expression is something like [SUM(Score)] but after I created my report, I thought I should not need the summation because the value is distinct, which resulted in having [Score] instead.

Once I added the SUM back to my report, it started working as expected.

SSRS showing a blank column on a column grouping that is not in the design tab

Solved - There were some blank fields in my Attribute_Group_Desc column in my dataset which caused the blank column at the start within the matrix.

I've since dealt with my adding a clause on my SSRS report to hide the column, if the field isNothing. Similar to the below.

=iif(isNothing(Fields!Attribute_Group_Desc.value), True, False)

Hope this is helpful for the next person who has trouble with this.

SQL Server Reporting Services: Cell Value Not Repeating when using table/matrix wizard

As you've seen, SSRS treats row header cells differently, i.e. stretching them over any child groups.

Your report probably looks a bit like this:

I've highlighted the dotted lines that separate the report areas. This example report has the same issue as your example:

To get around this, the various row group values need to be moved from the row header area to the main report area.

First, delete the left four rows - when prompted choose Delete columns only.

You should only have the Skill column remaining.

Right click and Insert Column -> Outside Group - Left.

Keep adding columns using Insert Column - Left.

Once you have enough new columns, add the various grouping values. The report should look something like this:

Note that there are now no dotted lines between the Skill column and the Test column.

Now the group values are repeated for each row as required:

---

Related Topics