Multiple Records on Multiple Columns 1

[kskid]

I am using CR 8.5 on Oracle 8i database

I did a search but came up empty.

I have a case record linked to the Charges via case_id.

cASE1 ORDINANCEA
CASE1 ORDINANCEB
CASE1 ORDINANCEC
CASE2 ORDINANCEA
CASE2 ORDINANCED
...

I Want it to look like this


CASE# CHARGE#1 CHARGE#2 CHARGE#3.... CHARGE#6

CASE1 ORDINANCEA ORDINANCEB ORDINANCEC
CASE2 ORDINANCEA ORDINANCED
CASEn ORDINANCEG ORDINANCEH ORDINANCEI... ORDINANCEZ ET AL

Also, I will only want the first 6 charges. If there are more than 6 charges, then add "et al" to the 6th ordinance.

I cannot do it on a crosstab because I have to export it into a CSV file.

Any suggestions would be very helpful.

 

[lbass]

Am I assuming correctly that "OrdinanceA", "OrdinanceB", etc. are all results from the same field {table.charge}?

Then you could do the following. First group on {table.caseno} and suppress the group header and group footer.

Then create a running total {#cntwingrp} using the running total editor: Select {table.caseno}, count, evaluate for each record, reset on change of group (case number).

Next create a formula {@1st6charges}:

if {#cntwingrp} < 6 then {table.charge} else
if {#cntwingrp} = 6 then {table.charge} + " et al"

Place {table.caseno} in the details section on the very left and then right click on it->format field->common->suppress->x+2 and enter: {#cntwingrp} <> 1

Place {@1st6charges} next to {table.caseno} and then go to the section expert (format->section). Highlight the details section and check "Suppress blank section" and also check "Format with multiple columns". Then go to the layout tab and enter a width for the details (I used 1.0) and for the gap (I used 0.2). Check "Across then Down".

The critical thing for this to work is to get the widths of the details and gap so that the six fields will fit across. So you might have to play around with this, but it worked when I tested it.

-LB

 

[kskid]

That looked too easy. However, I neglected to mention that I have 49 other data objects to include with the output so I don't think the multiple column will work.

Here's what I did. And please feel free to critique it.

First, since this is going to be a CSV file, I used the

http://support.businessobjects.com/library/kbase/articles/c2014451.asp

to create the necessary title row.

//@FakePh - Fake Page Header
WhileReadingRecords;
1

//@FalseGp - False Group
WhileReadingRecords;
2

I then Group them in the same order


Report Header Section (Suppressed)
Page Header Section (Supperessed)
Group Header 1a Section - 1st 25 text objects for the 1st 25 data objects inserted here. Also @Charge_Init formula inserted here but suppressed.

//@ChargeInit - Initialize shared variables to null
shared stringvar chg1 := "";
shared stringvar chg2 := "";
shared stringvar chg3 := "";
shared stringvar chg4 := "";
shared stringvar chg5 := "";
shared stringvar chg6 := "";


Group Header 1b Section - 2nd 24 text object for the next 24 data objects inserted here
Group Header 1c Section - 6 text objects for the 6 charges inserted here
Group Header 2 Section (Suppressed)
Group Header 3 Section (Suppressed) - Group by Case_Number
Detail Sections 1a - 1c (Hidden) - 49 Data objects along with running total for charges and Formula for charge detail inserted here

//#RChgNo - Distinct Count Charges
Summary Section - Distinct Count on {CASE_CHARGES.CHA_CODE}
Evaluate Section - Every Record
Reset Section - Reset on Group 3, Case_number

//@ChargeDetail
shared stringvar chg1;
shared stringvar chg2;
shared stringvar chg3;
shared stringvar chg4;
shared stringvar chg5;
shared stringvar chg6;

if {#RChgNo} = 1 then
chg1 := {CASE_CHARGES.CHA_CODE}
else if {#RChgNo} = 2 then
chg2 := {CASE_CHARGES.CHA_CODE}
else if {#RChgNo} = 3 then
chg3 := {CASE_CHARGES.CHA_CODE}
else if {#RChgNo} = 4 then
chg4 := {CASE_CHARGES.CHA_CODE}
else if {#RChgNo} = 5 then
chg5 := {CASE_CHARGES.CHA_CODE}
else if {#RChgNo} = 6 then
chg6 := {CASE_CHARGES.CHA_CODE}
else if {#RChgNo} = 7 then
chg6 := chg6 + "et al"
else
""

Group Footer 3a - First 25 data objects inserted here
Group Footer 3b - Second 24 data objects inserted here
Group Footer 3c - Formulas to display Chrg1 through Chrg6 inserted here

//@Chrg1 - Formula to display 1st charge
shared stringvar Chg1;
chg1

//@Chrg2 - Formula to display 2nd charge
shared stringvar Chg2;
chg1


...

//@Chrg6 - Formula to display 6th charge
shared stringvar Chg6;
chg6

Group Footer 2 (Suppressed)
Group Footer 1 (Suppressed)
Report Footer (Suppressed)
Page Footer (Suppressed)


That's it. The resultant output had the title and corresponding data objects in proper CSV-delimited format.

Thnaks again for your help.