Replace multiple values with others in a string. 2

[Solojer]

I have the following text stored in a local stringvar in my report:

 <li class="bar1 purplebar" style="height: [COLOR=#EF2929]12px[/color];">12 </li>
<li class="bar2 redbar" style="height: [COLOR=#EF2929]7px[/color];">7 </li>
<li class="bar3 bluebar" style="height: [COLOR=#EF2929]3px[/color];">3 </li>
<li class="bar4  greenbar" style="height: [COLOR=#EF2929]2px[/color];">2 </li>
<li class="bar5 grapebar" style="height: [COLOR=#EF2929]3px[/color];">3 </li>
<li class="bar6 crimsonbar" style="height: [COLOR=#EF2929]41px[/color];">41 </li>
<li class="bar7 navybar"   style="height: [COLOR=#EF2929]31px[/color];">31 </li>

I need to be able to take the ##px part of the string and replace the ## with the result of a simple multiplication formula. For example, I would need to multiple each ## by 5 to get the result below. One additional complication is that the value of “bar#” is auto-generated, and can climb as high as bar12.

<li class="bar1 purplebar" style="height: [COLOR=#EF2929]60px[/color];">12 </li>
<li class="bar2 redbar" style="height: [COLOR=#EF2929]35px[/color];">7 </li>
<li class="bar3 bluebar" style="height: [COLOR=#EF2929]15px[/color];">3 </li>
<li class="bar4  greenbar" style="height: [COLOR=#EF2929]10px[/color];">2 </li>
<li class="bar5 grapebar" style="height: [COLOR=#EF2929]15px[/color];">3 </li>
<li class="bar6 crimsonbar" style="height: [COLOR=#EF2929]205px[/color];">41 </li>
<li class="bar7 navybar"   style="height: [COLOR=#EF2929]155px[/color];">31 </li>

Is this something that’s possible in Crystal? I looked around at the replace function, but that doesn't seem to work in this situation.

 

[MCuthill]

Solojer,

This is not something I have been able to test, or try before, but you may have luck using "Instr()" to find the "Bar#" string location and then add "x" characters to get the location of the ##px on the same row. Your replacements would either need to then be nested to take care of all instances or some script written that stores the field to a variable and passes it through a series of Replace() steps. For this to work, the "colorbar" portion would have to be specific to each "bar#". There are also other alternatives to the above, one being that if you are able to break the lines of the string into an array (then you could just look for the numbers immediately preceeding "px" on each row).

Unfortunately, I do not have a specific solution to give you, but I think it is certainly "possible" to do by some means.

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."

 

[Solojer]

HMM.. maybe I'm making this too complicated for myself. I'm building that string in a GLOBAL stringvar (I noticed I accidentally said local in my original post) in GF2. The code looks like this...

//@GF2

evaluateafter ({@CallsPerSubject});
WHILEPRINTINGRECORDS;
global stringvar bar;
global stringvar subject;
global numbervar barcounter;
local stringvar table_color;
local numbervar maxcount:=((count({Incident.Incident #},{@Subject})));
global numbervar Persubcount;
select {#RTotal0}
    case 1 : table_color:="purplebar"
    case 2 : table_color:="redbar"
    case 3 : table_color:="bluebar"
    case 4 : table_color:="greenbar"
    case 5 : table_color:="grapebar"
    case 6 : table_color:="crimsonbar"
    case 7 : table_color:="navybar"
    case 8 : table_color:="goldbar"
    case 9 : table_color:="tealbar"
    case 10 : table_color:="redbar"
    case 11 : table_color:="bluebar"
    case 11 : table_color:="greenbar"
default: table_color:="Purple";

bar:=bar+"<li class=""bar"&totext({#RTotal0},0,"")&" "&table_color&""" style=""height: " &totext((count({Incident.Incident #},{@Subject})),0,"")&"px;"">" &totext(count({Incident.Incident #},{@Subject}),0,"")&" </li> ";
subject:=subject+"<li>"&{@Subject Desciption}&"</li>"

and I'm displaying the text in @GF1, like this..

//@GF1

whileprintingrecords;
global stringvar bar;

bar

Ultimately, I need the final PX value to be the count in GF2*(200/max incident count for any GF2).

The more I think about it, an array seems the best way to go, but i'm really not sure where/how to implement this in Crystal.

 

[IanWaterman]

If it is always followed by a px and preceded by a : then split will work.

This returns a number which you can then sum

tonumber(trim(split(split('<li class="bar1 purplebar" style="height: 60px;">12 </li>', "px")[1], ":")[2]))

Replace string '<li class="bar1 purplebar" style="height: 60px;">12 </li>' with your field

Ian

 

[Solojer]

Thanks Ian; I'm still not sure I fully understand

The string '<li class="bar1 purplebar" style="height: 60px;">12 </li>' is in 1 field with many other similar strings (redbar, bluebar, etc, all combined in one string). In GF2, how would I specify for which part of the string the replacement will be taking place?

 

[IanWaterman]

Perhaps I have focused onto the wrong point. I thought you just wanted to sum the values prior to px.

I assumed this string <li class="bar1 purplebar" style="height: 60px;">12 </li> was contained in a single database field.

" "&totext(5*(tonumber(trim(split(split('<li class="bar1 purplebar" style="height: 60px;">12 </li>', "px")[1], ":")[2]))), 0)

In the above example this would return " 300"

@replace
replace({yourfield}, split(split({yourfield}, "px")[1], ":")[2],
" "&totext(5*(tonumber(trim(split(split({yourfield}, "px")[1], ":")[2]))), 0))

If <li class="bar1 purplebar" style="height: 60px;">12 </li> was in the field the above formula would return

<li class="bar1 purplebar" style="height: 300px;">12 </li>

You can then concatenate these together using a string var similar to your example something like

global stringvar bar:=bar+@replace

Ian

 

[Solojer]

Since the text for all the bar colours is stored all together in one global stringvar (bar), how would I use your @replace function to change all the values at once?

 

[MCuthill]

Solojer,

Now knowing that this is a string field created by you, why not modify the original formula to multiply these values by 5? (I assumed in my original post that you had a database field containing text like shown -- in the future, please ensure your original postings give a clear outline of what you are working with; in this case, it is a very significant difference in approach.

Since you are building this string via the formula you have provide above, either it can be modified as follows, or if a separate formula is needed, copy the one you provided into this new field regardless of "where" the x5 needs to happen, i beleive the following *should* provide you what you need:

[green] //the rest of your formula.[/green]

bar:=bar+"<li class=""bar"&totext({#RTotal0},0,"")&" "&table_color&""" style=""height: " &totext((count({Incident.Incident #},{@Subject})[red][b]*5[/b][/red]),0,"")&"px;"">" &totext(count({Incident.Incident #},{@Subject}),0,"")&" </li> ";
subject:=subject+"<li>"&{@Subject Desciption}&"</li>"

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."

 

[Solojer]

Thanks for your suggestion, Mike.

I mentioned a few posts up that ultimately, I need the final PX value to be the count in GF2*(200/MAX incident count for any GF2). So, the *5 was simply a placeholder for whatever the value of 200/MAX of any GF2.

My problem is, I'm building the original string in one group (GF2), and need to add the multiplier in another group (GF1).

 

[MCuthill]

Solojer,

Your requirement of using the "MAX incident count for any GF2" makes this a fair bit trickier... essentially, I think would would have to run the entire report as a SubReport to determine this value (does your current report have any subreports? if so, this will not work), store the value in a variable and then use the variable in the situation you have outlined above. Once we are able to *somehow* determine this "MAX incident count for any GF2", you should be able to then place the calculation in place of the "*5" I placed above.

Though you may have said this somewhere above (I cannot recall now), can you please advise if there is any SubReports used in your current report, or would a subreport be needed if all you were asked for is to provide the Incident Count Per GF2?

If this number would not require a subreport in a stand-alone request, I think we may be able to create a subreport in your current project to calculate this maximum to then use... I think. Or, if your DBA can create a query to house "Incidents per GF2", we can just use that query in a subreport and return the Maximum() of the Count in the DB query.

Hope I didn't garble up this posting too badly, my mind was a bit here and there as to how this could potentially be acheived for you and got a bit sidetracked a few times. haha.

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."

 

[pmax9999]

Hi Solojer

I tackled your challenge (as I understand it) this way:

1. Create a fomula that determines the maximum value within each of the vaiables as follows

WhilePrintingRecords;
Local NumberVar  c := UBound(Split({@table.field}, '</li>')) -1;
Local NumberVar  i := 1;
Global NumberVar m := 1;

for i := 1 to c step 1 do 
If      Val(Left(Split(Split({@table.field}, '</li>')[i] , 'height: ') [2], (Instr(Split(Split({@table.field}, '</li>')[1], 'height: ') [2], 'px')-1))) >= m 
Then    m := Val(Left(Split(Split({@table.field}, '</li>')[i] , 'height: ') [2], (Instr(Split(Split({@table.field}, '</li>')[1], 'height: ') [2], 'px')-1)))
Else    m := m;

m

NB: {@table.data} is the location of the data to be manipulated. If it is a variable, amend as appropriate. It assumes a maximum of 12 sets within the record.

2. Create a formula that manipulates the values by multiplying the px value by (200/MAX)

WhilePrintingRecords;

Global NumberVar m;

Local NumberVar c := UBound(Split({@table.field}, '</li>')) -1;
Local NumberVar i ;

Local StringVar t := '';

m := 200/m;

For i := 1 to c step 1 do
T := T + Split(Split({@table.field}, '</li>')[i], 'height: ') [1] + 'height: '
       + ToText(Val(Left(Split(Split({@table.field}, '</li>')[i] , 'height: ') [2], (Instr(Split(Split({@table.field}, '</li>')[1], 'height: ') [2], 'px')-1)))*m, '#')
       + 'px:"' + Left(Split(Split({@table.field}, '</li>')[i] , 'height: ') [2], (Instr(Split(Split({@table.field}, '</li>')[1], 'height: ') [2], 'px')-1)) 
       + '</li>';


T

Hope this helps.

Cheers
Pete,

 

[Solojer]

Thanks to both Mike and Pete I was able to get this working the way I needed!!

 

[Solojer]

And Ian too. Sorry Ian, didn't mean to slight you