March 6, 2008 at 5:30 am
In VFP there is a value called 'remove line if blank' which eliminates blank space. How it could be done in SQL Reporting Services?
OR in Crystal report there also an option of row property "suppress if blank"
Any one have idea how it can be achieved in SQL Reporting?
Thanks
March 13, 2008 at 7:49 am
In Reporting Services almost every object (including rows in tables) has a property called Visible, this property can be set using an expression. Let's assume you have a column called SalesRegion and you want to blank the details line if this field is blank:
First, select the details line on the left side and then go to the properties area, click on the Visible property to open it up then click on the drop down next to Hidden and select expression.
In the expression you would put something like this:
=IIF(Fields!SalesRegion.Value = "",TRUE,FALSE)
Basically what this means is if the sales region is blank then set the attribute Visible/Hidden to TRUE (i.e. hide the object) otherwise set it to FALSE (display the object).
Good luck,
Nigel.
Nigel West
UK
June 2, 2008 at 10:05 pm
I believed we are looking for a solution to hide a line object in crystal report not the field
June 20, 2008 at 1:45 pm
August 20, 2008 at 5:51 pm
I too am having a similar problem.
I am trying to suppress certain columns in a matrix. I have set the visible property on them to False and they are rendering as blank columns.
I am trying to add a custom totals column to each group. so I have created a parent group which holds two child groups, the second child group is a single column of custom totals. I have collapsed and hidden the first child group so everything is displaying correctly, except that the first child column still exists as a blank column.
I know I can accomplish this in RS2008, but I don't have that option, I am stuck in RS2005. I'll try and post here if I ever find a solution, but it seems unlikely at this point.
August 21, 2008 at 9:11 am
Actually, the property is the "Hidden" property, and it's within a property group known as Visibility. When set to False, the object IS visible, and when set to True, the object disappears. In a table, you would need the entire row's visibility to change, which is separately controllable from the individual text boxes within. You have to make the entire row hidden and not just the individual text boxes within it. Each row has a separate "Hidden" property.
Hopefully, that helps clarify where to set the expression, and clears up what to set it to to get invisibility.
This DOES apply to RS 2005, by the way.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 21, 2008 at 9:28 am
My visibility issue is with a matrix column (actually a collapsed group column), not a table row. Table rows are much simpler to make the visibility thing work on.
While I still haven't solved the visibility issue, I did find an alternate method of solving the source problem. My original issue was needing a custom totals column for my column grouping in a matrix table in RS2005.
It has been my experience that reporting services is extremely bad at handling large amounts of data, so I generally do as much aggregating in a stored procedure as possible before returning the result set to reporting services. This means that at my lowest level of grouping in a matrix reporting services isn't doing any aggregating, it is simply displaying the data.
Because of this I can wrap the lowest level in either First(), Last(), or Sum() and it will return the same value when fully expanded; however the functions play a critical role when the group is collapsed. If I have created a custom total column and ordered it last in each of my column groupings then I can wrap the value in the Last() function and when the group is collapsed it will only display the last column of values rather than auto summing them for me.
August 21, 2008 at 10:15 am
Jereme,
Most of my post was intended at clarifying the details, as many questions were asked that suggested there was confusion. Even one of your posts talks about having the visibility property as False to hide the column, and, at least in RS 2005 anyway, that would need to be set to True to hide any given object. As you also referred to it as "visibility", clarifying that it's actually the "hidden" property might help folks understand why True means don't display it and False means display it. If you set it to False, that could have been your problem.
Of course, in your situation, avoiding the need in the first place is a much better solution.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 21, 2008 at 11:55 am
smunson (8/21/2008)
Jereme,Most of my post was intended at clarifying the details, as many questions were asked that suggested there was confusion. Even one of your posts talks about having the visibility property as False to hide the column, and, at least in RS 2005 anyway, that would need to be set to True to hide any given object. As you also referred to it as "visibility", clarifying that it's actually the "hidden" property might help folks understand why True means don't display it and False means display it. If you set it to False, that could have been your problem.
Of course, in your situation, avoiding the need in the first place is a much better solution.
Steve
(aka smunson)
:):):)
Good point Steve. I was trying to refer to the concept rather than a specific attribute, probably should have been more specific.
December 5, 2008 at 7:51 am
tshadab (3/6/2008)
In VFP there is a value called 'remove line if blank' which eliminates blank space. How it could be done in SQL Reporting Services?OR in Crystal report there also an option of row property "suppress if blank"
Any one have idea how it can be achieved in SQL Reporting?
Thanks
I think tshadab want to remove the blank line.. when an object is hidden in Reporting Services..
like a floating option:
textbox1
textbox2 (set to hidden)
textbox3
to get:
textbox1
textbox3
NOT
textbox1
<-- (blank line)
textbox3
(you can check my other post on microsoft's forums)
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2228455&SiteID=1
February 23, 2009 at 7:19 am
I have tried something similar to this, if my condition was field="abc123" then the lines were not suppressed when the condition was met, however if my condition was a numeric such as field=0 then the lines with the zero value field were suppressed. Using a trim on the field comparing a text made no difference ???
February 23, 2009 at 8:50 am
In SQL Server, there's a difference between a NULL value and an empty string, and fields that are stored as char or varchar and happen to contain numerics are NOT automatically translated into numbers whenever it would appear to be the right thing for SQL Server to do. If your character field stores a zero in it, then the best way to test for that is: field = "0". Using a function to translate to a numeric value from a character field can have problems whenever the character field contains characters that are non-numeric. That's why properly typing data is so important. Thus, having a suppression expression depending on testing a character field for a zero value without using quotes around the zero is going to be problematic.
For CHARACTER fields (char, varchar) :
===========================================
Testing for an empty string would be: field = "".
Testing for zero would be: field = "0".
===========================================
Steve
(aka smunson)
:):):)
robin (2/23/2009)
I have tried something similar to this, if my condition was field="abc123" then the lines were not suppressed when the condition was met, however if my condition was a numeric such as field=0 then the lines with the zero value field were suppressed. Using a trim on the field comparing a text made no difference ???
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
February 24, 2009 at 7:12 am
In the case I quoted the numeric field worked when suppressing the whole line, but the string field did not.
iif(refno="ABC123",true,false)
made no difference printing lines with ABC123 and those without
iif(linetotal=0,true,false)
suppressed the lines where linetotal had a zero value and not when the value was non zero
In some cases I need to be able to suppress data using the first example.
February 24, 2009 at 8:03 am
One of the other things you may have to worry about is when you have a "char" field that has extra spaces at the end. You can use RTRIM in your SQL query to eliminate them, or use the Trim function within RS:
=IF(TRIM(fieldname) = "ABC123", True, False)
My previous post was concerned with ensuring that the field's type determines how you check the field's value. If it's a char or varchar field, then you have to use quotes, even when the characters in that field are all numerics. Otherwise, you risk non-numeric characters causing some kind of value function to cause an error when it encounters them, which can include treating such values as if they were nulls, or generating a runtime error. Either of those is a kind of problem you want to avoid.
Finally, you also have to be sure that the data values you're testing for actually exist in the output data. I've been there and dealt with that enough times to make me remember ALL the time these days.
Steve
(aka smunson)
:):):)
robin (2/24/2009)
In the case I quoted the numeric field worked when suppressing the whole line, but the string field did not.iif(refno="ABC123",true,false)
made no difference printing lines with ABC123 and those without
iif(linetotal=0,true,false)
suppressed the lines where linetotal had a zero value and not when the value was non zero
In some cases I need to be able to suppress data using the first example.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 27, 2009 at 10:10 am
I know this is an old topic -- however I was searching online for an answer to this as well, and found a solution to my problem that I thought I'd share. In Crystal Reports, what you can do is right click in the design on the left border next to details / footer and it will give you the option to suppress blanks.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply