Create a list

[wanzek1]

I would like to take data that looks like:

1100.1 (phase code)
154691 (this is a list of units charged to that phase code for the month)
154691
154691
15853
15853
15853
15853
1720.14
17030
17030
17030
17040
17040
171003
171003

I would like to change the format to:

1100.1..............154691, 15853
1720.14..............17030, 17040, 171003

It is currently grouped:
Company
Project Manager Name
Job Name
Phase Code
Units

I have tried every suppression criteria I can think of and formula combination I can find on through help sites and I always end up with the following:

1100.1............154691, 154691, 154691, 15853, 15853, 15853, 15853
1720.14............17030, 17030, 17030, 17040, 17040, 171003, 171003

 

[SkipVought]

Hi,

FIRST generate a list of unique values. THEN do your summarization.

 

[wanzek1]

Not sure what you mean by generating a list of unique values?

 

[SkipVought]

You list was generated from some data source. It needs to be generated as a DISTINCT or UNIQUE list.

 

[wanzek1]

I don't know how I would do that because I need to be able to run the report for a certain time frame. For example if I run it for August I want to know what units have hit that particular phase code in a list view. Then the following pages have all the data for each entry.

Since my report is grouped by phase code and then unit I can get the view to look like:

1100.1 (phase code)
154691
15853

1720.14
17030
17040
171003
This isn't how I want it to appear though.

 

[hilfy]

Or you can use "InStr()" to determine whether the number already exists in your list. It would look something like this:

if InStr(string_variable, {MyTable.unit}) <= 0 then
string_variable := string_variable + ", " + {MyTable.unit}

Depending on the order of the data, this may not work if you have unit numbers that are varying widths and one unit number can match part of another unit number. For example, if you have unit numbers 154691 and 4691 - if the record with unit 4691 is processed after unit 154691, it will be found in the string and won't be added.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[SkipVought]

This isn't how I want it to appear though"

Well is there any reason why you couldn't do both?

 

[wanzek1]

Hilfy - That is a good idea but your example would definitely happen in this case.

SkipVought - I don't understand exactly what you want me to do. Could you be a little more descriptive?

 

[wanzek1]

I have previously tried the following and got close but still have the repeating issue:

Formula 1 (located in detail section but suppressed):
whileprintingrecords;

global stringvar sRemarks:=sRemarks & if next({EMRD.JCPhase})={EMRD.JCPhase}

or previous({EMRD.JCPhase})={EMRD.JCPhase}

then {EMRD.Equipment} & ','

else '';
sRemarks;

Formula 2 (located in group header 4):
Whileprintingrecords;

global stringvar sRemarks:=''

Formual 3 (located in detail section):
whileprintingrecords;

global stringvar sRemarks;

Formula 4 (located in suppress section of detail section) :
{EMRD.JCPhase}=next({EMRD.JCPhase})

Groups are:
EMRD.EMCo
JCMP.Name
EMRD.Job
EMRD.JCPhase

There can sometimes be a piece of equipment that will show up multiple times under a JCPhase. How can I only show that piece of equipment once?

1100.1 ---------10104, 171407, 173025, 173025, 173028

 

[hilfy]

There is a way around this then....

1. Change the formula that concatenates the data to this:

if InStr(string_variable, "|" + {MyTable.unit} + "|") <= 0 then
string_variable := string_variable + "|" + {MyTable.unit} + "|"​

2. Change the formula that displays the string to first replace "||" with ", " and then replace the remaining two "|" (on the ends of the string) with "":

Replace(Replace(string_variable, "||", ", "), "|", "")

By putting the pipes ("|") on both ends of the units and using them in the comparison in InStr(), you ensure that you're looking for the whole unit number and InStr() will not find unit numbers that might be inside other unit numbers.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[wanzek1]

I tried the following:

whileprintingrecords;

global stringvar sRemarks:=sRemarks & if next({EMRD.JCPhase})={EMRD.JCPhase}

or previous({EMRD.JCPhase})={EMRD.JCPhase}

then if InStr(sRemarks, "|" + {EMRD.Equipment} + "|") <= 0 then
sRemarks := sRemarks + "|" + {EMRD.Equipment} + "|"

else '';
sRemarks;


I get an error that says:
A string can be at most 65534 characters long.

Am I putting the formula in the wrong section?

 

[hilfy]

It looks like things are getting added to the string twice. I would change the formula to this:

whileprintingrecords;
global stringvar sRemarks;

if next({EMRD.JCPhase})={EMRD.JCPhase}
or previous({EMRD.JCPhase})={EMRD.JCPhase} then
if InStr(sRemarks, "|" + {EMRD.Equipment} + "|") <= 0 then
sRemarks := sRemarks + "|" + {EMRD.Equipment} + "|";
sRemarks;

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[wanzek1]

THANK YOU SO MUCH!!!!!!!!

You seriously made my week! I have been working on this part of the report forever and have been stuck!

Thank you! Thank you!!