percentage formatting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

percentage formatting

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-25 : 11:07:53

Hi

I've created 3 simple expressions to calculate percentages of seconds as I want to be able to calculate these and also add conditional formatting.

The 3 expressions are:
=Fields!Agreed_Solve_Time__Secs_.Value *0.75 --75%
=Fields!Agreed_Solve_Time__Secs_.Value *0.90 -- 90%
=Fields!Agreed_Solve_Time__Secs_.Value *1 -- 100%

I then added 3 calculated fields and referenced them in the font formatting in the text box properties like so:

=iif((Fields!SeventyFive_Percent.Value), "Green",(iif(Fields!Ninety_Percent.Value, "Red", iif( Fields!OneHundred_Percent.Value, "Blue", ""))))

But this does not work, all I get is green, can this be done this way, or is there a better way?

Actually just realised how can the fields understand I am wanting to return a colour, I need to have the column actually assigned with the % amount to see the reference...

In that case can I use an iif or case statement to turn the columns into 75%,90% and 100%, so 3 columns?? (I could hide the columns after).

So, =Fields!Agreed_Solve_Time__Secs_.Value * 0.75 then "75%"
if anyone can think of a better way though I'd appreciate it.

Thanks

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 11:14:59

You have to provide some logical expression that evaluates to TRUE or FALSE. For example,

=iif((Fields!SeventyFive_Percent.Value > 300), "Green".....

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-25 : 11:19:29

Yes but this doesn't exist, this is why I have had to create the actual % amounts as stated above, can I get around this by making the columns actuall the percent value after calculating so C1 = 7%, C2 = 90% and C3 = 100%, then I can use the cell reference to work out the colours and hide the columns after?
Thanks

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 11:35:45

I am not quite following your requirement. Are you trying to determine the color of the column that displays Agreed_Solve_Time__Secs_ ? Assuming that that is the case, if Agreed_Solve_Time__Secs_ had a value of, for example 584, what color do you want to show?

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-25 : 11:42:33

Yes this field calculates the time in seconds from start datetime to end datetime, but it does nothing else it only shows me seconds, so I created the *0.75...to work out the percentages which then returns a number based on the % I'm asking which is fine but I need the cell to return the actual percent not the number, ohterwise I cant use conditional formatiing as the number returned will always be different, so is it possible to make the column display the actual percentage in every row instead, so 3 columns for each, then use the display string to work out conditional formatting then hide the columns?
Thanks

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-25 : 12:22:30

When you calculate a percentage, you are always working with two numbers. For example, 40 is 20% of 200.

In your case, you have one number - the time in seconds from start to end - which is Agreed_Solve_Time__Secs_. Let us say that that time is 40 seconds in a given row. The formulas you were using were calculating 75%, 90% and 100% of that, which would be 30, 36, and 40, respectively. Given that we have four numbers now - 40, 30, 36, and 40, what do you want to do with those? What I am not clear on is given this SPECIFIC EXAMPLE, what is the color that you want to display for this cell, and what is the logic to be used to determine that color?

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-26 : 06:05:39

yea but the numbers will always be different as there are hundreds of records, so I was thinking if the calculations are worked out in 3 columns and then hide the columns but have the colours refer back somehow to the 75%, 90% and 100% to work out the colours?

Does that make sense?

SZ1
Learning and development is the driving force in the universe...!

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-26 : 06:39:54

Please see here for screenshot, you will see I have worked out the percentages for each column, how can I add colours though if the result is always different?

http://www.pcfixerman.com/index.php/screenshot

Thanks

SZ1
Learning and development is the driving force in the universe...!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-26 : 13:45:56

Looking at the screen shot you posted, the first two rows:


Actual       75%      90%       100%
3600000   2700000  3240000   3600000
  50400     37800    45360     50400

I am with you so far. What is not clear to me is, what is the color you want to display for each of these rows, and what are the reasons for picking those colors

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-26 : 21:18:33

I'm with James on this. I'm sure the answer is simple but you really need to explain the issue clearly.

All I can see (per row) is a single number and then some calculations to work out various percentages of this number. As you say you want to colour the columns Red, Green or Blue what are the conditions for each colour?

I might be way off the mark but you or you explanation appears to be missing something to compare the results of the percentages to.

I would have expected something like an ActualSolvedTime value that you could use to compare against.

For example (abbreviated field names for readability):
A single row might contains these two values.
Agreed = 500
Actual = 440

Your report could show the Agreed Column followed by the Actual column. The Actual column would then have its color property as an expression something like this...

=IIF(Fields!Actual.Value <= Fields!Agreed *.75,"Green",
IIF(Fields!Actual.Value <= Fields!Agreed *.90,"Blue","Red"))

The values will change colour as follows.
Green of the Actual is <= 75% of Agreed
Blue of the Actual is >75% <=90% of Agreed
Red if Actual is over 90%

Of course this may not be what you want but hopefully it'll point us in the right direction.

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-27 : 05:55:58

Thaanks for your replies. I've found out that I cant use the Actual Seconds (Agreed_Solve_Time_Secs) field as this is always preset by the SLA, so my percentage calculations would be pointless, as the figures I set on percentages will be static and not moving towards the % set.

So I think the best that can be done for now is to use the Agreed Fix Time(Agreed_Solved_Date_Time) field and calculate the colours based on that field,

so, if Agreed_Solved_Date_Time = today "Red" --going out of date today

else if Agreed_Solved_Date_Time > Today +1 Orange--going out of date tomorrow

else if Agreed_Solved_Date_Time > Today +2---going out of date in 2 days or more

else if Agreed_Solved_Date_Time < Today "Blue"--out of date

So I needto work out the formula for theis for conditional formatting next :(
Thanks

SZ1
Learning and development is the driving force in the universe...!

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-27 : 07:11:42

You might want to consider adding some custom code to your report. It keeps the expressions nice and clean and give you a little more flexibility, plus, if you end up having more conditions it's easier to modify the custom code than it is to have IIF statemenst 10 levels deep! (Well in my humble opinion is it !)

If you pass your Agred_Solve_date_time field to this fucntion it should work.

So add this as custom code first (go to report properties and then custom code)


Public Function fnGetConditionalColor(pDate as datetime) AS String

	Dim d AS integer
	Dim c as string

	d= DateDiff("d", pDate, Now)

	IF d<0 THEN 
		c="Blue"
	ELSE IF d=0 THEN 
		c="Red"
	ELSE IF d=1 THEN 
		c="Orange"
	ELSE 
		c="Green"
	END IF

	Return c

End Function

And then set the color property of the cell you want to colour to


=Code.fnGetConditionalColor(Fields!Agreed_Solve_Date_Time.Value)

and that should do the trick.

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-27 : 09:59:33

Hi

Thats interesting, just updated the field name and it works...the colours are not the right way around yet but looks promising...

I want it red if the Agreed_Solved_Date_Time is todays date or one day before it, to show the actual date is reaching the Agreed_Solved_Date_Time
Green for 2 days and all other days before the Agreed_Solved_Date_Time, so anything that is someway off to reaching the Agreed_Solved_Date_Time
and Blue for anything thats gone over the Agreed_Solved_Date_Time

Can this be done with the function?
Thanks

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-27 : 10:37:31

Hi its close but the red is overlapping into yesterdays date, this should also be blue not red...any ideas?
Thanks

you can see here
http://pcfixerman.com/index.php/screenshot

I removed the orange dont really need that...

Public Function fnGetConditionalColor(pDate as datetime) AS String

Dim d AS integer
Dim c as string

d= DateDiff("d", pDate, Now)

IF d<0 THEN
c="Green"
ELSE IF d=0 THEN
c="Red"
ELSE
c="Blue"
END IF

Return c

End Function

SZ1
Learning and development is the driving force in the universe...!

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-27 : 10:45:41

Yes you just need to change the logic slightly. I'll explain what it currently does and that might help you to help yourself in the future.


Public Function fnGetConditionalColor(pDate as datetime) AS String 

	Dim d AS integer
	Dim c as string

	d= DateDiff("d", pDate, Now)

	IF d<0 THEN 
		c="Blue"
	ELSE IF d=0 THEN 
		c="Red"
	ELSE IF d=1 THEN 
		c="Orange"
	ELSE 
		c="Green"
	END IF

	Return c

End Function

First line defines the function's name, it's input parameter(s) and what type of data the function will return. So in this case we called it fnGetConditionalColor, then told it to expect a datetime to be passed in as a parameter called pDate and finally we want our function to return a string.

The next two lines define some variables. 'd' will be used to stored the number of days we are away from the Agreed date. 'c' will be used to stored the name of the colour that we want to pass back to our report.

The next line uses the DateDiff function to set our variable d to the number of days (hence the "d" as the 1st parameter) between our passed in date pDate and the current date Now.

Once we have determined the number of days we then use IF ELSE IF END IF to set 'c' to the correct color name.

In the version above it basically reads
If the number of days is less than 0 (in other words the date has already passed) then set 'c' to "blue". If not then if number of days is 0 (it's due today) then set it "red", if the number of days is 1 (due tomorrow) then set it to "orange" and in all other cases set it to "green"

The next line tells the function to return the value in 'c' (our colour name)
and finally, the close finish the function definition with
End Function.

In your report all you are doing is calling this function and passing in the date field, the function returns a string containing the colour name which then gets applied to the color property.

To modify it to use your new conditions just change the IF ELSE bit, try the following.


IF d<0 THEN 
		c="Blue"
	ELSE IF d>=0 and d <=1 THEN 
		c="Red"
	ELSE 
		c="Green"
	END IF

I can't test any of this so it might not be spot on but it should give you enough to work with.

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-27 : 11:00:09

Thanks for explaining I will take note of that.

It looks nearly there but where its red it needs only to be today or tomorrow (coming up to the date) everything that has passed the solved date needs to be blue (out of date). the 25th and the 26th are red but they should be blue, strangley though the 25th has both red and blue colours?

I swapped the red around from your last statement...

Public Function fnGetConditionalColor(pDate as datetime) AS String

Dim d AS integer
Dim c as string

d= DateDiff("d", pDate, Now)

IF d<0 THEN
c="Green"
ELSE IF d>=0 and d <=1 THEN
c="Red"
ELSE
c="Blue"
END IF
Return c

End Function

SZ1
Learning and development is the driving force in the universe...!

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-27 : 11:35:45

Sorry my mistake, I was doing it from memory and SSRS uses VB.Net. The VB.Net DatDiff function behaves differently than the T-SQL datediff function.

The one we are using is trying to calculate whole days (i.e 24 hours)so that's why there is a discrepancy.

Luckily it's simple to fix.

Just change

d= DateDiff("d", pDate, Now)

to

d= DateDiff("d", pDate.Date, Now.Date)

What this does is essentially chop the times off the dates when they are compared (effectively setting both dates to midnight).

This should give you the correct result (he says, fingers crossed)

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-27 : 11:42:21

Also I'm being really stupid!! The Dates are the wrong way round in the datediff, if you revert to my earlier code (the IF ELSE bit) and then changethe datediff to read

d= DateDiff("d", Now.Date, pDate.Date)

Then it shoudl work (or be closer). The idea is that if the agreed date has passed the number will be negative. I think my ealier logic then works OK.

IF d<0 THEN
c="Blue"
ELSE IF d>=0 and d <=1 THEN
c="Red"
ELSE
c="Green"
END IF

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-27 : 11:48:54

yes noticed it was changing when the time was changing...:)
Thats great mate well chuffed with that...its all looging good now...only thing I would like to see is tomorrows date red too like todays, tomorrows is Green at the moment but this would be good for red to show its getting cloe to breach date, that way plenty of warning will show instead of the current date going red.

Apart from that great stuff...
cheers :)

SZ1
Learning and development is the driving force in the universe...!

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-27 : 12:09:46

I would have thought the last IF statement I posted would have done that but it's hard to tell as I can't test it right now.

What you can do is create another function that just returns the difference (our d variable) then dump that on the report so you can see exactly what the IF statement is working with.

Something like this (just add it somewhere after the previous function a couple of lines down from the 'END Function' statement that you already have)


Public Function testGetDaysDifference(pDate as datetime) AS Integer

	Dim d AS integer

	d= DateDiff("d", pDate, Now)

	Return d

End Function

Then add anew column to your report and set the value expression to
=Code.testGetDaysDifference(Fields!Agreed_Solve_Date_Time.Value)

This will show the datediff result and from there you should be able to workout what numbers need changing in the IF statement in the proper function.

Post a screenshot if it still doesn't work and I'll remember to take my headphones off while I look at it !

sz1
Aged Yak Warrior

555 Posts

Posted - 2013-02-27 : 12:18:12

Thanks a lot I can work with that :)

cheers

SZ1
Learning and development is the driving force in the universe...!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources