Tough Crystal Reports 2008 Grouping and Summary question

[jj1576]

I've been trying to figure out a grouping issue in Crystal reports on and off for months now- what I am trying to do is summarize a point total for an employee entered as a test result. There are a number of tests for an asset in this particular database. All have a score of 0 if they are in spec, and -10 if they are out of spec. The only result that doesn't score is the employee name. What I want to do is to group each set of results by that employee name test result (all results, both tests and employees are entered in the same test result field). Currently, the time field time is the only common link and they are grouped by this- however, each group will have an employee test result entered. Example below(table names and fields on top row, "score" is a formula):

Group: 24-OCT-2010 14:24:17

Testmainlist. Testmainlist. Testresults. testresults. score
Probetype testdescription Testresult outofspec

Employee Employee Smith,F 243 NA NA
Test Windows Clean? No Yes 10
Test Ledges Yes No 0


Score for group: 10

Group: 25-OCT-2010 09:44:32

Testmainlist. Testmainlist. Testresults. testresults. score
Probetype testdescription Testresult outofspec

Employee Employee James,F 755 NA NA
Test Windows Clean? Yes NO 0
Test Ledges No Yes 10
Test Bed made? No Yes 10

Score for group: 20

End of report summary: 100 - average score:

Smith, F 243 10
James, F 755 20



The idea is to have a summary at the end of the report of the average score for each employee. The only thing that separates them is in the Left column which is the Test Category.

The report runs on a date range and is ordered by time. This means an employee can show up several times in different places.in the report, and there are several different employees. I need to isolate the score for each employee for the average at the end of the report- (in short an average for EACH employee). I know this is a complicated explanation, but hopefully you can see what I'm driving at. Can this be done? Please let me know if I have left out any details.

Note- when I posted this the example had even columns- please let me know if they don't make sense.

Thanks!

 

[lbass]

In the field explorer, do you see the option to create SQL expressions? if so, you could create a SQL expression {%empl} like this:

(
select max(B.`TestResult`)
from Testmainlist A, Testresults B
where A.`datetime` = B.`datetime` and
A.`Probetype` = 'Employee' and
A.`testdescription` = 'Employee' and
A.`datetime` = Testmainlist.`datetime`
)

The syntax depends upon your CR version (in versions 9.0 to XI you couldn't specify the alias table name within the summary function), and the punctuation depends upon your datasource.

You should be able to insert a group #1 on this, leaving datetime as your group #2. You should also be able to insert a crosstab in the report footer that does an average of score (depending upon the content of your formula, which you should have shown).

-LB

 

[jj1576]

The formula is a simple if then statement:

if {ProbeType.ProbeTypeDescription} = "test" and {TestResults.OutOfSpec} = true then 10
else if {ProbeType.ProbeTypeDescription} = "test" and {TestResults.OutOfSpec} = false then 0 else if
isnull({TestResults.OutOfSpec}) then 0

I'll give what you suggested a try and post a response with the result.

Thanks!

 

[lbass]

Your formula will not work correctly unless the null check is first:


if isnull({TestResults.OutOfSpec}) then
0 else
if {ProbeType.ProbeTypeDescription} = "test" and
{TestResults.OutOfSpec} = true then
10 else
if {ProbeType.ProbeTypeDescription} = "test" and
{TestResults.OutOfSpec} = false then
0

-LB

 

[jj1576]

lbass,

Thanks- I updated the @score formula.

I also tried the code you suggested. I put it in as follows:

(
select max(B.`TestResults`)
from Testmainlist A, Testresults B
where A.`testid` = B.`testid` and
A.`Probetype` = 'Employee' and
A.`testdescription` = 'Employee' and
A.`testid` = Testmainlist.`testid`
)


A few things are different from yours because the 2 tables are linked by a field called "testid" I forgot to include the linking- sorry about that. So I replaced the lines where I thought the link was referenced (I'm rough at SQL statements).

I'm not sure if the linking changes anything. Also, I am using an Access database with an ODBC connection- not sure if that is important.

One final note- when I put in the formula, it gives me a "too few parameters- expected 3" error. Previous experience tells me that this has to do with syntax of field names? The field names look correct, so I'm assuming it;s the syntax. Any thoughts?

 

[lbass]

Show me exactly how your SQL query displays in database->show SQl query, as I am unsure of the punctuation. Also, I used the datetime for a reason--please plug that back in instead of of the testID field, which I assume is unique to each row.

-LB

 

[jj1576]

lbass,

Ok- sorry about that. I put the formula in as you said and got the too few parameters error- expected 5. Yes- the testid field is unique.

The SQL query for the report is long- not sure if you wanted all of it. here it is:



SELECT `AssetType`.`AssetDescription`, `Site`.`SiteID`, `AssetList`.`DateOffSystem`, `TestResults`.`TestDate`, `TestResults`.`ResultText`, `TestMainList`.`Description`, `Site`.`SiteName`, `ProbeType`.`ProbeTypeDescription`, `TestResults`.`OutOfSpec`
FROM ((((((`Site` `Site` INNER JOIN `Area` `Area` ON `Site`.`SiteID`=`Area`.`SiteID`) INNER JOIN `Location` `Location` ON `Area`.`AreaID`=`Location`.`AreaID`) INNER JOIN `AssetList` `AssetList` ON `Location`.`LocationID`=`AssetList`.`LocationID`) INNER JOIN `AssetType` `AssetType` ON `AssetList`.`TypeID`=`AssetType`.`TypeID`) INNER JOIN `TestResults` `TestResults` ON `AssetList`.`AssetID`=`TestResults`.`SubjectID`) INNER JOIN `TestMainList` `TestMainList` ON `TestResults`.`TestID`=`TestMainList`.`TestID`) INNER JOIN `ProbeType` `ProbeType` ON `TestMainList`.`ProbeType`=`ProbeType`.`ProbeTypeDescription`
WHERE (`TestResults`.`TestDate`>={ts '2011-06-01 00:00:00'} AND `TestResults`.`TestDate`<{ts '2100-01-01 00:00:01'}) AND `Site`.`SiteID`=1 AND `AssetList`.`DateOffSystem` IS NULL AND `AssetType`.`AssetDescription`='Patient Room' AND (`ProbeType`.`ProbeTypeDescription`='Employee' OR `ProbeType`.`ProbeTypeDescription`='Test')
ORDER BY `Site`.`SiteName`, `TestResults`.`TestDate`

 

[lbass]

Your previously shown column labels don't seem to correspond to the SQL query above. The following is my best guess. Try this just like this and if it compiles, place it in the detail section to see if you get the correct result on EACH line of the testdate group. If you do, you are good to go and can group on this formula and use it in a crosstab.

I'm not sure whether you will have to add the Site table or other criteria that are currently in your record selection formula, but try this first:

(
select max(B.`ResultText`)
from TestMainList A, TestResults B
where A.`TestID`=B.`TestID` and
A.`Description` = 'Employee' and
B.`TestDate` = TestResults.`TestDate`
)

-LB

 

[jj1576]

lbass,

Thanks- The site shouldn't be needed. It is there just to show which site or client the report is being run for. I ran the code as is and it gave me a "too few parameters" error with "expected 1" this time.

After reviewing the code the only thing I could come up with is the resulttext field is only in one of the tables- testresults. Would this cause a problem? Thanks.

 

[lbass]

No, that doesn't matter. I thought {TestResults.ResultText} was the field that returns the employee name. If all fields are correctly named for their particular table then I am at a loss about why you are getting that message.

-LB

 

[jj1576]

Ok- well thanks for all your help- I'm at least a lot closer to figuring it out. I'll have to examine it and play around a little to see if I can get it to run.

 

[jj1576]

LBASS,

One further thought on this if you don't mind- when I typed it is as below it worked:

(
select min(B.`ResultText`)
from TestMainList A, TestResults B
where A.`TestID`= B.`TestID` and
A.`Description`= 'Employee'
)


except that it selected just one name from the whole list and ignored all parameters in the report. So it seems that you were right in that it needs more detail.
I just took out reference to the date, as the date line:

B.`TestDate` = TestResults.`TestDate`

was the problem- the date was only in the testresult table and there is no reference to date in testmainlist.
By the way, employee name is in the testresult.resulttext field.

 

[lbass]

No, that is not the problem unless the field is not actually named {TestResults.TestDate}. The line:

B.`TestDate` = TestResults.`TestDate`

...is setting an alias of the field = to the field itself, creating a group on TestDate within the subquery. You need that to define the group within which the maximum (not the minimum) should be tested.

Note also that a SQL expression goes directly to the database, and does not honor the report's selection formula, so you have to build in relevant criteria --which was why I asked about the Site table. If employees are unique to a site then you are all set. Similarly, the date field is irrelevant once you build in the date clause, since the expression will only appear for TestDates that are selected for the report (the clause acts as the "link").

-LB

 

[jj1576]

lbass,

I put "min" in there just because i was playing around a little and didn't realized I hadn't changed it back when I pasted it.

The only other clue the error gives me other than the "too few parameters expected 1" is that it is a "Database connector error DAO Error Code 0xbf5" Not sure what that could mean- other than the wrong field as you suggested.

 

[jj1576]

lbass,

I go it to work- I just started a new report and did the SQL Query first, and then added the tables. For some reason, it compiled without an error.

The only other "requirement" was that I add all the tables after it was created and then group it by the expression. If I grouped it before finishing adding the tables it would fail to retrieve the data. Thanks so much for your help- this was a good learning experience for me.