How Do I NOT Group a Field and Get My Query to Run?

[Patrick A]

All,

I have a query that creates 2 calculated fields. I want to Group the
results. When I attempt to run my query,

SELECT CASE WHEN BNB = 'Billable' THEN SumHours ELSE '0' END AS
Billable,
CASE WHEN BNB = 'Non-Billable' THEN SumHours
ELSE '0' END AS NonBillable, SUM(dbo.PAV_Answers.SumHours) AS
TotalHours,
dbo.PAV_Answers.EmployeeName,
dbo.PAV_Answers.Lname, dbo.PAV_NeedWork.NeedWork
FROM dbo.PAV_Answers INNER JOIN
dbo.PAV_NeedWork ON dbo.PAV_Answers.EmployeeID =
dbo.PAV_NeedWork.EmployeeID
GROUP BY dbo.PAV_Answers.EmployeeName, dbo.PAV_Answers.Lname,
dbo.PAV_NeedWork.NeedWork

I get the dreaded "[field name] is invalid in the select list..."
error. I thought setting the "Group By" to "Expression" for the
derived Billable and Non-Billable columns (coming from [BNB]), and SUM
for TotalHours (coming from [SumHours]) would do the trick...but no.

Any suggestions?

[Uri Dimant]

Patrick
I cannot test it since you have not provide the sample data
You have to add BNB column into GROUP BY list

"Patrick A" <par...@stradley.com> wrote in message
news:22aea33b-9ce4-48e1...@e7g2000vbi.googlegroups.com...

[vinuthan]

Hi,

As Uri mentioned, the BNB is missing in the group by.

I was thinking whether we could get the totalhours evaluated at the report
level

Have a simple query like

SELECT
CASE WHEN BNB = 'Billable' THEN SumHours ELSE '0' END AS Billable,
CASE WHEN BNB = 'Non-Billable' THEN SumHours ELSE '0' END AS
NonBillable,

dbo.PAV_Answers.EmployeeName,
dbo.PAV_Answers.Lname,
dbo.PAV_NeedWork.NeedWork
FROM
dbo.PAV_Answers INNER JOIN
dbo.PAV_NeedWork ON dbo.PAV_Answers.EmployeeID =
dbo.PAV_NeedWork.EmployeeID

The your Total hours field value expression should look like
=SUM(Fields!SumHours.Value, DATASETNAME)

Let me know of this helps
Regards
Vinuthan

"Patrick A" wrote:

> .
>