Formula for Bad Data

[mwake]

I'm using CR2008 with an Oracle database as my data source. The reports that I am creating are all at the coutry level, and all countries are associated to a region (ex France belongs to the region EU). The problem is that, for reasons you don't want to hear, the developers decided to create EU as a country in addition to a region. This allows the user to create a single EU record which would apply to all countries with a REGION of EU.

The reports I'm developing are registration reports by country. Issue 1 is how do I take all records with a country of EU and make duplicate records for each country with a region = EU. The end users want to see the EU country record appear under each country with a region equal to EU. Issue 2 is then to be able to take the newly created records for each EU country and compare them to a table that contains deregistration records in order to determine which countries have been deregistered.

Any ideas on how to handle this would be appreciated!!

 

[lbass]

Please show some samples of how region and country display now and then show a second sample of how you would like them to display. Show both group and detail levels, please.

-LB

 

[mwake]

Current model
Region:EU(group)
Country:EU(group)
Detail:
Product Submission Approval License Status
Aspirin 1/1/2000 6/30/2005 12345 Marketed

Country:France
Valium 2/1/2001 7/31/2007 67895 Not Marketed
Zanax 4/1/2004 8/1/2008 98765 Marketed

Country:Germany
Ritalin 5/1/2003 9/1/2005 11223 Marketed

New Look:
Region:EU(group)
Country:EU(group)
Detail:
Product Submission Approval License Status
Aspirin 1/1/2000 6/30/2005 12345 Marketed

Country:France
Aspirin 1/1/2000 6/30/2005 12345 Marketed
Valium 2/1/2001 7/31/2007 67895 Not Marketed
Zanax 4/1/2004 8/1/2008 98765 Marketed

Country:Germany
Aspirin 1/1/2000 6/30/2005 12345 Marketed
Ritalin 5/1/2003 9/1/2005 11223 Marketed

So each record with a region of EU and a country of EU should appear under each country that has a region of EU but not a country of EU. I can't use a subreport because I need to compare the new records to another table with deregistration records that are at the country level. If there is a record in the dereg table with the same combination of country/drug product/operation, then don't include that record

 

[lbass]

I'm having trouble mocking up a report like this, but I would try adding an alias of the table that links the table1.country to table2.region with a left outer join. Then place region and country from both tables in the detail section to see the resulting dataset. Then it might be obvious how to create a formula to group on. As I say, I'm not sure.

I also wouldn't rule out using a subreport. You could suppress subreport values using shared variables.

-LB