Totals in Report are Inflated

[TammyToth]

Hi, everyone. This is my first posting so if I don't supply all of the necessary information, I apologize in advanced.

I am using CR 8.5, using Elite Information Systems database which is located on our SQL Server.

I am creating creating a report which has some of the following columns:

Name Title Exp.Level FTE LTM Jan-04 Dec-03 Nov-03

When I generate the report, it returns the correct number of names, but my totals are inflated because some of the people appearing under the name column belongs to multiple practice groups and I only need to report on one particular group (group 240).

My SQL looks like this:

SELECT
timekeep."tkinit", timekeep."tklast", timekeep."tkfirst", timekeep."tktitle", timekeep."tktmdate", timekeep."tkloc",
udf."udtype", udf."udfindex", udf."uddate",
timecard."tmatter", timecard."tworkdt", timecard."tworkhrs",
udf_1."udtype", udf_1."udfindex",
matter."mnonbill",
deptlab_1."label5"
FROM
{ oj (((("son_db"."dbo"."timekeep" timekeep INNER JOIN "son_db"."dbo"."timecard" timecard ON
timekeep."tkinit" = timecard."ttk")
INNER JOIN "son_db"."dbo"."udf" udf_1 ON
timekeep."tkinit" = udf_1."udjoin")
INNER JOIN "son_db"."dbo"."udf" udf ON
timekeep."tkinit" = udf."udjoin")
INNER JOIN "son_db"."dbo"."deptlab" deptlab_1 ON
udf_1."udvalue" = deptlab_1."delcode")
INNER JOIN "son_db"."dbo"."matter" matter ON
timecard."tmatter" = matter."mmatter"}
WHERE
timekeep."tktitle" = 'Associate' AND
timekeep."tktmdate" IS NULL AND
timekeep."tkloc" = '01' AND
udf."udfindex" = 13 AND
udf."udtype" = 'TK' AND
udf_1."udfindex" = 1 AND
udf_1."udtype" = 'TK' AND
deptlab_1."label5" = '240'

When I remove the following information:

udf_1."udfindex" = 1 AND
udf_1."udtype" = 'TK' AND
deptlab_1."label5" = '240'

my totals are correct, but the incorrect number of people appear in my report. When I place the same information back in, the correct number of people appear, but the totals are inflated. I also tried to use Select Distinct Records and this reduces my totals too low.

Any ideas would be greatly appreciated.

Thanks for your help.
Tammy

 

[synapsevampire]

If you only need group 240, add something to the Report->Edit Record Selection->Record which states:

{table.group} = 240

It sounds like your getting record inflation because of the joins, so you may have to use a different tact, such as using a View in SQL Server, or a subreport in Crystal.

-k