[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Splitting into row based on route

Posted on 2009-12-30
8
Medium Priority
?
339 Views
Last Modified: 2012-05-08
I have a table called ROUTING which has 3 fields:

prefix
country
route

My data in ROUTING Table as follows:

Prefix  Country  Route
001      USA     2,3,1
0091    India     4,2
0034    Spain    10
00346  Spain-GSM  2,1

Route above tells priority means first entry with top priority.

I want my above data to display in CR 2008 as follows:

Prefix  Country  Route  Priority  Type
001      USA       2         0           0
001      USA       3         1           0
001      USA       1         2           4
....
...

As you can see that my route 2,3,1 above splitted into rows and two new commupted fields (formulas) in CR required, there is Priority (0 assigning to first choice, then 1 to 2nd choice and so on). Similarly Type Formula field remain 0 for all choices except for last choice where should appdear as 4.

Let me show 2nd example:

In database I have:

Prefix  Country  Route
0091    India     4,2

In CR it should appear:

Prefix  Country  Route  Priority  Type
0091      India    4         0           0
0091      India    2         0           4

Hope I am able to explain this well.

Thanks in advance for your help and happy new year.
0
Comment
Question by:NickHoward
  • 4
  • 3
8 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 1600 total points
ID: 26146964
This will be quite difficult to do in a way which produces good looking output.

Essentially you have use a formula field to build up a multi-line string based on the single record.


Here is some sample code for such a formula field

stringvar prefix;
stringvar country;
stringvar route;
stringvar Type;
stringvar output;
// for testing: real report would assign fields
prefix:="0001";
country:="USA" ;
route:= "3,1,2";
type:="4";

numbervar x;
numbervar ub;
stringvar array Routes;
routes:=split(route,",");
ub:= ubound(routes);
for x:=1 to ub do
 output:= output & Prefix & "  " & country & "  " & routes[x] & "  " & type & chr(13);
output

0
 
LVL 77

Expert Comment

by:peter57r
ID: 26146981
You must format the formula field to CanGrow to see all the rows produced by the procedure.
0
 

Author Comment

by:NickHoward
ID: 26147601
Thanks Peter57r for your quick solution.

I am however missing Proirity field.

CR Output should:

Prefix  Country  Route  Priority  Type
0091      India    4         0           0
0091      India    2         1           4

In database I had:

0091 India 4,2

Can you add this? Priority 0 is is for the 1st choice, Prioirty 2 is for 2nd choice and so on (+1).




0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 77

Expert Comment

by:peter57r
ID: 26147720
for x:=1 to ub do
 output:= output & Prefix & "  " & country & "  " & routes[x] & "  " & totext(ub-1,0,"") & "  " & type & chr(13);
output

0
 

Author Comment

by:NickHoward
ID: 26147807
Thanks for very prompt reply. As you said it is complicated and it is happening.

By adopting above change, I get output as:

0001  USA  3  2  4
0001  USA  1  2  4
0001  USA  2  2  4

While it should be:

0001  USA  3  0  0
0001  USA  1  1  0
0001  USA  2  2  4

You can see above that 4th colum is assigning priorities start with 0 and then adding +1 . I my example, since route is 3,1,2 then priority for 3= 0, 1 = 1 and 2 = 2. Similarly the 5th column, it will remain 0 but for last row it will be 4.

I think 2 more formulas to be created, one for Priority (0, 1, 2, 3, ......) and other for Type (0 or 4) and combined them in main output.

Any chance to incorporate above these?

Sorry for the pain.
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 400 total points
ID: 26149580
Try it this way

for x:=1 to ub-1 do
 output:= output & Prefix & "  " & country & "  " & routes[x] & "  " & totext(ub-1,0,"") & "  0"  & chr(13);

output:= output & Prefix & "  " & country & "  " & routes[ub] & "  " & totext(ub,0,"") & "  4"  & chr(13);

output

mlmcc
0
 
LVL 77

Expert Comment

by:peter57r
ID: 26152270
sorry, wrong variable..
for x:=1 to ub do
 output:= output & Prefix & "  " & country & "  " & routes[x] & "  " & totext(x-1,0,"") & "  " & type & chr(13);
output
0
 

Author Comment

by:NickHoward
ID: 26154386
Thanks Peter and Mlmcc.

It is solved by using:

for x:=1 to ub-1 do
output:= output & Prefix & "  " & country & "  " & routes[x] & "  " & totext(x-1,0,"") & "  0"  & chr(13);
output:= output & Prefix & "  " & country & "  " & routes[ub] & "  " & totext(x-1,0,"") & "  4"  & chr(13);
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question