Graph Average Max Values of Records Problem

[Mav3000]

Hi All,

I have a report in CR10 which has data as follows:

Records are grouped by 'Contract' and within each Contract are Sites. Each Site can have multiple Audits.

Report Header (Here's the problem):
-----------------------------------
Need it to Display Chart of 'Average' 'Max' value of Audit Score for the Contracts. HOW?

Group 1 (Contract):
-------------------
Displays Chart of 'Max' Audit_Score for each Site. DONE

Group 2 (Site):
---------------
Displays Chart of Audit_Scores Vs Audit_Dates. DONE

--> Details
-------
(Audits): Audit_Date (Date), Audit_Score (num). DONE

-----------------------

My problem is - how do I summarise via an average, just the maximum value of Audit_Score in the Report Header?

Currently it averages all Audit_Score values, but I only want it to average the highest - at Report Header level.

Hope this is clear enough!

Any input or ideas would be appreciated.

Richard

 

[lbass]

Try creating a SQL expression {%maxsite}:

(
select max(`Audit_Score`)
from table A
where A.`site` = table.`site`
)

The punctuation will be specific to your datasource.

Then you should be able to use an average of {%maxsite} as your 'show value', on change of contract.

-LB

 

[Mav3000]

Hi LB,

I've never done a SQL Expression in Crystal before - could you just clarify whcih pices of the expression I need to replace with my table / field information?

So far I have:

(
select max("tbl_Audit"."N_Audit_Score")
from table A
where A.`site` = table.`site`
)

Do I need to substitue 'A'"?

Is 'site' the path of the site table?

Thanks,

 

[Mav3000]

Just to let you know what tables I have - I have:

tbl_Sites
tbl_Contracts
tbl_Jobs
tbl_Audits

all are linked one-to-many in descending order above.

My Report is ordered:

1. Contracts (GH1)

2. Sites (GH2)

3. Audits (Details)

 

[lbass]

Is this Oracle? Please identify your site field (the one you are grouping on) by using {table.field}.

-LB

 

[Mav3000]

Hi LB,

It's connected to a SQL Server Database.

The fields that the database is grouped on is:

GF1: Contracts.Reference

GF2: Sites.Reference

Details: Audits

The Audits are grouped per Site, the Sites are then grouped per Contract. This is slightly out-of-order to the actual table relationships, due to how I'm needing to show the data.

Richard.

 

[lbass]

Do the table names have the prefix tbl_ or not? Does the sites table have a field that can be directly linked to a field in the the audits table? What are these fields?

-LB

 

[Mav3000]

Hi Lbass, yes - the tables have the prefix tbl in them. Exactly as the primary keys appear in the tables, this is the relationship:

tbl_Sites / SiteID

tbl_Contracts / ContractID

tbl_Jobs / JobID

tbl_Audits / AuditID (foreign key Job ID)

--------------------

As above, the Report is ordered as follows:

1. Contracts (GH1) Group: tbl_Contracts.Name

2. Sites (GH2) Group: tbl_Sites.Name

3. Audits (Details) Group: by tbl_Sites.Name

I don't group by ID because this is not meaningful to the users of the report when the list of sections is shown down the left. The 'name' fields are sufficient.

Everything sums up ok to the GH1 level, but I cannot sum just the maximum values shown in the chart at GH1 level into an average chart at RH level.

Regards,

Richard

 

[lbass]

You did not answer my question: Does the sites table have a field that can be directly linked to a field in the the audits table? Or are they only linkable by linking through the intermediary tables?

-LB

 

[Mav3000]

Hi, Sorry, no - the Audit Table is ONLY linked to the Job Table immediately above it.

This in turn links up through the database hierarchy through Jobs, Contracts and then Sites via the IDs listed in my previous post.

Thanks, Richard

 

[lbass]

I'm not sure whether you have to specify the intervening tables in the SQL expression. Please try this and see if the correct values are returned:

(
select max("N_Audit_Score")
from "tbl_Audit" A, "tbl_Sites" D
where D."Name" = "tbl_Sites"."Name"
)

-LB

 

[Mav3000]

Hi Lbass,

I have created a new SQL expression and have entered the following. This has the actual table names in it - I simplified them in the posts above to keep things clearer.

(
select max("N_Audit_Score")
from "dtblDMC2092Jobs_HAS_Audit" A, "dtblDMC2092Sites" D
where D."Name" = "dtblDMC2092Sites"."Name"
)

The hierarchy of the actual tables I have is:

dtblDMC2092Sites
dtblDMC2092Options
dtblDMC2092Jobs
dtblDMC2092Jobs_HAS_Audit

Each level has an ID field, and a foreign key which references only the table above it's ID field.

---------------

The Report Structure is as follows:

Group1: dtblDMC2092Options.Name

Group2: dtblDMC2092Sites.Name

Details: various fields from dtblDMC2092Jobs_HAS_Audit

As mentioned above, everything works ok, apart from showing the Average of just the Maximum of each Sites "N_Audit_Score"s - for each option.

Regards, Richard

 

[lbass]

And what results do you get for the SQL expression? Are they accurate?

-LB

 

[Mav3000]

Hi Lbass,

When I check the SQL statement in the SQL Expression Editor, I get a Query Engine Error: 42000 - 'The column prefix 'dtblDMC2092Sites' does not match with a table name or alias name used in the query'.

I've checked that the table name is spelt correctly, but as SQL Expressions are a little out of my depth, I'm not sure what the problem is.

Regards, Rich

 

[lbass]

In your main report what fields link to what fields of the four tables? Please use the convention {table.field}.

-LB