How to split a row into multiple rows if a numeric field is greater than certain value.

[garban]

Hi,
I am working with CR XI R2 and SQL Server 2005 stored procedure. I have a requirement wherein, I need to split a row into multiple rows if values of a field is greater than e.g. 20 million. So 66,000,000 should split into 4 rows of 20, 20, 20 and 6 millions.

/*** For Example ***/

SQL Stored Proc Output:

Account # Account Type Bought/ Sold Symbol/ CUSIP Quantity Price/Basis Type Price/Basis Amt Trade Date Setllement Date
QJZ001043 0 - COD Bought 912810QY7 3000000.00 Dollar Price 91.429515419 01/30/2013 01/31/2013
QJZ001043 0 - COD Sold 912828TY6 66000000.00 Dollar Price 96.580216872 01/30/2013 01/31/2013
QJZ001043 0 - COD Bought 912828UF5 1000000.00 Dollar Price 98.306690141 01/30/2013 01/31/2013

In Crystal Reports, it should look like:

Account # Account Type Bought/ Sold Symbol/ CUSIP Quantity Price/Basis Type Price/Basis Amt Trade Date Setllement Date
QJZ001043 0 - COD Bought 912810QY7 3000000.00 Dollar Price 91.429515419 01/30/2013 01/31/2013
QJZ001043 0 - COD Sold 912828TY6 20000000.00 Dollar Price 96.580216872 01/30/2013 01/31/2013
QJZ001043 0 - COD Sold 912828TY6 20000000.00 Dollar Price 96.580216872 01/30/2013 01/31/2013
QJZ001043 0 - COD Sold 912828TY6 20000000.00 Dollar Price 96.580216872 01/30/2013 01/31/2013
QJZ001043 0 - COD Sold 912828TY6 6000000.00 Dollar Price 96.580216872 01/30/2013 01/31/2013
QJZ001043 0 - COD Bought 912828UF5 1000000.00 Dollar Price 98.306690141 01/30/2013 01/31/2013

Any suggestions and/or solutions much appreciated.

Thanks,

 

[IanWaterman]

This solution requires you to know the max number for CUSIP Quantity Price as you need to split the Detail section into that number of sections.

Create this formula and place in top detail section Details a

@CUSIPR1
whileprintingrecords;

Global numbervar CUSIP;
Global numbervar CUSIPREM;
Global numbervar rowcnt;

rowcnt:= int({CUSIP Quantity Price}/20000000);

If rowcnt >=1 then cusip:= 20000000 else cusip:= {CUSIP Quantity Price};
CUSIPREM:= cusip- 20000000

cusip;

In section expert for Details b, enter this in the suppression formula box

whileprintingrecords;

Global numbervar rowcnt < 1

In section expert for Details c, enter this in the suppression formula box

whileprintingrecords;

Global numbervar rowcnt < 2

Repeat for other details sections as required

Place this formula in all the remaining details sections

@CUSIPR2
whileprintingrecords;

Global numbervar CUSIP;
Global numbervar CUSIPREM;

If CUSIPREM >20000000 then CUSIPREM:= CUSPREM-20000000;

If CUSIPREM >20000000 then CUSIP:=20000000 else CUSIP:= CUSIPREM;

CUSIP;

Can not test but this should work

Ian

 

[HowardHammerman]

Ian's solution is right on. Just to be clear, you need to create X number of detail sections where X is the maximum number that can ever be. That is, the maxium number of row that can result from dividing the price by 20 million. Then use Ian's formula and supress all those that your don't need.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com