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.
No comments:
Post a Comment