Crosstab Informix & Excel Data Usage

[paulcook]

I am creating a report using CR8.5 on WinXP.

Most of the data is in an Informix database. I have some contract plan numbers that are not stored in this database. I have them in an Excel spreadsheet, but that format can be changed if need be.

I have a crosstab that has three formulas as rows so that I can see their education status, in or out of school, their enrollment status in our program, new or carryin, and age status, older or younger. For the column I'm using a field straight out of the database, agency code, so that I can have the report show the data for each agency. For the summarized field I have a distinct count of the clients case number.

I need to put a column in the crosstab that will show the contract plan numbers for each agency next to their actual numbers and a third column that will calculate the percent of the plan that they have achieved. Is this possible using an external file, like excel, using crosstabs?

What should the layout of that external file be? For now I have it like this:

agency | InNewEnrlY | InNewEnrlO | .......
GG0 | 6 | 5 | .......
IR0 | 3 | 2 | .......

This is my first foray into crosstabs, so I really appreciate all help. If I can figure this out it would save my self and my coworkers alot of time as we have to print out three reports and input the data into an Excel spreadsheet.

TIA,
Paul

 

[lbass]

It looks like you are working on a WIA Youth report. As far as I know, you cannot use two different datasources in a crosstab. In CR 8.5 you also cannot have summary columns displayed side by side, and I’m not sure that percentages using distinctcounts can be achieved. I think you would have to use a manual crosstab. If you didn't have too many agencies, you could hard-code the plan figures in a formula which could then be used in a manual crosstab.

I'm assuming you have different plan figures for each row value and if you had 6 agencies, you would then have 64 plan figures. You could write a formula like {@plan} (here I'm using only three agencies):

if {table.edstat} = "I/S" then
(if {table.enrstat} = "C-in" then
(if {table.agestat} = "Older" then
(if {table.agency} = "ABC" then 15 else
if {table.agency} = "DEF" then 12 else
if {table.agency} = "GHI" then 24) else
if {table.agestate} = "Younger" then
(if {table.agency} = "ABC" then 8 else
if {table.agency} = "DEF" then 10 else
if {table.agency} = "GHI" then 9)) else
(if {table.enrstat} = "New" then
(if {table.agestat} = "Older" then
(if {table.agency} = "ABC" then 10 else
if {table.agency} = "DEF" then 112 else
if {table.agency} = "GHI" then 22) else
if {table.agestate} = "Younger" then
(if {table.agency} = "ABC" then 18 else
if {table.agency} = "DEF" then 4 else
if {table.agency} = "GHI" then 6)) else
if {table.edstat} = "O/S" then //etc.

To create the manual crosstab, insert groups on educational status, enrollment status, and age status. Then create three running totals for each agency to be placed in the group footers (one for each group):

//{#ABCactual}: Select {table.caseID}, distinctcount, evaluate for each record, reset on change of group (three running totals, just changing the group used for the reset)

For each agency, create a formula:

//{@ABCPlan} to be placed in the detail section:
if {table.agency} = “ABC” then {@plan}

Right click on this formula and insert a maximum at all group levels.

Then create three formulas, one for each group level, to be placed in the group footers:

//{@ABCpercentIn/OutSch}:
{#ABCactualIn/OutSch} % maximum({@ABCPlan},{table.educstat})

//{@ABCpercentenrstat}:
{#ABCactualenrstat} % maximum({@ABCPlan},{table.enrstat})

//{@ABCpercentagestat}:
{#ABCactualagestat} % maximum({@ABCPlan},{table.agestat})

Then suppress the detail section.

-LB