Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Having trouble combining formula Post Reply Post New Topic
Author Message
JvdWal
Newbie
Newbie
Avatar

Joined: 13 May 2010
Location: South Africa
Online Status: Offline
Posts: 3
Quote JvdWal Replybullet Topic: Having trouble combining formula
     Posted: 13 May 2010 at 1:28am

I have a quick question on combining crystal formula if possible.  I am trying to combine the sorting and truncation formula but I am not having any luck. The individual formulas are:

 

Numbering:

if isnumeric({activity.numberPrefix}) then

left({activity.qualifiedName}, instr({activity.qualifiedName}," ") - 1) else "1"

 

This works in sorting the numbering, but still contains the full qualified name. 
e.g.

4.1.4 Issue contracts [Omega 3]                
4.1.4.1 Capture policies sold data (under Business Process: Issue contracts [Omega 3])      
4.1.4.1.1 Record policies sold (under Activity: Capture policies sold data, Organization: BD / PFA)      
4.1.4.1.2 Submit policy applications for input (under Activity: Capture policies sold data, Organization: BD / PFA)  
4.1.4.1.3 Input policy applications automatically (under Activity: Capture policies sold data, Role: OMSTA Client Services data capturer)
4.1.4.1.4 Input policy applications manually (under Activity: Capture policies sold data, Role: Retail Affluent New Business data capturer)

Truncation:

StringVar activityL1fullname := {activity.qualifiedName};

NumberVar NamePosition;

NamePosition := InStr (activityL1fullname," (under ");

Left (activityL1fullname,NamePosition);

 

This works in truncating the name, but throws out the numbering. e.g

4.1.4 Issue contracts [Omega 3]    
4.1.4.1 Capture policies sold data     
4.1.4.1.1 Record policies sold     
4.1.4.1.4 Input policy applications manually   
4.1.4.1.2 Submit policy applications for input   
4.1.4.1.3 Input policy applications automatically 
 

 

Do you have any recommendations?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 May 2010 at 4:15am
Not exactly sure what your issue is here.
You can use the sorting field to sort your rows without displaying the result from the formula and use the truncation formula for displaying on each row.
Did I miss something else that you are trying to do?
IP IP Logged
JvdWal
Newbie
Newbie
Avatar

Joined: 13 May 2010
Location: South Africa
Online Status: Offline
Posts: 3
Quote JvdWal Replybullet Posted: 13 May 2010 at 4:59am

Thank you for the response.  I apologies for being so vague. I have tried to apply a sort against the Group in question, but I am not that familiar with the Order Formulation.

This report contains 10 Grouping levels - with a Hierarchical construct.  - Each of which, with their own sort and numbering formula. The reason for this being that the reported field used in the groupings is against what I call “qualified names” (this is a concatenation of number and name). This in itself does not allow for the standard sort function but rather a formula.

You see the numbering must represent the “Parent to child” model construct.  Please refer to the below example:

 

 

 

4.1.4 Issue contracts [Omega 3]                                - Group 1 (Parent)

4.1.4.1 Capture policies sold data                                 - Group 2 (child)

 

 

4.1.4.1.1 Record policies sold                                          - Group 3 (sub-child)

 

 

4.1.4.1.4 Input policy applications manually                     - Group 3

 

4.1.4.1.2 Submit policy applications for input                   - Group 3

 

4.1.4.1.3 Input policy applications automatically              - Group 3

 

I hope this give you an idea about the matter, if not, thank you for your time.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 May 2010 at 6:02am
Trying to understand your row level data...
You have a string that has the above items in the same column.
You want to group on all rows that start with 4.1.4 in group level 1
Then you want to create a group level 2 to further group all rows into anything that starts with 4.1.4.1
then you want to group each row into the 4.1.1.1.x level?
Is that correct?


Edited by DBlank - 13 May 2010 at 6:02am
IP IP Logged
JvdWal
Newbie
Newbie
Avatar

Joined: 13 May 2010
Location: South Africa
Online Status: Offline
Posts: 3
Quote JvdWal Replybullet Posted: 13 May 2010 at 8:06pm

Correct!

The query constructs flows as follows:

 

* Link = Left Outer join

 

Table

Table

Table

 BusinessProcess (ob)       

Id (field)

 

Activity (ob)    

 

Parent (field)

Id (field)

Activity_1 (ob)

 

 

Parent (field)

 

Grouping construct:

Group 1: BusinessProcess

            Group 2: Sort Activity

                        Group 3: Activity

                                    Group 4: Sort Activity_1

                                                Group 5: Activity_1

 

 

Formula Fields:

Activity L1 Number:

if isnumeric({activity.numberPrefix}) then left({activity.qualifiedName}, instr({activity.qualifiedName}," ") - 1) else "1";

 

//StringVar Level3Activity := {activity.qualifiedName};

//NumberVar NamePosition;

 

//NamePosition := InStr (Level3Activity," (under ");

//Left (Level3Activity,NamePosition);

 

 

Activity L2 Number:

if isnumeric({activity_1.numberPrefix}) then left({activity_1.qualifiedName}, instr({activity_1.qualifiedName}," ") - 1) else "1"

 

Sort Activity L1:

local numbervar digit;

local numbervar v;

local stringvar s;

local numbervar i;

v :=0;

for i := 1 to len({@Activity L1 Number}) do (

   s:= mid({@Activity L1 Number},i,1);

  

if isnumeric(s) then

   digit := val(s);

    v:= v*10 + digit;

);

 V

 

 

Sort Activity L2:

local numbervar digit;

local numbervar v;

local stringvar s;

local numbervar i;

v :=0;

for i := 1 to len({@Activity L2 Number}) do (

   s:= mid({@Activity L2 Number},i,1);

  

if isnumeric(s) then

   digit := val(s);

    v:= v*10 + digit;

);

 

V         

 

This is all the queries, grouping and formula information I've used. perhaps this will help.

thanks again.
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.