Home » SQL & PL/SQL » SQL & PL/SQL » Matrix with dates/time
icon7.gif  Matrix with dates/time [message #187426] Sun, 13 August 2006 23:52 Go to next message
jewelfire
Messages: 2
Registered: August 2006
Location: New Zealand
Junior Member
hey there
I realise there are lots of questions on matrix.

But for the life of me I can not see how to do this

I am using Visual Studio.net and using the Reporting Services window.
Making a weekly timesheet.
so in the layout I have added a matrix.

which has 2 columns 2 rows so far.

1st column row 1 blank
1st column row 2 = =Fields!CallSubject3.Value
2nd column row 1 = =Fields!WorkDate.Value
2nd column row 2 = =Format(Sum(Fields!WorkHours.Value) + (Sum(Fields!WorkMins.Value) /60), "#.##")

I have a group on callsubject3
and a group on workdate

What my problem is: I need to sum each days total hours = 8
and then sum the total weekly hours = 40

what do I do to get that?

thanks
jewel



Re: Matrix with dates/time [message #187432 is a reply to message #187426] Mon, 14 August 2006 00:37 Go to previous messageGo to next message
Littlefoot
Messages: 20894
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
"Visual Studio.net and Reporting Services" - as far as I can tell, Visual studio.net isn't product of Oracle Corp. I don't know about Reporting Services (Report Builder yes, but "Services"?).

Unless data you are fetching is stored in an Oracle database, what does your problem have to do with Oracle? Did you, perhaps, miss the Forum (as this is OraFAQ forum)? If not, I apologize (and no, I can't help you - I don't know anything about products you use).

[Updated on: Mon, 14 August 2006 00:38]

Report message to a moderator

Re: Matrix with dates/time [message #187594 is a reply to message #187426] Mon, 14 August 2006 10:33 Go to previous messageGo to next message
markmal
Messages: 113
Registered: April 2006
Location: Toronto, Canada
Senior Member
Probably you can do it in MSRS, however I am pretty much sure you can calculate it right in Oracle. Use SQL for Analysis functions.

something like

select CallSubject, WorkDate, WorkHours+WorkMins/60,
sum(WorkHours+WorkMins/60)
over (partition by CallSubject, trunc(WorkDate) as SumHrsByDay,
sum(WorkHours+WorkMins/60)
over (partition by CallSubject, to_char(WorkDate,'WW') as SumHrsByWeek
from TheTable
order by CallSubject, WorkDate;

addon: to be precise, you better do not use WorkHours+WorkMins/60 expression, better use to_date( WorkHours||':'||WorkMins,'HH24:MI') or some other way to operate with minutes. May be trunc((WorkHours*60+WorkMins)/60)+mod((WorkHours*60+WorkMins),60)

[Updated on: Mon, 14 August 2006 10:39]

Report message to a moderator

Re: Matrix with dates/time [message #187648 is a reply to message #187594] Mon, 14 August 2006 16:02 Go to previous message
jewelfire
Messages: 2
Registered: August 2006
Location: New Zealand
Junior Member
thanks Markmal I will have a look at your suggestions.
Yes Littlefoot my data is stored in an Oracle Database hence posting in here. But the way we do it here I have read only access to the Database and all my reporting is done in Visual Studio / Reporting Services.

cheers for the help
jewel
Previous Topic: raise_application_error function and unicode problem
Next Topic: Oracle
Goto Forum:
  


Current Time: Mon Dec 05 12:46:29 CST 2016

Total time taken to generate the page: 0.11130 seconds