Crystal XI - Set up an order of precedence query 1

[marisam]

Hello All,

I need help your desperately.

Is it possible to set up an order of precedence query and have the results display in a table format.

Here is my table:
Data Table
APPNO Country Status Number
APP01 US Grant 966333
APP01 EP Pub 5566
APP01 GB Pub 67676
APP01 JP Pub 5555
APP02 US Flg 9090
APP02 EP Grant 123545
APP02 GB Pub 54656765
APP02 JP Pub 8778678
APP03 US DES
APP03 EP DES

I need to have certain order of precedence I must follow the following order of precedence.
Order of Precedence
Ctry Status
1. US Grt
2. US Pub
3. EP Grt
4. EP Pub
5. GB Grt
6. GB Pub

If the Application has a US grant then we select the US Grant and move on to the next Application Number.

For example:
So in the above example for AppNo#1 , the US Grant would be selected
APPNO Country Status Number
APP01 US Grant 966333
Then I would move onto the next application number, AppNO #2
For AppNo#2, we would select the EP Grant

APPNO Country Status Number
APP02 EP Grant 123545

Then I would move onto the next application number, AppNO #3
For AppNo#3, we would NOT select any country because it doesn't have the event grant or pub but the App# should be appear.


APPNO Country Status Number
APP03 US DES
APP03 EP DES

In the end the final result, should have at least one entry for each appno



APPNO Country Status Number
APP01 US Grant 966333
APP02 EP Grant 123545
APP03

 

[lbass]

Create a formula {@sort}:

select {table.country}+" "+{table.status}
case "US Grt" : 1
case "US Pub" : 2
case "EP Grt" : 3
case "EP Pub" : 4
case "GB Grt" : 5
case "GB Pub" : 6
default : 7

Use this formula as your sort field, ascending. Then insert a group on {table.appno} and then drag your other fields into the group header and suppress the detail section and group footer.

-LB

 

[marisam]

Thanks this worked great!

However,I need some further assistance. Apparently, the database isn't as reliable as I thought. The only way to make sure I'm getting the correct data is to use the date fields associated with the status.
The order of predence is still relevant however I have to base it on whether the date fields for each status are not null.
Data
APP Ctry Stat Pub # Pub Date Grt #Grt Date
APP1 US GRT 333 1/03
APP1 EP Pub 5566 1/00
APP1 GB Pub 67 1/03
APP1 JP Pub 5555 1/04
APP2 US Flg
APP2 EP GRT 545 2/09
APP2 GB Pub 6765 4/03
APP2 JP Pub 8778 5/06
APP3 US DES
APP3 EP DES

I need to have certain order of precedence I must follow the following order of precedence.
Order of Precedence
Ctry Status Grant # Pub #
1. US Grt Is not null
2. US Pub Is null Is not null
3. EP Grt Is not null
4. EP Pub Is null Is not null
5. GB Grt Is not null
6. GB Pub Is null Is not null



For example,

So in the above example for AppNo#1 , the US Grant would be selected because the grant date is not null
APPNO Country Status Number
APP01 US Grant 966333
Then I would move onto the next application number, AppNO #2
For AppNo#2, we would select the EP Grant because the grant date is not null


How do I translate that into the case statement? Is this too complicate for a case statement.

Thanks for your help!

 

[synapsevampire]

Switch to an IF:

if isnull({table.date})
and
trim({table.country}) + " " + trim({table.status}) = "US Grt" then
1
else
{table.country}+" "+{table.status} = "US Grt" then
1.5
else
...etc each condition chcking for ISNULL first in each criteria.

-k

 

[marisam]

Thanks. I will give it try now. Although, if I start each if statement with is null I won't be getting the right data back. Will I?

In order to get a US Grt the date must be not null.

 

[marisam]

It seems to work except one thing: an extra country (China) is appearing even though I did not state it in the if then statment. I only want US, FR, DE, GE, EP.
Why is it displaying? How do I only display US, FR, DE, GE, EP?


IF NOT ISNULL({TAB.GRTDATE}) AND
{{TAB.COUNTRY}} = "US" THEN
1
ELSE IF ISNULL({TAB.GRTDATE}) AND NOT ISNULL({{TAB.PUBDATE}}) AND
{{TAB.COUNTRY}} = "US" THEN
2

ELSE IF NOT ISNULL({TAB.GRTDATE}) AND
{{TAB.COUNTRY}} = "EP" THEN
3
ELSE IF ISNULL({TAB.GRTDATE}) AND NOT ISNULL({{TAB.PUBDATE}})AND
{{TAB.COUNTRY}} = "EP" THEN
4
ELSE IF ISNULL({TAB.GRTDATE}) AND NOT ISNULL({{TAB.PUBDATE}}) AND
{{TAB.COUNTRY}} = "WO " THEN
5
ELSE IF NOT ISNULL({TAB.GRTDATE}) AND
{{TAB.COUNTRY}} = "GB" THEN
6
ELSE IF ISNULL({TAB.GRTDATE}) AND NOT ISNULL({{TAB.PUBDATE}}) AND
{{TAB.COUNTRY}} = "GB" THEN
7
ELSE IF NOT ISNULL({TAB.GRTDATE}) AND
{{TAB.COUNTRY}} = "FR " THEN
8
ELSE IF ISNULL({TAB.GRTDATE}) AND NOT ISNULL({{TAB.PUBDATE}}) AND
{{TAB.COUNTRY}} = "FR " THEN
9
ELSE IF NOT ISNULL({TAB.GRTDATE}) AND
{{TAB.COUNTRY}} = "DE " THEN
10
ELSE IF ISNULL({TAB.GRTDATE}) AND NOT ISNULL({{TAB.PUBDATE}}) AND
{{TAB.COUNTRY}}= "DE" THEN
11
ELSE 12

 

[synapsevampire]

Is hould have used parentheticals,an uyoushouldhave tested the formula instead of making incorrent assumptions.

(
if isnull({table.date})
and
trim({table.country}) + " " + trim({table.status}) = "US Grt" then
1
else
if {table.country}+" "+{table.status} = "US Grt" then
1.5
)
else
...etc each condition checking for ISNULL first in each criteria.

Wrap each unique set of statements (hence it seperates the ISNULL) in parnethesis.

The rub with Crystal is that the ISNULL check MUST comne first in a formula (though your unneccessary use of a not null check may work partially, it's wordy and bad coding form).

-k

 

[marisam]

Hi Synapsevampire,
Thanks for the reply.
I did test the formula. Everything I ask for came out. It was nearly perfect. Except for the extra country. I didn't understand why. I will add the parentheticals.


Also there are two different date fields I must check not one.
Ctry Grant date Pub date
1. US Grt Is not null
2. US Pub Is null Is not null
3. EP Grt Is not null
4. EP Pub Is null Is not null
5. GB Grt Is not null
6. GB Pub Is null Is not null

I will change the formula around so the isnull #2 order of precedence is first. #2 reads if the grant date field is blank and the pub has a date then it is a grant.

#1 order of predence reads if grant date field has a date then it is a grant.

(IF ISNULL({TAB.GRTDATE}) AND NOT ISNULL({{TAB.PUBDATE}}) AND {{TAB.COUNTRY}} = "US" THEN
2
ELSE IF NOT ISNULL({TAB.GRTDATE}) AND
{{TAB.COUNTRY}} = "US" THEN
1)


Thanks I will report back.

 

[synapsevampire]

Excellent grasp of the subject, I misunderstood your requirements.

That should work well for you.

-k

 

[marisam]

Hello. I changed the order of the formula so that each statement starts with ISNULL. I also began to place the parentheses around my statements but I get the following error.
"The remaining text does not appear to be part of the formula."

I've shortened the formula. Where am I going wrong? Thanks
Post Parenthesis
((IF ISNULL({{TAB.GRANTDATE}}) AND NOT ISNULL({{TAB.PUBDATE}}) AND
{{TAB.COUNTRY}} = "US" THEN
2
ELSE IF NOT ISNULL({{TAB.GRANTDATE}}) AND
{{TAB.COUNTRY}} = "US" THEN
1)
(ELSE IF ISNULL({{TAB.GRANTDATE}}) AND NOT ISNULL({{TAB.PUBDATE}})AND
{{TAB.COUNTRY}} = "EP" THEN
4
ELSE IF NOT ISNULL({{TAB.GRANTDATE}}) AND
{{TAB.COUNTRY}} = "EP" THEN
3)
ELSE IF ISNULL({{TAB.GRANTDATE}}) AND NOT ISNULL({{TAB.PUBDATE}}) AND
{{TAB.COUNTRY}} = "WO " THEN
(5
ELSE IF ISNULL({{TAB.GRANTDATE}}) AND NOT ISNULL({{TAB.PUBDATE}}) AND
{{TAB.COUNTRY}} = "GB" THEN
7
ELSE IF NOT ISNULL({{TAB.GRANTDATE}}) AND
{{TAB.COUNTRY}} = "GB" THEN
6)
(ELSE IF ISNULL({{TAB.GRANTDATE}}) AND NOT ISNULL({{TAB.PUBDATE}}) AND
{{TAB.COUNTRY}} = "DE " THEN
11
ELSE IF NOT ISNULL({{TAB.GRANTDATE}}) AND
{{TAB.COUNTRY}} = "DE " THEN
10)
ELSE 12)

Help.

 

[synapsevampire]

One problem is double brackets around your fields, don't have time to look at everything now, but c'mon...

-k

 

[marisam]

Hi

I'm not sure what happened but the double brackets aren't in the formula editor.

I will continue to try different combinations.
Thanks for your help. I'm pretty close.

 

[synapsevampire]

Turn off the process TGML when you post, that may help with ghosting characters.

Can you please repost the formula with the corrected use of parens/braces?

-k

 

[synapsevampire]

It appears that you are Not wrapping the
NOTs using parens, please do so.


IF NOT(ISNULL({TAB.GRTDATE}))
AND
{{TAB.COUNTRY}} = "US" THEN
1
ELSE
IF ISNULL({TAB.GRTDATE})
AND
NOT(ISNULL({TAB.PUBDATE}))
AND
{TAB.COUNTRY} = "US" THEN
2
else
...

-k

 

[marisam]

Thanks for the help. I think I know what the problem is.

I'm trying to develop a report where the only thing the user has to do is select the product type via the parameters.

Each app is assigned one product type
APPNO Country Status Number Type
APP01 US Grant 966333 Car
APP01 EP Pub 5566 Car
APP01 GB Pub 67676 Car
APP01 JP Pub 5555 Car
APP02 US Flg 9090 Boat
APP02 EP Grant 123545 Boat
APP02 GB Pub 54656765 Boat
APP02 JP Pub 8778678 Boat
APP03 US DES House
APP03 EP DES House


In the select expert I have one criteria the product type (i.e. car, boat).

I think that even though the results are correct. The report is displaying all application with the product type selected even though I did not ask for that specific country China.

How can I maintain the correct formula and the product type in parameter. Is that possible?

Thanks for your help

 

[synapsevampire]

Since you decided not to post your record selection, I can't tell.

Try placing the condition up front as:

(
{table.field}={?MyParameter}
)
and
(
(
<your series of if conditions>
)
)

-k

 

[marisam]

I did as you suggested and I keep getting a boolean is required here. The if then else statement is highlight.

I have a parameter in my select expert.
({table_product} in [{?product}]

 

[synapsevampire]

You have an open paren, without a closing one.

(
{table_product} in [{?product}]
)

-k