How to show duplicates in reports

I'm trying to create a report in Sql Server 2005 Reporting Services where it will display duplicates rows in the reports. I'm unable to find the property that enables this. I would need something like HideDuplicates = false, but that is not the way it is intended to be used. If HideDuplicates is left blank, it seems to still hide duplicate values if the preceding row matches.

The report is a crosstab type, so we are using a Matrix for the report item. I'll illustrate with a simple example:

This is what we currently get:

Catagory	Class	Detail
Type 1	A	Item 1
	B	Item 2
		Item 3
Type 2	A	Item 4
	B	Item 5

This is what we want:

Catagory	Class	Detail
Type 1	A	Item 1
Type 1	B	Item 2
Type 1	B	Item 3
Type 2	A	Item 4
Type 2	B	Item 5

I'm sure its something simple, but we just cant seem to find the solution,

Thanks

Darin

Rather than using a matrix report, I think a simple list report would do what you are looking for (if you don't do any grouping). You can still use aggreated fields in the simple list report.

|||

Unfortunately we need to use a matrix as the actual report is a crosstab, I just didn't illustrate that in the example for simplicity sake.

I think the report looks nicer with the blanks and is easier to read; however, if it is dumped to Excel for further analysis ( which in our actual report will happen often), the auto filter function will not work as the blank rows are not included if a specifiec catagory is selected. Only the first row that is allgned with the merged cells is shown.

|||

Open the code view of the report and search for the XML node called "HideDuplicates". See http://msdn2.microsoft.com/en-us/library/ms152916.aspx for on how to set. Removing the element entirely will cause the values to appear in each row on the report, like you'd want to have for use in Excel.

My problem is related. I have business analysts using the Report Builder tool basically as a querying tool. 90% of the time, they export to Excel and then crunch there. While I know this is not intended usage of Reporting Services, I am still disappointed by the fact that, in order to get duplicates to appear, you must go code-side, something I can't push off on a BA.

Any ideas for editting a Report Builder generated report file on the fly, to remove "HideDuplicates" elements?

|||

Try setting the "HideDuplicate" property on the "Type 1" textbox in your report.

Here are the defintion of the Textbox properties:

=========================================

Textbox

The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:

Attributes/Elements

Name

Card

Type

Description

Value

1

Expression

(Variant)

An expression, the value of which is displayed in the text-box.

This can be a constant expression for constant labels.

CanGrow

0-1

Boolean

Indicates the Textbox size can increase to accommodate the contents

CanShrink

0-1

Boolean

Indicates the Textbox size can decrease to match the contents

HideDuplicates

0-1

String

Indicates the text should not be displayed when the value of the expression associated with the report item is the same as the preceding instance.The value of HideDuplicates is the name of a containing grouping (other than the current grouping) or data set over which to apply the hiding.Each time a new instance of that group is encountered, the first instance of this report item will not be hidden. Rows on a previous page are ignored for the purposes of hiding duplicates.If the textbox is in a table or matrix cell, only the text will be omitted.The textbox will remain to provide background and border for the cell.Outside of a table/matrix cell, the background and borders are omitted as well.

Ignored in matrix subtotals.

ToggleImage

0-1

Element

Indicates the initial state of a toggling image should one be displayed as a part of the textbox.

UserSort

0-1

Element

Indicates an end-user sort control should be displayed as a part of this textbox in the UI.

DataElementStyle

0-1

Enum

Indicates whether textbox value should render as an element or attribute: Auto (Default) | AttributeNormal | ElementNormal. Auto uses the setting on the Report element.

|||Sorry for the late reply, but the 'HideDuplicates' element is nowhere to be found in the report definition.|||I've tried setting the HideDuplicates to a number of the values available in the dropdown list and it still does not solve my problem. Other than that, I really dont know what to set it to.