Totals in Matrix dont match up to Query

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

Development Tools

Reporting Services Development

Totals in Matrix dont match up to Query

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-08-25 : 23:09:18

Hi guys I have a problem, the Colums in the matrix exactly in the order they should be but when I run a query the totals I get are completely differnt then the ones I am getting on the matrix and I dont understand why.

here is the query I ran


SELECT     People_tbls.[Parent First Name], People_tbls.[Parent Last Name], Tanf_tbl.[Earned hours], Tanf_tbl.[Catagory for hours], Tanf_tbl.[Services Covered], 
                      Tanf_tbl.[Contact Date], People_tbls.[Referral Status]
FROM         People_tbls INNER JOIN
                      Tanf_tbl ON People_tbls.[Parent ID] = Tanf_tbl.[Parent ID]
WHERE     (Tanf_tbl.[Contact Date] BETWEEN CONVERT(DATETIME, '2009-07-01 00:00:00', 102) AND CONVERT(DATETIME, '2009-07-31 00:00:00', 102)) AND 
                      (People_tbls.[Referral Status] = N'ACTIVE')
ORDER BY People_tbls.[Parent Last Name]

this is what this gave me for this one is 57 but on RS in the matrix I get 56

Armendariz 1
Armendariz 1
Armendariz 4
Armendariz 1
Armendariz 1
Armendariz NULL
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 4
Armendariz 1
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 4
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2
Armendariz 2

and for this one = 92 but on the RS it says 10

Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 2
Francisco Beltran 4
Francisco Beltran 4
Francisco Beltran 4
Francisco Beltran 4
Francisco Beltran 2
Francisco Beltran 1
Francisco Beltran 2
Francisco Beltran 1
Francisco Beltran 2
Francisco Beltran 4
Francisco Beltran 2

is there something wrong with the query I'm running

[code]SELECT People_tbls.[Parent ID], People_tbls.[Parent Last Name], People_tbls.[Referral Status], TanfActivity_tbl.[Contact Date], TanfActivity_tbl.[Earned hours],
TanfActivity_tbl.[Catagory for hours], TanfActivity_tbl.[Services Covered], People_tbls.[Parent First Name]
FROM People_tbls INNER JOIN
TanfActivity_tbl ON People_tbls.[Parent ID] = TanfActivity_tbl.[Parent ID]
WHERE (People_tbls.[Referral Status] = N'Active') AND (TanfActivity_tbl.[Catagory for hours] IN (N'ITP', N'Parenting', N'Education', N'Travel Time', N'Cultural',
N'Job Skills Training')) AND (TanfActivity_tbl.[Contact Date] BETWEEN @Beginning_ContactDate AND @End_ContactDate)
ORDER BY CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'ITP' THEN 0 ELSE 1 END,
CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Parenting' THEN 0 ELSE 1 END,
CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Job Skills Training' THEN 0 ELSE 1 END,
CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Travel Time' THEN 0 ELSE 1 END,
CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Cultural' THEN 0 ELSE 1 END,
CASE WHEN TanfActivity_tbl.[Catagory for hours] = N'Education' THEN 0 ELSE 1 END, TanfActivity_tbl.[Catagory for hours],
TanfActivity_tbl.[Services Covered], People_tbls.[Parent Last Name][/code[]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 10:50:37

whats the expression you gave for getting total? or did you just added the subtotal column?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources