have a cross tab show all field values, not just ones being used

[jschill2628]

Is there a way to have a cross tab, and have all the values from a field. What I mean by this is when I create a cross tab, to show me count of Incidents, it only shows me those codes that are being used, I wasn’t to create a quality report for all codes, even those codes that are not being used.

For example: Currently my crosstab looks like this:

Abrasion/Laceration 52
Birth Related Injury-NOC 51
Burn 50
Cardiovascular 41
Dental Damage 35
Dermatological, NOC 27
Disease 11
Embolus/DVT/PE 8

I want it to look like this:

Abrasion/Laceration 52
Birth Related Injury-NOC 51
Burn 50
Cardiovascular 41
Dental Damage 35
Dermatological, NOC 27
Disease 11
Embolus/DVT/PE 8
Financial 0
Foreign Body 0
Fracture/Sprain 0
Gastrointestinal, NOC 0
Genitourinary, NOC 0
Hematoma/Bruise 0
Infection/Sepsis 0
Insufficient Information 0
IV/Catheter Related 0
Loss of Consciousness 0
Loss of organ/body part 0


Thank you

 

[briangriffin]

Use a command object:

select codeID, codeDesc from codetable

Then do a left outer join from the command to the detail table (probably using codeID). Make sure you group on the code description from the command.

That will give you one record for every unused code, and one or more records for each code that was used.

You can substitute a database table for the command if there is a table that only contains code descriptions.

 

[jschill2628]

Okay now what if I cannot join a table, because not all my fields are required, so I have hard coded some.

I just want to list out the values that are not being used.

So from the above, the folowing fieldsa are being populated:
Abrasion/Laceration, Birth Related Injury-NOC, Burn, Cardiovascular , Dental Damage
Dermatological, NOC, Disease, Embolus/DVT/PE

The following fields sare not v=being populated (or are null)
Financial, Foreign Body, Fracture/Sprain, Gastrointestinal, NOC, Genitourinary, NOC, Hematoma/Bruise, Infection/Sepsis, Insufficient Information, IV/Catheter Related, Loss of Consciousness, Loss of organ/body part

I want a list for a specified time from of all the fields that are null.

Is this at all possible? I have nearly everything I know.

 

[jschill2628]

I have tried to do a subreport where I have my main table {ClaimTable} and my Code table {CodeTable} linked {ClaimTable.Specila 3} to {CodeTable.CODE} with a left outer join, enforced, !=

In my selection criteria of the subreport I have the code type = "10" to pull out the codes for that field.
and then I have the {CodeTable.DESC} in the details, to list all the possible values.

I have the main report and the subreport linked:
{CodeTable.CODE} = {?Pm-ClaimTable.SPECIAL3}

I know i am doing something wrong because I only get one description field to show up, and that description field is being used aready.

Can you please help.

Thanks, using Crystal 2008.

 

[jschill2628]

I hate to keep rambling, nut I am trying to give you as much information as I can without (hopefully) confusing you. Say we go to the example at the top , and I know that 23 of my values are null, which is great, but what I want to do is show that list of the codes that are not being used.

 

[Madawc]

Try a 'Mock Crosstab'. This is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5 or Crystal 2008, you can also duplicate formula fields using the Field Explorer.

Each running total will count the record if it was within the criteria.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP