Conditional Formatting Mystery
Oct 4, 2007
I am trying to format a background color based on the field's value. But the expression always returns the false result.
Here is the expression I am using
=iif(Me.Value="RSB","Red","Blue")
I have also tried
=iif(ReportItems("Type").Value="RSB","Red","Blue")
Both fill the background color blue when the textbox clearly contains RSB. I'm not sure why it cannot find the value. The field data type is Char(4). The textbox values are all uppercase.
This all started when I was trying to use a report parameter for conditional formatting and I realized that it wasn't working. I broke down the expression by directly trying a value.
Any thoughts?
View 4 Replies
ADVERTISEMENT
Apr 2, 2008
hey there I have a series of finacial reports. the girls that work on them want color and the boss wants them to print in black and white. the color back grounds print grayscale so I would like to add a link of some sort that changed the background to transparent when they want to print a report.
View 10 Replies
View Related
Apr 2, 2008
can anyone help me try to figure out how to get my colored background to print in white and not gray
View 1 Replies
View Related
Jan 25, 2007
I want to write an expression that will evaluate 2 fields. So, in simple terms, I want the following:
if field1 > 0 and field2 = "yes", apply formatting, otherwise leave formatting as is.
I am familiar with using iif statements, but since I want to evaluate 2 fields instead of one, I'm not sure how to proceed.
It seems I may need to use another statement, such as the Switch statement...I just don't know how.
Thanks for any help!
David
View 3 Replies
View Related
Nov 23, 2007
hu guys,
I am trying to change the colour of the background of each row in a table depending on the month it is.
I have tried putting the expression
=IIF(Datepart("month",Fields!AppDate.Value) = Datepart("month",Now) ,"Green","Red")
in the backgroundcolor forthe table row, but it doesn' work.
do you have any suggestions ? or can see why this isn't working?
kind regards
Jamie
View 2 Replies
View Related
Oct 11, 2007
Hi,
I have a report as such:
dataset:
select s.supplier, i.item, p.price, i.[type],i.origin,i.unit
from pricelist p,item i,suppliers s
where p.item = i.pkey and p.supplier = s.pkey
and a matrix that has
row groups; item,type,origin,unit
column groups : supplier
and price as detail.
this has been achieved
example:
freshegg bestdeal freshmarket
Eggs small lo ea 1 2 2
now i want minimum price to be highlighted.
the minimum price on that row to be for example red.
I am currently trying in color properties set it like this:
=IIf(Fields!price.Value = Min(Fields!price.Value, "matrix1_unit"),"DarkRed","Black")
where matrix1_unit is the last row group,
but this is not working right,
Anyone has some directions?
thanks.
View 1 Replies
View Related
Dec 15, 2006
I have the following code in the color property of a textbox. However, when I run my report all of the values in this column display in green regardless of their value.
=SWITCH(Fields!Wrap.Value >= 3, "Red", Fields!Wrap.Value < 3, "Green")
I already tried =iif(Fields!Wrap.Value >= 3 , "Red", "Green") and got the same results.
Is it because this is a matrix report? What am I doing wrong?
Thanks in advance . . .
View 4 Replies
View Related
Aug 27, 2015
I have an SSRS report that I want to use conditional formatting on. When the value of the cell = 0, I want the background yellow, and the font red. For the background, I have:
=IIF(Fields!reads.Value = 0, "Yellow","White")
For the font color, I have
=IIF(Fields!reads.Value = 0,"Red", "Black")
The issue is that the background one is working properly, while the font one is not. If the value = 0, the font is invisible. The value is present in the report (I can export to excel and see it just fine). If I change both the background and font to the defaults, the value is STILL invisible.
View 0 Replies
View Related
Jul 19, 2007
Hi Everyone,
I am trying to hide a column in a matrix table. I have no trouble using conditional formatting to control the visibility of the column, but when I hide the column the main row in the matrix does not shrink. Therefore, I have a big gap€¦ My thought was to use conditional formatting to control the column width of the main matrix row. Listed below is the expression that I used. Can anyone help me with the error message or recommend a better solution?
=IIF(Fields!FiscalYear.Value = IIF(Month(TODAY()) = 10-12,Year(TODAY()) ,Year(Today())-1), 0.625in, 0.25in) cannot be parsed as a unit because it does not contain numeric values. Examples of valid unit strings are "1pt" and ".5in".
Regards,
A.Akin
View 1 Replies
View Related
May 15, 2007
I have a column that contains either a test score or the state abbreviation where the student passed a particular test. I want the text to be red between 100-159, green greater than or equal to 160 and black otherwise.
This is what I've tried to take care of the scores:
=IIf(Fields!Score.Value <= 159 and Fields!Score.Value > 100,"Red", "Green")
Two issues:
I want all other scores (< 100) to be Black and
I'm not sure how to deal with the state abbreviations. I'm assuming this is why I receive this error:
[rsRuntimeErrorInExpression] The Color expression for the textbox €˜Score€™ contains an error: Input string was not in a correct format.
Preview complete -- 0 errors, 1 warnings
All data is stored as varchar.
View 3 Replies
View Related
Jun 5, 2007
I have a report that when the user drills down I need to change the formatting to include borders and/or colours. I tried adding the following statement to top border of the element without luck. Since I don't know when the detail is the last line of the drop down, I am uncertain how to try adding it there.
I do not want the underline to show when there is no expanded drill down.
=iif((Previous(Level) > 0, "Dotted", "None")
g1 name1 total1
g1 name2 total2
d po#3a total3a
d po#3b total3b <<-- want to add underline between total3b and total3
g1 name3 total3
Thank you
View 2 Replies
View Related
Jun 15, 2007
I am trying to create a report which has conditional formatting.
The primary dataset is a view of objects with several values
eg
object1,0,4,0,1
object2,0,3,1,1
The secondary dataset is the comparison table and just contains the values
eg
0,3,1,1
I'd like to conditionally format the values based on the comparison table but when I create an expression comparing to the second dateset
eg
=iif(Fields!object1.Value <> Fields!comp_object1.Value , "Red", "SkyBlue")
i get
Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Not sure if there is a way to tell the expression to look for comp_object1 in dataset2, even tho it is uniquely named?
Ideas gratefully received !
View 7 Replies
View Related
Feb 2, 2007
Hi all!
I would like to know if Conditional Formatting in Report Builder is supported in Service Pack 2 CTP.
Thanks for your time.
View 3 Replies
View Related
Aug 22, 2007
Hi there.
I am creating a report that the requirements need different background colors based on the row or column as shown below:
Community
Total # of Respondents
Resident's Overall Satisfaction Rating
Quality of Repair
May '07
41
3.6
5.0
April '07
14
1.8
3.0
Q2 '07
55
2.7
4.0
March '07
36
3.6
3.0
February '07
28
4.0
1.2
January '07
22
2.2
4.0
Q1 '07
86
3.3
2.7
YTD '07
141
3.0
3.2
December '06
33
3.8
4.2
November '06
27
2.6
5.0
October '06
42
1.8
3.0
Q4 '06
102
2.7
4.1
September '06
58
4.0
2.2
August '06
84
2.0
1.6
July '06
52
3.2
3.4
Q3 '06
194
3.1
2.4
June '06
40
2.4
4.2
May '06
41
3.6
5.0
April '06
14
1.8
3.0
Q2 '06
95
2.6
4.1
March '06
67
N/A
3.8
February '06
38
N/A
2.8
January '06
N/A
3.8
N/A
Q1 '06
105
3.8
3.3
YTD '06
496
2.9
3.5
Rolling 12 Month Average
477
2.9
3.3'
I can get the row colors to work great with an expression, but when I try to add the gray column with conditional formatting for the Resident's Overall Satisfaction Rating question, it clobbers my row formatting. I am thinking that I will have to do some gnarly expression in each of the rows and columns using the InScope function. Does that sound about right, or is there an easier way?
Thanks, Mike
View 1 Replies
View Related
Aug 3, 2007
Hi,
Does anyone know how to implement conditional formatting with three criteria (e.g. If a value is <83 then BGcolour Green, between 83 and 90 the BGcolour is Yellow, greater than 90 the BGcolour is green).
I have tried approaching it in two ways. One was with a case statement as follows:
CASE
WHEN TargetPer < 83 THEN 'Red'
WHEN TargetPer >= 83 AND TargetPer < 90 THEN 'Yellow'
WHEN TargetPer >= 90 THEN 'Green'
END AS BGColor
TargetPer being the value i wish to examine. I returned this in the query dataset i am using to populate the report. I then used the following expression to set the backgroundcolor property:
=Fields!BGColor.Value
Unfortunately I get no yellow fields for the appropriate values (even though the BGcolor value says yellow!!!)
The other approach i have used is the following expression to set the background colour
=IIf(Fields!TargetPer.Value>90,"Green",IIf(Fields!BGColor.Value>=83 and Fields!BGColor.Value <=90, "Yellow","Red"))
For this i still have no yellows just reds and greens!
I am using a matrix report that has subgroups on the columns for table!
Any ideas anyone????
Thanks
Marek
View 8 Replies
View Related
Aug 28, 2006
SQL Server 2000. Here's what I have so far. The section of the query I need help with is highlighted in blue.
CREATE PROCEDURE dbo.GetByVersion
(
@targetVersion varchar(30),
@product varchar(50)
)
AS
SET NOCOUNT ON;
SELECT * FROM MyTable
WHERE (product = @product)
AND
CASE
WHEN @targetVersion='' THEN (targetVersion='')
ELSE (targetVersion LIKE @targetVersion + '%')
END
GO
I get a syntax error in the Stored Procedure editor on an equal sign in this line:
WHEN @targetVersion='' THEN (targetVersion='')
What I want is this (in psuedocode):
if @targerVersion is blank
search for records where the targetVersion column = blank
else
search for records where the targetVersion column starts with @targetVersion
Can anyone offer any suggestions as to how I might modify my query to do what I want? Any help is very much welcome - Thanks in advance! :-)
View 5 Replies
View Related
Jan 23, 2007
I have a matrix where i'm trying to have the report select the maximum value in the data area (not including the subtotals) and bold that value.
month
day1
day2
day3
day4
total
Jan
10
15
5
12
42
Feb
5
8
3
11
27
Total
15
23
8
23
69
I'm sure i will have to use the IIF function in conjunction with the InScope function.
However i'm unsure on how to isolate to the max value and make sure the Inscope function does not affect the totals.
any idea's are appreciated.
View 3 Replies
View Related
Apr 11, 2008
Can I build an expression that allows me to change the field size of a column or row in SSRS2005?
View 5 Replies
View Related
Mar 25, 2008
There is no conditional color formatting for Report Builder.
Is there any way to have it - write plug-in for example.
I tried to "hack" rdl file the RB produces:
but it didn't work.
Also, I hear that Microsof is about to issue a fix for it (SSRS 2005). Is it true?
Thank you
View 3 Replies
View Related
Oct 19, 2007
Hello,
I am having a problem trying to do some conditional formatting on the text color.
This textbox is in the Group Footer so i essentially want change the color to red if the SUM of one field in the group is less than the SUM of another field in the same group.
I am doing this:
=Iif(Sum(Fields!YTDChargeHours)< Sum(Fields!YTDForecast), "Red", "Black")
but i am getting this error:
[rsRuntimeErrorInExpression] The Color expression for the textbox €˜YTDChargeHours€™ contains an error: Operation is not valid due to the current state of the object.
Thanks in advance!
View 4 Replies
View Related
Mar 2, 2012
I'm trying to put conditional formatting on a field, that behaves as follows:
The data in the field is varchar, and sample data is either:
NULL
3.0 :0
11.7 :1 (these are ratios of a sort)
I want to evaluate the first 3 characters of the string as numbers.
Example:
Mid(fieldvalue,1,3) = "3.0" or "11."
Any data that is greater than 1.99, I want to make the background dark red, anything else including nulls, zebra formatting. I have the following expression built so far and it appears to work, except when the value is null. If the value is null, it leaves the background color white.
This is the warning: [rsRuntimeErrorInExpression] The BackgroundColor expression for the text box "Asthma" contains an error: Input string was not in a correct format.
=iif(
isnothing(Fields!Asthma.Value)
,(IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))
,(iif(mid(Fields!Asthma.Value,1,3)>1.99
,"DarkRed"
,IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))))
My logic is, if the field is null, zebra format, if mid of the value is > 1.99, dark red, everything else zebra formatting. As I said, this seems to work except for nulls.
View 2 Replies
View Related
Jun 1, 2015
Running into an error [BC30205] and no values get colored using this syntax
=iif(DateDiff("d",Fields!Last_Reboot.Value,Now()) > 30 Â And DateDiff("d",Fields!Last_Reboot.Value,Now()) <= 59, "Orange", NOTHING),IIF(DateDiff("d",Fields!Last_Reboot.Value, Now()) >60, "Red",
NOTHING)
View 2 Replies
View Related
Aug 6, 2015
I have a chart with series that needs conditional colour for three conditions. One condition must be transparent. I have tried the various IFF or SWITCH expressions below and some work fine in SSDT but when I deploy them to the browser the formatting is lost. This simple IFF expression works. The series is transparent as required both in SSDT (Visual Studio BIDS) and IE11 and Firefox browsers but I need two other conditions.Â
=IIF(Fields!ExhibitionID.Value = -1, "#00ffffff", "Red")
Both of the expressions below work in SSDT (Visual Studio BIDS) as expected but when deployed to the browser none of this formatting is rendered and all series are in blues even the ‘transparent’ series. No red at all.
=IIF(Fields!ExhibitionID.Value = -1,"#00ffffff", IIF(Fields!ExhDepartment.Value = 27,"Orange","Red"))
=Switch(Fields!ExhibitionID.Value
= -1,"#00ffffff",Fields!ExhDepartment.Value = 27,"Orange",(Fields!ExhDepartment.Value <> 27) AND (Fields!ExhibitionID.Value <> -1),"Red")
I have tried deleting various files RDL and DATA. I have tried uploading the file from report manager in IE and I have tried editing the file in report manager.
View 2 Replies
View Related
Mar 4, 1999
I have a table Catalog_Item with 365000 rows. I wish to move the contents of the table to an identical database on a different server using bcp. The table has no primary keys, foreign keys, or indexes. I am able to successfully bcp out the data in character format and native format.
I attempt to bcp the data in to the identical table on the other server, but
the bcp ALWAYS fails at 19000 or 20000 rows; it does not complete, just hangs there in the command window, with no more info. When I use a small data set of 5000 rows, the same happens at the end of those 5000. I have tried to redo the bcp in native mode, and character mode. I have tried to specify the first line starts at 125000. I have tried to bcp the data into a different database on a different server; that also quits at 19000 or 20000 rows.
BCP of other large data sets into the same database occurs smoothly. Only this combination of table and data set is giving this problem.
any ideas?
View 2 Replies
View Related
Mar 7, 2008
Hello everyone,
Is there a way in order to execute a subscribed report based on a certain criteria?
For example, let's say send a report to users when data exist on the report else if no data is returned by the query
executed by the report then it will not send the report to users.
My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it.
Any help or suggestions will be much appreciated.
Thanks,
Larry
View 6 Replies
View Related
Apr 13, 2005
I am having trouble connecting to an SQL Server from an ASP.NET page
written in C#. I have reduced the code in my page to this: public void Page_Load( object s, EventArgs e ) {
SqlConnection conn = new SqlConnection( "Server=server;User ID=user;Password=pass;" );
conn.Open();
} and I get the following error:
System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.
This
works for other server/user/pass except the one I am having trouble
with. The SQL Server is using SQL & Windows Authentication. I am
able to connect from the machine that is running the webserver via
osql, ODBC, and MS Access using the same arguments (server, user, and
pass), so I know that the SQL server does exist, and the User/Password
should give me access. Does anyone have any ideas why this
isn't working or what ASP.NET is does differently from the other access
methods? Any ideas are greatly appreciated. Thanks, Mark Dane
View 6 Replies
View Related
Jan 31, 2000
I have created a maintenance plan which backups to a device which I have created. However the device does not show in explorer and maintenance plan cannot find it when it trys to open the device. Consequently I'm not getting a backup.
Even if I delete the device and re create it things don't change.
Running SQL 7 SP1 on NT SP5 with SMS 2 SP1.
Any help gratefully received....
Dave Turner
View 1 Replies
View Related
Jan 28, 2000
I was working at a customer site and wanted to verify that backups were indeed being run successfully before I began to make changes.
I noticed a dump device for the production database with the nightly backup in it but I cannot find the task that produces this dump. I did find the task that dumps the log each hour.
Is there another area that some of you DBAs used to schedule dumps other than the task manager that I might be missing? Maybe an AT job?
Thanks.
-Darin.
View 1 Replies
View Related
Feb 3, 2000
I am trying to track down an account in SQL 6.5 that keeps showing up in my failed login log. We are using Standard Security.
The log doesn't provide much detail other than the attempted login name and the time/date.
Any ideas on how to track down more information on this mystery account would be appreciated.
Thanks.
Darin Drewrey
View 2 Replies
View Related
Jul 20, 2005
Hi all,I have a statement that reads as follows:select [Sales Ledger] - gl as differencefrom(SELECT SUM(RPAAP / 100) AS [Sales Ledger] FROM F03B11) Q1join(SELECT SUM(GBAPYC + GBAN01 + GBAN02 + GBAN03 + GBAN04 + GBAN05 +GBAN06+ GBAN07 + GBAN08 + GBAN09 + GBAN10 + GBAN11 + GBAN12)/100AS GLFROM F0902WHERE (GBAID = '00667809') AND (GBFY = 3)) Q2My first nested statement however I keep getting the message:Server: Msg 170, Level 15, State 1, Line 8Line 8: Incorrect syntax near 'Q2'.I just cannot fathom why this is so?Any suggestions would be most helpful.Moby*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jul 17, 2007
Hey All,
Hope this is the right place for this post.
I set up logshipping between two databases recently. everything looks fine. the backup, copy and restore jobs are all suceeding(from the job log). the problem is no Tlogs are being restored in secondary database. The restore job skips all the tlogs and says it did not find any tlog back up file to restore!! The jobs finishes with this:
007-07-17 14:40:30.59 Could not find a log backup file that could be applied to secondary database 'SaaSNet_dataStore'.2007-07-17 14:40:30.59 The restore operation was successful. Secondary Database: 'SaaSNet_dataStore', Number of log backup files restored: 02007-07-17 14:40:30.59 Deleting old log backup files. Primary Database: 'SaaSNet_DataStore'2007-07-17 14:40:30.59 The restore operation was successful. Secondary ID: '5808a414-2ada-41d2-a8a0-2cf84f85174a'
Appreciate your help
View 3 Replies
View Related
Aug 17, 2004
In general, patch management solutions use an approach very similar to inventory and deployment, although, obviously, implementation details vary. Inventory relies on an external database to establish what is considered to be a recommended patch level and provides criteria for validating whether a particular patch has been installed. Products from Microsoft and Shavlik Technologies (on which HFNetChk, MBSA, and SMS 2.0 Feature Pack are based), keep track of published patches on the Microsoft Web site using the same mechanism, based on an XML formatted file called mssecure.xml. This file, available centrally at predefined locations, serves as a template against which the status of updates on target systems is compared. mssecure.xml can be obtained directly or via its compressed, digitally signed version, mssecure.cab. Both files can be downloaded from:
the Microsoft Web site at https://www.microsoft.com/technet/security/search/mssecure.xml and http://download.microsoft.com/download/xml/security/1.0/nt5/en-us/mssecure.cab
the Shavlik.com Web site at https://xml.shavlik.com/mssecure.xml and http://xml.shavlik.com/mssecure.cab
View 1 Replies
View Related
Sep 6, 2007
Calling any OLAP Guru's. Were attempting to improve a vendors BI process which involves running MDX queries against our SSAS cube, then saves the data into their own proprietary database which is taking to long.
We currently run this process on a x16 CPU, 65GB top end server attached to expensive disk subsystem, however a dual-core processor
laptop completely out performs the expensive hardware by around 45% quicker. Both when the configuration is identical or when we ramp up the processes on the expensive hardware it still performs miserably compared to the laptop. Perfmon counters or Profiler don't so much difference when comparing the server and laptop.
Does anyone know why the laptop completely outstrips the expensive top end hardware perfmance wise although the configuration profile is identical?
View 3 Replies
View Related
