Cross Tab Problems

[Crusz]

To be quite honest I am way over my head with this. I have never used Crystal Reports and am surprised I have even gotten this far. I’m not sure if I have one problem or multiple problems going on. (I searched the forum for help and found a thread that said to put each Cross Tab into a separate header of footer section but this did not produce different results. ) Any help would be greatly appreciated.

I am trying to reproduce a report that has been used for years, and lost when a server crashed. Unfortunately for me no backups could be found and I have inherited the task of trying to replicate this report.

The user can submit a “to and from” date for the search.

The first Cross Tab (Application Counts) is working correctly by itself. It determines if an application was submitted or is pending. (All application are given a (SUBM_DT) date of 9999,12,31 (pending) when the application is started.) When the user hits the submit key, when the application is properly filled out, the current date is then placed in the SUBM_DT.

When I add the second Cross Tab to the report instead of getting 20 rows from the Application Counts Cross Tab I only get 4 rows returned. This Cross Tab is not producing the correct figures.

What the second cross tab is to calculate:

Determine if the application is pending, or has been submitted, and then search another table to determine if there are household members under the age of 20. There can be an unlimited amount of household members under the age of 20.

Both Cross Tabs need to show zero amounts.


Application Counts
Pending Submit Total
10/21/2007 0 49 49
10/22/2007 0 427 427
10/23/2007 0 415 415
10/24/2007 2 383 385
10/25/2007 0 456 456
10/26/2007 1 254 255
10/27/2007 0 29 29
10/28/2007 0 37 37
10/29/2007 0 321 321
10/30/2007 0 446 446
10/31/2007 0 353 353
11/1/2007 1 334 335
11/2/2007 0 333 333
11/3/2007 0 42 42
11/4/2007 0 53 53
11/5/2007 0 287 287
11/6/2007 0 392 392
11/7/2007 0 367 367
11/8/2007 0 372 372
11/9/2007 1 247 248


Daily summary of applications entered through the web and the count of the number of children they
Represent. Running by itself it is correctly showing 20 rows.

This Cross Tab is using one table: MKA01_APPLICATION

It uses the following formula named: Submit_1

If {MKA01_APPLICATION.SUBM_DT} <> Date (9999,12,31) then 'Submit' else
If {MKA01_APPLICATION.SUBM_DT} = Date (9999,12,31) then 'Pending'



The second Cross Tab counts the number of people in the household that are under the age of 20.




Children Counts
Pending Total
11/9/2007 2 0 2
11/14/2007 10 5 15
11/15/2007 4 2 6
Total 16 7 23



Application Counts
Pending Total
11/9/2007 2 2
11/14/2007 15 15
11/15/2007 6 6
Total 23 23



The Children Counts Cross Tab is not showing the column header of ‘Submit’ and the Application Counts Cross Tab is only showing 3 rows instead of 20. The Application Counts is also not producing a ‘Submit’ column at all.

The Children Counts Cross Tab is using 2 tables:

MKB01_MEMBERS & MKA01_APPLICATION

The Children Counts Cross Tab is using the following formulas:

Updatedate (Converts the date timestamp into date only)

DateValue ({MKA01_APPLICATION.UPDT_TST})



BirthDate_2 (Calculates the age of the member as of the time of the timestamp. I need to use this date to calculate the members age at the time the application was last updated and not today’s date)

DateVar BRTH_DATE:={MKB01_MEMBERS.BRTH_DT};
DateVar UPDT_TIMESTAMP:={@updatedate};

If (Month(UPDT_TIMESTAMP)*100)+Day(UPDT_TIMESTAMP)>=(Month(BRTH_DATE)*100)+Day(BRTH_DATE)
then Year(UPDT_TIMESTAMP)-Year(BRTH_DATE)
else Year(UPDT_TIMESTAMP)-Year(BRTH_DATE);



Submit_2 (Is supposed to determine of the person in the household is under 20 and then finds out if the application was submitted or is still pending)

If {@BirthDate_2} < 20 then
If {MKA01_APPLICATION.SUBM_DT} <> Date (9999,12,31) then 'Submit' else
If {MKA01_APPLICATION.SUBM_DT} = Date (9999,12,31) then'Pending'



SQL:

SELECT "MKA01_APPLICATION"."UPDT_TST", "MKA01_APPLICATION"."SUBM_DT", "MKB01_MEMBERS"."BRTH_DT"
FROM "AIDDB2"."MKB01_MEMBERS" "MKB01_MEMBERS" INNER JOIN "AIDDB2"."MKA01_APPLICATION" "MKA01_APPLICATION" ON ("MKB01_MEMBERS"."APPL_ID"="MKA01_APPLICATION"."APPL_ID") AND ("MKB01_MEMBERS"."UPDT_ID"="MKA01_APPLICATION"."UPDT_ID")
WHERE ("MKA01_APPLICATION"."UPDT_TST">={ts '2007-10-21 00:00:00'} AND "MKA01_APPLICATION"."UPDT_TST"<{ts '2007-11-22 00:00:00'})

 

[Crusz]

I apologize for not mentioning I have version XI.

 

[lbass]

You are missing a -1 at the end of this formula:

DateVar BRTH_DATE:={MKB01_MEMBERS.BRTH_DT};
DateVar UPDT_TIMESTAMP:={@updatedate};

If (Month(UPDT_TIMESTAMP)*100)+Day(UPDT_TIMESTAMP)>=(Month(BRTH_DATE)*100)+Day(BRTH_DATE)
then Year(UPDT_TIMESTAMP)-Year(BRTH_DATE)
else Year(UPDT_TIMESTAMP)-Year(BRTH_DATE)[red]-1[/red];

You should also use parens in this one:

If {@BirthDate_2} < 20 then
(
If {MKA01_APPLICATION.SUBM_DT} <> Date (9999,12,31) then 'Submit' else
If {MKA01_APPLICATION.SUBM_DT} = Date (9999,12,31) then'Pending'
)

Note that crosstabs will not show rows if there is no corresponding data.

-LB

 

[Crusz]

I understand that a crosstab will not show rows if there is no corresponding data. Why would the Application Counts Crosstab show 20 rows when run by itself, but when the second Crosstab of Children Counts is placed next to it and the report is run, for the same time span, then only 4 rows of the Application Counts show? Shouldn't it still show 20 rows and the Children Counts Crosstab show 4?

 

[lbass]

You are activating the second table when you actually use a field from it. Should all members be present in the MKB01_MEMBERS table? If so, then I wonder whether your join is at fault. I noticed you joined on an Updt_ID field--I wonder if these fields really match or whether they reflect independent updates in each table. What happens if you remove this join and then place both updt_Id fields in the detail section of the report--do you observe them matching?

-LB

 

[Crusz]

Your suggestion worked. Thanks!