SQL reporting Service Week days sum

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 2005 Forums

Analysis Server and Reporting Services (2005)

SQL reporting Service Week days sum

Author

Topic

koolmission
Starting Member

4 Posts

Posted - 2008-02-04 : 09:11:02

hello please hlp

i have one report which list weekdays sum between two days

but the problems is that how i will calculate the week days sum

start date = ------- end date= ------

date | day | job| hours worked| total hours worked

-------------------------
week day sum hours workd=0

date | day | job| hours worked| total hours worked

-------------------------
week day sum hours workd=0

date | day | job| hours worked| total hours worked

-------------------------
week day sum hours workd=0

it should look like this ....

i can list out the week days between two dates but i dont know how to get sum of week day and show above format is there any programming methods are there to calculate the weeksays as field

please help me.....

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 09:34:35

Do you have a calendar table which has info about week days & holidays? Else, you might have to use DATEPART function to get day equivalent and use it in a CASE statement to determine whether working day or not. The display format can be done in RS designer by using a table container.

koolmission
Starting Member

4 Posts

Posted - 2008-02-04 : 22:20:04

hey buddy thanks for the reply
but the problem is how i will implement in SQL reporting service
2005.

i can use report designer design the rest of the things except the week day sum.. its not in stored prcedure it shuld calculate the week day sum in sql reporting service using any varibale or something...

i have one report which list weekdays sum between two days

but the problems is that how i will calculate the week days sum

start date = ------- end date= ------

date | day | job | hours worked | total hours worked

01-02-2008 Friday 12 30 4
02-02-2008 Saturday 12 30 4
03-02-2008 Sunday 12 30 4
05-02-2008 monday 12 30 4
06-02-2008 tuesday 12 30 4
07-02-2008 thursday 12 30 4

-------------------------
week day sum hours workd=180

08-02-2008 Friday 12 10 4
09-02-2008 Saturday 12 10 4
10-02-2008 Sunday 12 10 4
11-02-2008 monday 12 10 4
12-02-2008 tuesday 12 10 4
13-02-2008 thursday 12 10 4

-------------------------
week day sum hours workd=60

14-02-2008 Friday 12 30 4
15-02-2008 Saturday 12 30 4
16-02-2008 Sunday 12 30 4
17-02-2008 monday 12 30 4
18-02-2008 tuesday 12 30 4
19-02-2008 thursday 12 30 4

-------------------------
week day sum hours workd=180

it should look like this ....

i can list out the week days between two dates but i dont know how to get sum of week day, for a range of seven days and show above format is there any programming methods are there to calculate the weeksays as field

i want to list like this the week day = weekstartday +week end day how i implement this

please help me i am new to this SQl reports please give me some suggestion wih examples and links

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-04 : 23:25:19

The sum must be calculated in SSRS using SUM() function. You can place it on table footer of table where you are showing the SP data. To provide you more detail, you must give more deatil on what all fields you return from sp with some sample data.
b/w are you using some grouping in your tables as i see data in groups above (or is it only for illustration purpose?)

jery007
Starting Member

2 Posts

Posted - 2008-02-06 : 16:36:10

Funny but thats exactly what i was doing last week
and had many problem with it till i found my salvation

i found a reporting/analytics tool that connects to sql
in three simple steps, you define your date field
and from there its simple as hell you don't even need to know sql

its called Prism and as far as i can tell its free now (beta)
you can find it in http://www.sisense.com

btw: does any one here heard about this product?

hope i helped

koolmission
Starting Member

4 Posts

Posted - 2008-02-07 : 10:41:55

let me clear the things

i a have got a table

[dbo].[TimeEntry](
[TimeEntryId] [int] IDENTITY(1,1) NOT NULL,
[CreatedBy] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[CreatedDate] [datetime] NOT NULL DEFAULT (getdate()),
[UpdatedBy] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UpdatedDate] [datetime] NOT NULL DEFAULT (getdate()),
[ActiveFlag] [bit] NULL DEFAULT ((1)),
[InternalTimesId] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UserId] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShiftStartTime] [datetime] NULL,
[ShiftEndTime] [datetime] NULL,
[LunchStartTime] [datetime] NULL,
[LunchEndTime] [datetime] NULL,
[RegularTime] [float] NULL,
[OverTime] [float] NULL,
[DoubleTime] [float] NULL,
[CurrentShiftType] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TotalJobs] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TotalJobTime] [float] NULL,
[TotalTravelTime] [float] NULL,
[TotalJobTraveledMiles] [float] NULL,
[MilesTraveled] [float] NULL,
[TeamsId] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TeamName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

and the stored procedure is

create PROCEDURE [dbo].[rptTimeEntryWeekwise]
(
@FromDate datetime = NULL
, @ToDate datetime = NULL
, @UserId varchar(50) = NULL
, @UserName Varchar(50) = NULL
, @TeamsId varchar(50) = NULL
, @TeamName varchar(50) = NULL

)
AS
BEGIN

DECLARE @tmp1 TABLE -- used for storing weekends between a daterange
(
weekstart datetime
,weekend datetime
, WeekCount int
)

DECLARE @CorporateWeekEnd int -- Week end for company
, @CorporateWeekStart int
-- Week Start for Company
, @DaysLeft int -- days Left
, @DaysBefore int --Week Start
, @WeekStarting datetime -- days to reach Week end
, @WeekEnding datetime -- days to reach Week end
, @WeekCount int

SET @CorporateWeekEnd = 7 -- Initialize company week end as Saturday ( ie day 7).
SET @CorporateWeekStart =1 -- Initialize company week end as Monday ( ie day 2).

SET @WeekCount =0

WHILE @FromDate<=@ToDate
BEGIN
SET @DaysLeft = @CorporateWeekEnd - datepart(weekday, @FromDate)
SET @DaysBefore = datepart(weekday, @FromDate)-@CorporateWeekStart
SET @WeekStarting = @FromDate-@DaysBefore --dateadd(day,@DaysLeft,@FromDate)
SET @WeekEnding = dateadd(day,@DaysLeft,@FromDate)

SET @WeekCount = @WeekCount + 1
INSERT INTO @tmp1
Values(@WeekStarting,@WeekEnding,@WeekCount)

SET @FromDate= DATEADD(DAY,@DaysLeft+1,@FromDate)
END

--select * from @tmp1
CREATE TABLE #timetable
(
TDate datetime
,Tday varchar(20)
--,Tjob varchar(20)
--,TotalHours varchar(20)
--,DailyTotal int
, FirstName varchar(20)
, HoursWorked int
, WeekCount int
)

declare @startDate datetime
declare @endDate datetime
declare @HoursWorked int
declare @FirstName varchar(20)

DECLARE R_Cursor CURSOR FOR
select weekStart,weekEnd, WeekCount from @tmp1

OPEN R_Cursor
FETCH NEXT FROM R_Cursor INTO @startDate,@endDate, @WeekCount
WHILE @@FETCH_STATUS = 0
BEGIN

while @startDate<@endDate
begin
--select @startDate

select @FirstName=dbo.userinfo.FirstName, @HoursWorked =
CASE WHEN (datediff(dd,@startdate,ShiftEndTime) > 0 AND datediff(dd,ShiftStartTime,@startdate) > 0)
THEN 24
ELSE CASE WHEN datediff(dd,@startdate,ShiftEndTime) = 0 AND datediff(dd,ShiftStartTime,@startdate) = 0
THEN datediff(hh,ShiftStartTime,ShiftEndTime)
ELSE CASE WHEN datediff(dd,@startdate,ShiftEndTime) = 0
THEN datediff(hh,@startdate,ShiftEndTime)
ELSE CASE WHEN datediff(dd,ShiftStartTime,@startdate) = 0
THEN 24 + datediff(hh,ShiftStartTime,@startdate)
ELSE 0
END
END
END
END

from dbo.TimeEntry,dbo.userinfo
WHERE dbo.TimeEntry.UserId = @UserId
and dbo.userinfo.UserId=dbo.TimeEntry.UserId
AND (datediff(dd,@startdate,ShiftStartTime) = 0
OR datediff(dd,@startdate,ShiftEndTime) = 0
OR (@startDate > ShiftStartTime AND @startDate < ShiftEndTime)
)

insert into #timetable
Values(@startdate,datename(dw,@startdate),@FirstName,ISNULL(@HoursWorked,0),@WeekCount)
set @startDate=dateadd(day,1,@startDate)

end

--Select @startDate,datepart(day,@startDate),job, from timeEntry Where shiftStartTime<=@startDate and shiftEndTime<=@endDate
FETCH NEXT FROM R_Cursor INTO @startDate,@endDate, @WeekCount

END
CLOSE R_Cursor
DEALLOCATE R_Cursor
select * from #timeTable
DROP TABLE #timetable
end
return 0

any way i can bring up the rows but

how i implement week day total in Reports

how i use varibles in report service to calculate the week sum and day sum in the following format

for every week the sum should show below

by design i dont know its possible or not

could you please look around it and give me helpul suggestion

it will be great.........

start date = ------- end date= ------

date | day | job | hours worked | total hours worked

01-02-2008 Friday 12 30 4
02-02-2008 Saturday 12 30 4
03-02-2008 Sunday 12 30 4
05-02-2008 monday 12 30 4
06-02-2008 tuesday 12 30 4
07-02-2008 thursday 12 30 4

-------------------------
week day sum hours workd=180

08-02-2008 Friday 12 10 4
09-02-2008 Saturday 12 10 4
10-02-2008 Sunday 12 10 4
11-02-2008 monday 12 10 4
12-02-2008 tuesday 12 10 4
13-02-2008 thursday 12 10 4

-------------------------
week day sum hours workd=60

14-02-2008 Friday 12 30 4
15-02-2008 Saturday 12 30 4
16-02-2008 Sunday 12 30 4
17-02-2008 monday 12 30 4
18-02-2008 tuesday 12 30 4
19-02-2008 thursday 12 30 4

-------------------------
week day sum hours workd=180

in crystal reports we can use varibles and culcuate the week day sum
but in SQL reporting how it can be implement....

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources