Listing grouped data from left to right 1

[sorchard2000]

Crystal 8.5
Connecting through SQL Server

I am trying to make a simple listing of all the procedures from our database that we do in our hospital. Every procedure has a code between 1-4 associated with it.

I currently have my data like:
Level 1 Level 2 Level 3 Level4
---------------------------------------------------
(@Level1) (@Level2) (@Level2) (@Level4)
Appendectomy
bump removal broken hip
broken hip etc...

But this produces a listing with lots of blanks in it.
The only grouping I have is for "department_id"
I currently DO have 4 formulas (one formula for each code level) that reads:
if {psmpro.chargelevel_id} = 1.00 then {psmpro.proname_name}

I need to make a list, much like an Excel spreadsheet does where it reads:

Level 1 Level 2 Level 3 Level4
---------------------------------------------------
appendectomy bump removal broken hip open heart
etc...

Is there a simple way to do this or should I just make a small subreport for each level?

Thanks in advance!

sorchard2000

 

[lbass]

First try inserting a crosstab where {psmpro.chargelevel_id} is the column field, {table.deptID} is the row field, and {psmpro.proname_name} is the summary field. If you know the maximum number of procedures that can occur per department per level, then you can insert a summary of NthLargest multiple times, incrementing N by 1 each time. Or you could use department as your first row group, {psmpro.proname_name} as your second row group, and then use maximum of {psmpro.proname_name} as your summary.

Place the crosstab in the report header or footer.

-LB

 

[sorchard2000]

lbass:
Sorry, but I can't get a crosstab to work. I've tried a variety of different layouts for the crosstab and the problem seems to be that the summary field is looking for a number not a string. Since psmpro.proname_name is a string, it's just not working.

Am I doing something wrong?

sorchard2000

 

[lbass]

You have to provide a little more information about why something does not work. Crosstabs absolutely can use strings for summaries. In fact, that was what I was suggesting. Please explain exactly what happens when you follow my earlier suggestion. I think my second idea probably best meets your needs.

-LB

 

[sorchard2000]

OK-Sorry, I'm still learning all this!

I laid out the fields like:

Row:
dept_id
psmproname_name

Column:
psmchargelevel_id

Summary field:
max of psmproname_name

When I did your suggestion, the crosstab looked like this:

1 (chargelevel) 2 3 4

deptID | knee surg open heart

appendectomy gb surgery
valve surg

Lots of white space between fields.

Hope that explanation clears things up!

sorchard2000

 

[lbass]

You could try the crosstab without the name in the row field and then use the NthSmallest repeatedly, incrementing N by 1 up to the maximum number of instances of names, but this will cause each row to have the same number of lines, regardless of names within each department.

You certainly could do a subreport for each level, which might be the simplest method after all. If you want to avoid subreports, you could also use the following method, which entails formatting in multiple columns, but which uses the group sections instead of detail sections. I just discovered that this allows the alignment of groups across the page more easily than simply formatting the detail section with "format groups as multiple columns" checked.

First group on {table.dept_ID} and then on {table.psmchargelevel_id}. Drag the dept name from group #1 header to the group #2_a footer. Also drag the Group #2 header into the Group #2_a footer next to the department name. Suppress the repeating department name by creating two formulas:

//{@resetgrpcnt} to be placed in the group #1 header:
whileprintingrecords;
numbervar grpcnt := 0;

//{@grpcnt} to be placed in the group #2_a footer and suppressed:
whileprintingrecords;
numbervar grpcnt := grpcnt + 1;

Then right click on the department name->format->common->suppress->x+2 and enter:
{@grpcnt} <> 1

Place {table.psmproname_name} in the details section. Create three formulas:

//{@resetname} to be placed in the Group (level) header #2:
whileprintingrecords;
stringvar x := "";

//{@names} to be placed in the details section:
whileprintingrecords;
stringvar x;

if instr(x,{table.psmproname_name}) = 0 then
x := x + {table.psmproname_name} + chr(13);

//{@display} to be placed in the group #2_b section (to be inserted):
whileprintingrecords;
stringvar x;

Right click on {@display}->format field->common->check "Can Grow".

Next go to the section expert and suppress the group header sections and the details section. Also highlight the details section and check "format with multiple columns"->layout tab->width (I used 1.5)->gap (I used .5)->printing direction->check "across then down"-> check "Format groups with multiple columns."

This should give you a display like this:

Ortho Level 1 Level 2 Level 3 Level 4
knee surg shoulder ankle leg
elbow surg toes
fingers

Cardiac Level 1 Level 2 Level 3 Level 4
aorta ventricle stent open heart
veins valve
other

A little complicated to do, but the results are aligned nicely.

-LB

 

[sorchard2000]

LB:
Thanks SO MUCH for helping me with this. If I could bake some cookies for you, I would!

After I did all your instructions, I hit the run button and got the following error related to the @names formula:
"A string can be at most 254 characters long."
Not sure why it's saying that since I know these names are at most 50-60 characters long.

Also: I just wanted to make sure I read your instructions correctly:
for the @names formula--it's all of these lines?

//{@names} to be placed in the details section:
whileprintingrecords;
stringvar x;

if instr(x,{table.psmproname_name}) = 0 then
x := x + {table.psmproname_name} + chr(13);


Thanks again
sorchard2000

 

[lbass]

Sorry, I didn't notice that you were using 8.5. Yes, that is all one formula, and it is collecting the procedure names within each department. The string length includes all the procedure names within the department. What would be the maximum number of procedures per department per level--approximately? If there are more than 10 or so, this probably doesn't make sense to do it this way.

-LB

 

[sorchard2000]

There are probably 500+ procedures total for this department.

Sorry you went through all that work for nothing!

sorchard2000

 

[lbass]

Subreports are the way to go then.

-LB

 

[jenfromjax]

lbass,

I am using Crystal Pro v10 and followed your instructions above. It worked great, but I think I might(?) need to do one more thing?

In my scenario, substitute {table.dept_ID} with {table.sale_id} and {table.psmchargelevel_id} with {table.product_sold}. The goal is to show a list of all products sold in a deal, reading from left to right, like so:

12345
Network Services, eBanking, Consulting, Professional Services

Once I completed everything you listed above, I did get my products reading from left to right, however the deals that only have one or two products in them are showing up on the same line, like so:

23456 Consulting 12436 Network Services 12652 eBanking

I've tried adjusting the width, gap & printing direction, but that doesn't really change what is happening. Any suggestions?

Thanks much,
Jen

 

[lbass]

Not sure you really need such an elaborate method. If you have a group on {table.sale_id}, you could create three formulas like:

//{@reset} to be placed in the group header:
whileprintingrecords;
stringvar prods := "";

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar prods;

if instr(prods,{table.product_sold}) = 0 then
prods := prods + {table.product_sold} + ", ";

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar prods;
left(prods,len(prods)-2);

This would give you a comma-delimited list of distinct products sold all on one line.

-LB

 

[jenfromjax]

lbass,

I can't thank you enough for this (but did give you a star), it worked beautifully.
I'm just trying to understand why it works though & have a question about the "len" part of last formula. I've read what Crystal's Help file has to say about it, but I'm still not 100% clear. I've highlighted the part in question in blue:

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar prods;
left(prods,len(prods)-2);

Does this subtract the ", " used to separate the products from the last record?

 

[lbass]

Yes, exactly.

-LB