Forcing data rows where no relevant data in DB

[PMRiley]

I have a report that is working correctly but it now needs changing to force it to display info where data may not exist in the database.

Current (simplified) setup of the report...

3 tables - Contract, Person, Area (Must remain in that order for the report to work correctly)

The report is grouped by Area, then by contract and then by person ref.

In the GF2 section, there are a series of counts providing the required details for the report.

Some contract groups will list 2 contracts, others up to 20 depending on how many contracts have people on them.

What I need the report to do now, is to show GF2 for every contract regardless of it having a person on that contract or not so that the contract group footer (GF2) always lists 20 rows (or whatever the maximum number of contracts is).

Any guesses? If you need any more info, just let me know!

(CRXI, SQL Express 2005)

 

[RobbieB]

I'm making some assumptions here. One of them is that there is a contract number even if there is not a "person" related to that contract number. Is that correct? If so, you would need to do a left outer join on the person and area tables and create a formula that says:

if isnull ({person.person_ref}) or {person.person_ref} = ""
then
" No person related to contract"
else
{person.person_ref}

//in this formula Person.person_ref represents your database field and the "no person related to contract" text can be changed to whatever you want it to be including an empty string

Then change group 3 (person ref) to group on this formula.

I hope I understood your problem and that this helps.

 

[PMRiley]

Thanks for that suggestion, unfortunatly that doesn't cure the problem.

The only time that the formula returns the 'No person related to this contract' text is for 4 'duff' records that exist in the DB (That I can never be bothered to remove!)

I think you have understood the problem fine and all links were left outers anyway.

I suspect that the problem lies in the fact that the 1st group (area) relies on getting data from the Person table which then means that the report is effectivly looking at the People table as the 1st table even though the contracts table should be the starting point.

Any other suggestions will be most gratefully recieved!

 

[Turkbear]

Hi,
In which tables are the needed fields?
Is Area in Contracts, People or just in Area?

If linked as you describe:
Contract -LO-> Person -LO-> Area , then yes, unless a Person record links to a matching Area one, no Area will be returned, hence no Group entry

Try
Contract --LO--> Person
Contract --LO--> Area

That way, as long as Contract can have a link to Area, no person record will be involved/needed..

This is just a guess,however, without knowing the structure of, or relationships between, the tables..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[PMRiley]

Sorry Turkbear, I should have made that a bit clearer.

The contracts table has no area stored in it. (Just 20 contracts). the Person table has the area code as it relates to the area that that person belongs to as well as the contract that that person is on. Therefore I cannot link the contracts table to the area table.

I'm thinking that there is no way that this can actually be done without rewriting the entire report using 000's of formulas to manually count for each contract!

 

[RobbieB]

You could create a formula similar to the one I mentioned before to account for Null or "" areas. This would account for all persons and/or contracts not assigned an area.

if isnull ({area.area}) or area.area = ""
then
" No area associated"
else
{area.area}
It would group all of the people without areas together but you would get the contract info.

Good luck