How to combine data with separate row count in Crystal Reports 3

[choochoowinn2009]

Hi,

Would someone help me write the codes for this? I am not sure how to do the row count as it looks at each row. Would I need to do a sub-report? Any suggestions would help me a lot.

Here's my data:

ID Assistance
5 Medicaid
5 Medicare
5 Food Stamps
24 Medicaid
49 Food Stamps
119 Medicare
151 Food Stamps
151 Medicare
If an ID has both Medicaid and Food Stamps, then it should be combined as Food Stamps/Medicaid, otherwise, it will only show what it has. So the results should look like this:
ID Assistance
5 Medicare
5 Food Stamps/Medicaid
24 Medicaid
49 Food Stamps
119 Medicare
151 Food Stamps
151 Medicare


Other notes, rules for output:

1) If ID has Medicare by itself, then it will say Medicare
2) If ID has Medicaid by itself, then it will say Medicaid
3) If ID has Food Stamps by itself, then it will say Food Stamps
4) If ID has Medicaid and Food Stamps both, then it will say Food Stamps/Medicaid

Output should only combined Medicaid and Food Stamps together only if ID has both. Any other scenarios, it will remain as it is.

Thank you

 

[MCuthill]

choochoowinn2009,

I would think you should create a group on ID, with your Assistance Types on the Detail Level.

Create 3x Formulas, one for each assistance:
{@Medicare}

IF {Table.Assistance}="Medicare" THEN 1 ELSE 0

{@Medicaid}

IF {Table.Assistance}="Medicaid" THEN 1 ELSE 0

{@FoodStamps}

IF {Table.Assistance}="Food Stamps" THEN 1 ELSE 0

You then SUM each of the above and do your analysis on the combinations of SUMS:
{@AssistanceGrp}

IF Sum({@Medicare},{Table.ID})>1 AND Sum({@Medicaid},{Table.ID})=0 AND Sum({@FoodStamps},{Table.ID})=0 THEN "Medicare" ELSE
IF Sum({@Medicare},{Table.ID})=0 AND Sum({@Medicaid},{Table.ID})>1 AND Sum({@FoodStamps},{Table.ID})=0 THEN "Medicaid" ELSE
IF Sum({@Medicare},{Table.ID})=0 AND Sum({@Medicaid},{Table.ID})=0 AND Sum({@FoodStamps},{Table.ID})>1 THEN "Food Stamps" ELSE
IF Sum({@Medicare},{Table.ID})=0 AND Sum({@Medicaid},{Table.ID})>1 AND Sum({@FoodStamps},{Table.ID})>1 THEN "Food Stamps/Medicaid" ELSE "Other"

(Note: If you will only have 1 instance of each within an ID Group, the ">" symbols can be "=")

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

choochoowinn2009,

Oops. Posted early. You mentioned needing a "Row Count" as well... I believe a DistinctCount() of {Table.ID} should return the number of rows. (as you will supress the details in this report)

Cheers! Hope this helps!



Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[fisheromacse]

i don't have crystal in front of me, so apologize for any mistakes.

you could group by ID, and then set up formulas in your details section to evaluate the data and then in the group footer section another formula to apply your rules and display the data in the group footer.

//{@foodstamps} //in details
numbervar fs;
if {Table.Assistance} = "Food Stamps" then fs := 1 else fs := 0;

//{@medicaid} //in details
numbervar mcd;
if {Table.Assistance} = "Medicaid" then mcd := 1 else mcd := 0;

//{@eval} //in group footer
IF fs = 1 and mcd = 1 then "Food Stamps/Medicaid" else
IF fs = 1 and mcd = 0 then "Food Stamps" else
IF fs = 0 and mcd = 1 then "Medicaid" else "";

//{@reset} //in group header
numbervar fs := 0;
numbervar mcd := 0;

 

[MCuthill]

Correction. You'd think it was Monday.

In the final formula:
Should be ">=1" instead of ">1"
OR
Should be ">0"

IF Sum({@Medicare},{Table.ID})>=1 AND Sum({@Medicaid},{Table.ID})=0 AND Sum({@FoodStamps},{Table.ID})=0 THEN "Medicare" ELSE
IF Sum({@Medicare},{Table.ID})=0 AND Sum({@Medicaid},{Table.ID})>=1 AND Sum({@FoodStamps},{Table.ID})=0 THEN "Medicaid" ELSEIF Sum({@Medicare},{Table.ID})=0 AND Sum({@Medicaid},{Table.ID})=0 AND Sum({@FoodStamps},{Table.ID})>=1 THEN "Food Stamps" ELSE
IF Sum({@Medicare},{Table.ID})=0 AND Sum({@Medicaid},{Table.ID})>=1 AND Sum({@FoodStamps},{Table.ID})>=1 THEN "Food Stamps/Medicaid" ELSE "Other"

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[choochoowinn2009]

One more added thing please......

To make this a bit more confusing, I also need to specify whether it’ s for a child or for an adult. I already have the formula to say that if age is < 18, and it’s Medicaid, then it will return “Medicaid – Child”, and if age > 18, and it’s Medicaid, then it will return “Medicaid – Adult”
The same will go for Medicare and for Food Stamps as well.
So how would you write the codes to combine the two together? For example: for an ID under 18 and it has Medicaid and Food Stamps both?

Thank you so much for everyone's help!!!

 

[MCuthill]

choochoowinn2009,

I'd suggest making a new formula to bridge the Child / Adult tag with the Type of Assistance instead of making one larger formula This is only my suggestion, it can be done in one formula. I use multiple formulas because of the ease of expansion (if there were a third criteria separate from Age or Assistance).

You have some formula for Age, but I think it should be revised to look at only the "Age" portion. Something like:
{@AgeCategory}

IF {Table.Age}<18 THEN "Child" ELSE "Adult"

From prior postings, you have a formula for the Assistance Group (in my post it is identified {@AssistanceGrp}. The new Formula needed to combine the two criteria into one text string is simply:
{@IDTitle_Grouping_Final}

{@AssistanceGrp} & " - " & {@AgeCategory}

This will return possibilities of:
Medicare - Child
Medicaid - Child
Food Stamps - Child
Food Stamps/Medicaid - Child
Medicare - Adult
Medicaid - Adult
Food Stamps - Adult
Food Stamps/Medicaid - Adult

Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

choochoowinn2009,

Quick Question. In your example, ID "5" has all three Assistance Types and you show wanting to split it into a group for "Medicare" and a second line for "Medicaid/Food Stamps". I don't believe either my solution or fisheromacse are coded to handle this.

For this scenario:
ID Assistance
5 Medicaid
5 Medicare
5 Food Stamps

The group for ID 5 would evaluate where all three formulas in my solution would be >0 (namely 1) and therefore the {@AssistanceGrp} Formula will return "Other".

Please verify your expected results. If you do want to potentially see two lines where an ID has all three Assistance Types, another solution will need to be developed.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

I thought the idea was to display the ID with the assistance types. Just got back to this with a little different approach.

Create a formula {@assistance}:

{table.assistance}+
(
if {@age} < 18 then
"-Child" else
"-Adult"
)

Note that your description of the requirements referred to <18 and >18, so it wasn't clear which category exactly 18 falls into. Adjust as necessary.

Create a formula for the ID group header:
//{@reset}:
whileprintingrecords;
stringvar x := "";
stringvar y := "";

Add this formula to the detail section:
whileprintingrecords;
stringvar x;
if not({@assistance} in x) then
x := x + totext({table.ID},0,"")+ chr(9)+ {@assistance} + chr(13);

Add this formula to the ID group footer and be sure to format it to "Can grow":
whileprintingrecords;
stringvar x;
stringvar y;
if instr(x, "Food Stamps") <> 0 and
instr(x,"Medicaid") <> 0 then
y := y + replace(x,chr(13)+totext({table.ID},0,"") +
chr(9)+ "Medicaid","/Medicaid")+chr(13) else
y := y + x ;
y

This seems to provide the correct display at least when I test it.

-LB

 

[choochoowinn2009]

Hi,

Adult = Age >= 18
Child = Child < 18

Yes, ID5 has all 3 types of assistance, and ID5 is an ADULT, then the output should read:

ID Assistance
5 Medicare - Adult
5 Medicaid/Food Stamps - Child


ID24 is a child, and its output should read:

ID24 Medicaid - Child

Does your codes show that all lines for output to be shown for ID5? I don't think it does either. It only shows ID5 with Medicaid/Food Stamps only, but it doesn't show Medicaid.

How would I write the codes then? How would the row count be differ?

Again, thank you so much for all your help!

--C

 

[MCuthill]

choochoowinn2009,

I have an idea on how to resolve this, but do not have Crystal in front of me to test. There may be better solutions, but I think the following "should" work.

The idea is to create a grouping level underneath ID that will bucket the Assistance Types, ID can be repeated on this level and the Assistance Groups displayed there.

Report structure:
Group 1: ID
- Group 2: {@NewFormula}
- - Details
- GF 2
GF 1

{@NewFormula}

 IF {Table.Assistance} = "Medicare" THEN 1 ELSE 2

Then change all my original formulas to SUM on this new group instead of the ID group level and try a test. If my theory is at all sound, you should receive the following output for ID 5:

Group Header #1: {ID} = 5
- Group Header #2: {@NewFormula} = 1
- - Details: Medicare
- Group Footer #2
- Group Header #2: {@NewFormula} = 2
- - Details: Foot Stamps
- - Details: Medicaid
- Group Footer #2
Group Header #1

Group Header #2 should display as:
({ID} {Final Title})
5 Medicare - Adult
5 Food Stamps/Medicaid - Adult


Hope this helps, please advise if you need any clarification ... or if the idea fails miserably. *smiles*

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

I tested my formulas and it did work correctly and showed all types of assistance, so I wonder how you implemented it.

I don't really know what you are looking for re: row count. You haven't shown an example of an end result that shows what you mean by this.

-LB

 

[choochoowinn2009]

Everyone - Thank you very much for all your input!!!

I created a new assistance and age group, and then created a bunch of IF's statements for all my possibilities. This way seems to work.

I also tried LB's way, but it didn't seem to catch all my possibilities when an ID has all three. Let me work on that a little bit more.

Very much appreciated with all your ideas!!!
Thank you! Thank you!!!!