childt table not showing updates in report

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » childt table not showing updates in report

childt table not showing updates in report

Rate Topic

Display Mode

Topic Options

Author

Message

scairdesiree

Posted Wednesday, August 01, 2012 12:22 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 8:48 AM
Points: 4, Visits: 13

I have a problem I have three tables all joined together One is the people.tbl the other the tanfactivity.tbl and the counselors.tbl I am trying to show an active participants report with a date range which is located in the tanfactivity.tbl. I need for users to be able to run the report by the beginning date and end date, showing the updated information. The problem is when a participant changes counselors the updated counselor doesnt show on the report only the last counselor.

here is my select statement can anyone tell me what I'm doing wrong??

SELECT DISTINCT 
                      People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea, 
                      People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.ScairHoursOnly, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita, 
                      TanfActivity_tbl.EventDate, Counselors_tbl.CounselorsName, Counselors_tbl.[Parent ID] AS Expr1, Counselors_tbl.Counselor, 
                      Counselors_tbl.CounselorID
FROM         People_tbl INNER JOIN
                      TanfActivity_tbl ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID] LEFT OUTER JOIN
                      Counselors_tbl ON People_tbl.[Parent ID] = Counselors_tbl.[Parent ID]
WHERE     (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate)
ORDER BY People_tbl.ServiceArea, People_tbl.LastName

Post #1338743

Gazareth

Posted Thursday, August 02, 2012 5:03 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

Hi, is the report cached or a snapshot?
Does the updated value show correctly if you run that SQL in Management Studio?

Cheers

Post #1339058

scairdesiree

Posted Thursday, August 02, 2012 12:58 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 8:48 AM
Points: 4, Visits: 13

Hello the report is cached. And when I run the Select Statement in Management studio This is the error I get. Msg 8120, Level 16, State 1, Line 1
Column 'People_tbl.ScairHoursOnly' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT DISTINCT 
                      People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea, 
                      People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.ScairHoursOnly, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita, 
                      TanfActivity_tbl.EventDate, Counselors_tbl.CounselorsName, Counselors_tbl.Counselor, Counselors_tbl.CounselorID
FROM         People_tbl INNER JOIN
                      TanfActivity_tbl ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID] LEFT OUTER JOIN
                      Counselors_tbl ON People_tbl.[Parent ID] = Counselors_tbl.[Parent ID]
GROUP BY People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea, 
                      People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita, TanfActivity_tbl.EventDate, 
                      Counselors_tbl.CounselorsName, Counselors_tbl.Counselor, Counselors_tbl.CounselorID
HAVING      (TanfActivity_tbl.EventDate BETWEEN CONVERT(DATETIME, '2012-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2012-07-31 00:00:00', 102))

Post #1339468

Daniel Bowlin

Posted Thursday, August 02, 2012 2:54 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:36 AM
Points: 2,681, Visits: 2,423

First, it is a bad idea to hardcode your dates into your query. I will assume for now that it is just for testing purposes

Second using GROUP BY with DISTINCT is unnecessary, use one or the other. Generally I use DISTINCT when I do not need to aggregate (SUM, AVG, etc.) anything, and GROUP BY if I do need to aggregate some values. The error you are getting is because 'People_tbl.ScairHoursOnly' is not in your GROUP BY section. Either remove your GROUP BY section entirely or add 'People_tbl.ScairHoursOnly' to your GROUP BY section and it should work.

Post #1339532

scairdesiree

Posted Thursday, August 02, 2012 2:57 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 8:48 AM
Points: 4, Visits: 13

Thank you very much for your help I appreciate it

Post #1339534

scairdesiree

Posted Thursday, August 02, 2012 3:18 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, August 31, 2012 8:48 AM
Points: 4, Visits: 13

Here is what I did seems to be working now

SELECT People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea,
People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita, TanfActivity_tbl.EventDate,
Counselors_tbl.CounselorsName, Counselors_tbl.Counselor, Counselors_tbl.CounselorID, Counselors_tbl.EffectiveDate
FROM People_tbl INNER JOIN
TanfActivity_tbl ON People_tbl.[Parent ID] = TanfActivity_tbl.[Parent ID] LEFT OUTER JOIN
Counselors_tbl ON People_tbl.[Parent ID] = Counselors_tbl.[Parent ID]
GROUP BY People_tbl.[Parent ID], People_tbl.FirstName, People_tbl.LastName, People_tbl.Weekly, People_tbl.Month, People_tbl.ServiceArea,
People_tbl.ReferralStatus, People_tbl.ScairCaseWorker, People_tbl.TANF, People_tbl.Adult_Child, People_tbl.Manzanita, TanfActivity_tbl.EventDate,
Counselors_tbl.CounselorsName, Counselors_tbl.Counselor, Counselors_tbl.CounselorID, Counselors_tbl.EffectiveDate
HAVING (TanfActivity_tbl.EventDate BETWEEN @Beginning_EventDate AND @End_EventDate)
ORDER BY People_tbl.LastName, Counselors_tbl.EffectiveDate DESC

Post #1339541

« Prev Topic | Next Topic »

Permissions