April 11, 2008 at 2:08 pm
This might be a simple one but I just can't figure out why.
In my report, I just want to add a grand total of a number column. In the footer cell on that column of body section, I typed in =sum(fields!units.value), but when I preview the report, that cell shows #Error. Don't know what's wrong.
THANKS!
April 14, 2008 at 2:32 am
Hi,
Are you sure that the field in the query is returning a numeric data type?
for example if I use
select '1' as MyNum
union (select '2')
union(select '3')
and try to add a summed column on MyNum, it will give an error.
However
select 1 as MyNum
union (select 2)
union(select 3)
will work fine.
Are you trying to convert or format the field in question at any point in the data query?
April 14, 2008 at 9:32 am
check your output window, it should have something about the error...
Ben Sullins
bensullins.com
Beer is my primary key...
April 14, 2008 at 12:44 pm
In the query, the column is like this:
ROUND ( ( (wl.WorkDone * tut.ConversionFactor) / 60), 1) [Time Spent (Hours)]
so it is a numerical field.
April 14, 2008 at 2:09 pm
Did the output window reveal anything?
Ben Sullins
bensullins.com
Beer is my primary key...
April 14, 2008 at 3:32 pm
This is the revealing:
-------------------------------------------------------------------------------------------
Build complete -- 0 errors, 0 warnings
[rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox8’ contains an error: Operation is not valid due to the current state of the object.
Preview complete -- 0 errors, 1 warnings
[rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox8’ contains an error: Operation is not valid due to the current state of the object.
Preview complete -- 0 errors, 1 warnings
[rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox8’ contains an error: Operation is not valid due to the current state of the object.
Preview complete -- 0 errors, 1 warnings
April 15, 2008 at 1:44 am
Could you please post the entire query used as the data source?
I've had this error before when using the DateDiff function, so I'm wondering if you are using datediff either in the query or in report groupings ?
If so, this post might be relevant.
http://blogs.msdn.com/bimusings/archive/2005/09/13/464836.aspx
April 15, 2008 at 2:35 pm
I didn't use dateadd function:
---------------------------------------------------------------------
SELECT wl.ItemId [Project ID], F._Features_NAME AS ProjectName, P.NAME as ProjectCategory, wlt.name as HourType, CONVERT(CHAR, wl.WorkLogDateTime, 101) [Date],
case when datename(dw, wl.WorkLogDateTime) = 'Monday' then wl.WorkLogDateTime
when datename(dw, wl.WorkLogDateTime) = 'Tuesday' then dateadd(day, -1, wl.WorkLogDateTime)
when datename(dw, wl.WorkLogDateTime) = 'Wednesday' then dateadd(day, -2, wl.WorkLogDateTime)
when datename(dw, wl.WorkLogDateTime) = 'Thursday' then dateadd(day, -3, wl.WorkLogDateTime)
when datename(dw, wl.WorkLogDateTime) = 'Friday' then dateadd(day, -4, wl.WorkLogDateTime)
when datename(dw, wl.WorkLogDateTime) = 'Saturday' then dateadd(day, -5, wl.WorkLogDateTime)
when datename(dw, wl.WorkLogDateTime) = 'Sunday' then dateadd(day, -6, wl.WorkLogDateTime)
end as WeekName,
u.FirstName [User], f._UsersAssignedTo_FirstName as AssignedTo, f._UsersRequestedBy_FirstName as RequestedBy, f._Custom_177_Sector as Sector,
ROUND ( ( (wl.WorkDone * tut.ConversionFactor) / 60), 1) [Time Spent (Hours)]
into #WorkLog
FROM WorkLog wl
left JOIN MR_vwFeatures f ON wl.ItemId = f._Features_FeatureId
left JOIN Projects p ON f._Projects_ProjectId = p.ProjectId
left JOIN TimeUnitTypes tut ON wl.WorkUnitTypeId = tut.TimeUnitTypeId
left JOIN Users u ON wl.UserId = u.UserId
left JOIN WorkLogTypes wlt ON wl.WorkLogTypeId = wlt.WorkLogTypeId
WHERE wl.ItemTypeId = 1
AND p.Path LIKE '%|29|%'
and P.NAME <> 'Dev'
AND wl.WorkLogDateTime >= dateadd(week, -2, getdate())
ORDER BY wl.WorkLogDateTime desc, wl.ItemId
--------------------------------------------------------------------------------------------------------------
select isnull(a.date, b.date) as Date,
isnull(a.WeekName, b.WeekName) as WeekName,
isnull(a.projectname, b.projectname) as project,
isnull(a., b.) as ,
totalbusinesshours,
totalafterhours
from
(select date, WeekName, ProjectName, coalesce(HourType, 'Work - Business Hours') as HourType, [User], SUM([Time Spent (Hours)]) as TotalBusinessHours
from #worklog
where date >= dateadd(day, -30, getdate())
and coalesce(HourType, 'Work - Business Hours') = 'Work - Business Hours'
group by date, WeekName,ProjectName, coalesce(HourType, 'Work - Business Hours'), [User]
)a
full join
(
select date, WeekName, ProjectName, coalesce(HourType, 'Work - Business Hours') as HourType, [User], SUM([Time Spent (Hours)]) as TotalAfterHours
from #worklog
where date >= dateadd(day, -30, getdate())
and coalesce(HourType, 'Work - Business Hours') = 'Work - After Hours'
group by date, WeekName, ProjectName, coalesce(HourType, 'Work - Business Hours'), [User]
) b
on a.date=b.date
and a.projectname = b.projectname
order by 1 desc
April 15, 2008 at 2:36 pm
sorry I mean i didn't use the datediff function.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply