I am using
selectmonth(date_field)
to display the month but it's showing the month number and I want to show as names, e.g. Jan, Feb, Mar. etc..
|||Jassim:
Lookup the DATENAME function in books online; it should be something like:
select datename (mm, date_field)
Or if you want to only show the first three letters of the month:
select left(datename (mm, date_field), 3)
|||Select Convert(Varchar(3),Datename(mm,getdate()))
I am using
selectmonth(date_field)
to display the month but it's showing the month number and I want to show as names, e.g. Jan, Feb, Mar. etc..
|||Jassim:
Lookup the DATENAME function in books online; it should be something like:
select datename (mm, date_field)
Or if you want to only show the first three letters of the month:
select left(datename (mm, date_field), 3)
|||Select Convert(Varchar(3),Datename(mm,getdate()))
hi,
How to adjust/display/hide the table border(including all cells)?
I see in the property page, there is a BorderStyle for the table, but it only changes the outline of the table border style, I want to change the border style including all cells, like we did in html page, is there an easy way to do this, or I have to make the setting for all cells?
Thanks
I don't think there is a way to force gridlines for the table. You'll probably need to specify the borders of the cells.|||
Oh...
When I created a report table by the wizard, it can be set to have the gridlines. From the rdl file, seems it sets this for each cell, not the whole table.
Hello,
I would like to show the result fo each day of the past 30 days. I might not have data to display for everyday but I still want the date to show up
01/27/2007 resultA
01/28/2007 resultB
01/29/2007 .
...
02/26/2007 resultC
02/27/2007 resultD
Thanks for your help
Arnold
Could you give me an example for the table variable as I have no idea on how to build that.
Thanks
Just to give you an idea here's a sample. You might need to build your code on top of this (if this works for you).
declare @.ttable( Datecoldatetime, col2varchar(50))Declare @.mindatedatetime, @.maxDatedatetime, @.daysint,Set @.mindate ='01/27/2007'Set @.maxDate ='02/27/2007'while (datediff(d,@.mindate,@.maxdate) >= 0 )BeginInsert into @.t (Datecol, col2)Values (@.mindate )set @.mindate = @.mindate + 1--Another Query to get the results for date = @.mindateEndselect *from @.t
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:
=========================================
The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:
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.
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:
=========================================
The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:
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.
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:
=========================================
The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:
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.
