SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 percentage formatting
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

Alan Schofield
Starting Member

United Kingdom
23 Posts

Posted - 02/27/2013 :  12:50:10  Show Profile  Reply with Quote
Your welcome mate.. but one again I forgot to add ".Date" in the datediff function, best just to copy that line from your existing function. Too tired, I did a 22hr stint yesterday followed by 2hrs sleep, it's taking it's toll!
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
451 Posts

Posted - 03/04/2013 :  06:21:57  Show Profile  Reply with Quote
Hi Alan,

Thats some shift mate no wonder you were knacked!

Yeah I have this in the code:


Public Function fnGetConditionalColor(pDate as datetime) AS String

Dim d AS integer
Dim c as string
d= DateDiff("d", pDate.Date, Now.Date)
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

works well now today is red tomorrow is green and everything older than today is blue :)

Is there a quick way to sum the numbers for this so:
sum(=Code.fnGetConditionalColor(Fields!Agreed_Solved_Date___Time.Value "Red"))

so I can add the number of tickets for each color below a colour key text field?
?Thanks

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
451 Posts

Posted - 03/04/2013 :  06:42:32  Show Profile  Reply with Quote
someone like this even though this wont work:


Public Function fnGetCountColor(pDate as datetime) AS String

Dim d AS integer
Dim c as string
d= DateDiff("d", pDate.Date, Now.Date)
IF d<0 THEN
count(c="Green")
ELSE IF d >=0 and d <=1 THEN
count(c="Red")
ELSE
count(c="Blue")
END IF

Return c

End Function

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

Alan Schofield
Starting Member

United Kingdom
23 Posts

Posted - 03/04/2013 :  07:00:58  Show Profile  Reply with Quote
It really depends on what you current dataset looks like, but to be honest if you want to showe this as a seperate section of the report (eg. after all the detail) then it's probably as easy to do this in another dataset.

So, aggregatethe data into your three groups and just return those three values.

Assuming you are using SQL Server try something like.


SELECT 
	SUM(CASE WHEN DATEDIFF(d,Agreed_Solve_Date,getdate()) <0 THEN 1 ELSE 0 END) AS Blue,
	SUM(CASE WHEN DATEDIFF(d,Agreed_Solve_Date,getdate()) IN (0,1) THEN 1 ELSE 0 END) AS Red,
	SUM(CASE WHEN DATEDIFF(d,Agreed_Solve_Date,getdate()) >1 THEN 1 ELSE 0 END) AS Green
FROM MyTable 


This will return a simple 3 column single row result something like.


Blue   Red   Green
 123   456     789


Note the datediff function works slightly differently in T-SQL, it just compares the date portions and ignores the time part (which is what you want).
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
451 Posts

Posted - 03/04/2013 :  09:29:18  Show Profile  Reply with Quote
Hi

I created this and its returning ok but the numbers dont add up?

code:

SELECT /*c.OCCURED_DT, AGREED_SOLVED_DT, c.STATUS_NAME, ASSIGNED_GRP_NAME,*/
SUM(CASE WHEN DATEDIFF(d,AGREED_SOLVED_DT,getdate()) <0 THEN 1 ELSE 0 END) AS Blue,
SUM(CASE WHEN DATEDIFF(d,AGREED_SOLVED_DT,getdate()) IN (0,1) THEN 1 ELSE 0 END) AS Red,
SUM(CASE WHEN DATEDIFF(d,AGREED_SOLVED_DT,getdate()) >1 THEN 1 ELSE 0 END) AS Green
FROM DIM_CALL c
Where c.STATUS_NAME Not IN('Closed','Resolved')
And c.TYPE = 'Incident'
And ASSIGNED_GRP_NAME IN ('L1','L2','L3')
--Group by OCCURED_DT,
--AGREED_SOLVED_DT,
--STATUS_NAME,
--ASSIGNED_GRP_NAME


Result:
Blue Red Green
81 116 49908

There is only 289 records, too many reds and the greens is counting too many even though I'm only asking for open records...
The actual ticket total is 289

15 red
10 green
264 blue

Any idea?
Thanks

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

Edited by - sz1 on 03/04/2013 09:47:57
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
451 Posts

Posted - 03/04/2013 :  11:32:28  Show Profile  Reply with Quote
I got around it another way by adding a datediff column and working it out from there based on number of days...

Thanks

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

Alan Schofield
Starting Member

United Kingdom
23 Posts

Posted - 03/04/2013 :  11:51:20  Show Profile  Reply with Quote
Glad you got it working, not sure why previus query didn't work as it's really simple, it just add 1's up where there is a match and 0's where there is no match.

if you did SELECT COUNT(*) FROM Dim.Call WHERE {your previous WHERE clause} that should match the number of records that my previous query listed in total.

Anyway, as 'they' say, if it ain;t broke, don't fix it... Or if you're a SOftware Engineer, "If it ain't broke, it ain't got enough features yet!"

:)
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
451 Posts

Posted - 03/04/2013 :  12:06:44  Show Profile  Reply with Quote
lol mate cheers

SZ1
Learning and development is the driving force in the universe...!
Go to Top of Page

sz1
Constraint Violating Yak Guru

United Kingdom
451 Posts

Posted - 03/05/2013 :  10:30:32  Show Profile  Reply with Quote
Just wondering is it possible to group the code by the colour?

Public Function fnGetConditionalColor(pDate as datetime) AS String

Dim d AS integer
Dim c as string
d= DateDiff("d", pDate.Date, Now.Date)
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...!
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000