How to suppress this criteria?

[geestrong]

Hello,

I am using CR XI and Oracle 9 DB

I trying to figure how to suppress this criteria correctly. There are applications that come in. Based on the type of application, they go to certain agencies (ie Application Type 1 may go to agency1, agency2, agency3, agency4 or Application Type 2 will go to agency1, agency2, agency3).

The report is to display all the following agencies with approvals for the following agencies: agency2, agency3, agency4 and agency1 has a hold or has no decision(i.e. null).

In a nutshell the reports should display the holds of application in agency1, but all the other agencies must have approved it:

Application type1 12345
Agency 1 - hold
Agency 2 - Approved
Agency 3 - Approved
Agency 4 - Approved

They do not want to see a application that looks like this
Application type1 12666
Agency 1 - Hold
Agency 2 - Approved

I have used to suppressed: Section Expert-> Suppress(No Drill-Down) -> formula, but I am still getting the latter example in the report. Equally important, I must suppress for all the other application type too!

Thanks,

 

[synapsevampire]

Rather than trying to explain what your data looks like, try posting example data, and the expected output.

We don't know if the agency is different fields or all in one field, don't assume that we're familiar with your database.

-k

 

[geestrong]

I have the report group by application Number with is a contcat of application Type and number
group 2 is agency with a specific order with B-Pln Rev firs t all other agency
Group 3 is Decision ( Approved, HOLD, or NO Decision)

Here is a sample of how the data looks like:'
Group 1(Application) BLR2 200600181
Group 2(Agencies) B-Plan Rev HOLD
Arborist Approved
Zoning Approved
SCert Approved
SiteRev Approved

I only need to display applications that are on HOLD in B-Pln REV and approved for all other agencies. The issue I am having is not displaying applications like this:

Group 1 BLR2 2OO602222
Group 2(Agencies) B-Plan Rev HOLD
Arborist Approved
Zoning Approved
SCert Approved

Were as the this application HAS NOT been to SiteRev.

I hope this is clearer.

Thanks,
Gee

 

[lbass]

First insert a group on Try making a separate formula for each agency like:

//{@B-Plan Rev}:
if {table.agency} = "B-Plan Rev" and
{table.status} = "HOLD" then 1

//{@Arborist}:
if {table.agency} = "Arborist" and
{table.status} = "Approved" then 1

//{@Zoning}:
if {table.agency} = "Zoning" and
{table.status} = "Approved" then 1

//etc.

Then go to report->selection formula->GROUP and enter:

sum({@B-Plan Rev},{@application}) = 1 and
sum({@Arborist},{@application}) >= 1 and
sum({@Zoning},{@application}) >= 1 and
sum({@SCert},{@application}) >= 1 and
sum({@SiteRev},{@application}) >= 1

-LB

 

[geestrong]

LB,

I have tried your solution and it appears to be working. I have to next apply it to multiply applications types!

Thanks

 

[geestrong]

I am revisiting this topic.

The method used to suppress the criteria works, but it works when there is one decision on an application. There are times when there may be multiple decision on the application for Planning agency (i.e, where there may be a HOLD, and now there is a APPROVAL.) This does not need to show up in the report.

I am trying to figure out how to suppress this application from the report when there are multiple decisions.

Here is how the data looks and is grouped:
Group 1 Application Number
Group 2 Agency
Group 3 Decision (note forcing APPR first then HOLD)

BLR1 20051235
B-PLN REV
APPR
HOLD

Thanks

 

[lbass]

Change the {@B-Plan Rev} formula to:

if {table.agency} = "B-Plan Rev" and
(
isnull({table.status}) or
{table.status} = "HOLD"
) then 0 else 1

Then change the group selection formula to:

sum({@B-Plan Rev},{@application}) = 0 and
sum({@Arborist},{@application}) >= 1 and
sum({@Zoning},{@application}) >= 1 and
sum({@SCert},{@application}) >= 1 and
sum({@SiteRev},{@application}) >= 1

-LB

 

[geestrong]

Thanks again,

It worked!

geestrong