asked on

CR XI- Trying to count using stringvar in group footer?

Experts:

I need help please.

My data looks like like this:

FILENUMBER ACTCODE
1234 TR
1234 RR
1234 RC

1235 TR
1235 RR
1235 AC
1235 BC

I need to be able to identify and count/categorize files that contain a TR, RR and RC. IF they contain these 3 codes, then I want to count them.

I'm using the following formulas to build a string for calculating at the group FILENUMBER level.

///In the HEADER
whileprintingrecords;
stringvar NoLitFiles:= "";

///In the Details
Whileprintingrecords;
stringvar NoLitFiles;
NoLitFiles:= NoLitFiles&{RLS.Activity Code}&",";

///In the GroupFooter
whileprintingrecords;
stringvar NoLitFiles;

The result looks like this:

TR,RR,RC
TR,RR,AC,BC

I would like to assign TR,RR,RC as 1 and TR,RR,AC,BC as 0 so that I can get a total.

How can I accomplish this and also, please let me know if you can see a better/easier way to do this. I'm needed assign other CODE COMBINATIONS as well to identify 2 other categories and also count them as well.

THANKS.
M

Mike McCracken

How many different combinations do you need to identify?

It is fairly easy to count them using Running totals or variables but if the number of combinations is large then the number of totals will also get large.

mlmcc

Marcus Aurelius

ASKER

I believe at this point three:

NO LIT NEEDED
LIT STARTED
LIT COMPLETED

Thanks

Marcus Aurelius

ASKER

The codings involved are NOT too many.

ie.

TR,RR,RC = NO LIT NEEDED
TR,CR but no RC= LIT STARTED
TR, CR, RC = LIT COMPLETED

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marcus Aurelius

ASKER

Seems to be working, but I do have an additional issue.

Many times numerous identical codes are put in record many times:

ie. TR, RR, TR, RR, TR,RR, RR, RC,

These items are showing a 0 because of the string searching for a literal 'TR,RR,RC'

Is there a way to look through the string to see IF it contains a TR, RR and an RC? Not necessarily in that order, but rather if the file has any combination of those codes?

Thank you I appreciate your expertise.....

ASKER CERTIFIED SOLUTION

dylanyee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Marcus Aurelius

ASKER

Its working, but minor issues.

How can I format the total count in the final count from 0.00 to 0 or from 1.00 to 1, I don't want the decimals to show.

Also, another formatting issue. I'm interested too in creating a cross tab that can/will display the summarized totals, perhaps by client id which is another field we have not dealt with in this discussion. I don't see how I can use a variable in a crosstab. Is that possiblle.

Trying to achieve this in cross tab:

CLIENTID CATEGORY JAN06 FEB06 MAR06
1000 NO LIT NEEDED 3 5 2
1000 LIT STARTED 10 25 40
1000 LIT COMPLETED 25 10 5
2000 NOT LIT NEEDED 5 2 1
2000 LIT STARTED 5 10 15
2000 LIT COMPLETED 10 7 1

Something to this effect.

Does this require a whole rewrite of the report formatting of can I build on the information that you have provided thus far?

I guess the big question is Can a "variable" be used in a crosstab?

Thanks this is "awesome" information!
Mike

dylanyee

Use totext(CountNoLit,0), it will convert the number to string without any decimal point.

For the crosstab question... actually it is possible to create charting/crosstab according to shared variable via subreport:
http://support.businessobjects.com/communitycs/technicalpapers/cr9_charting_print_time_formulas.pdf

But since your calculation is really complex, I doubt that it will work in your case. What I have in my mind is to first use sql stored procedure to populate your result then only apply them in your crosstab.

dylan

Marcus Aurelius

ASKER

Can you elaborate on the stored procedure a little. In my company, we have a MS SQL Server 2000 "Tech Dept" that creates SQL Stored Procedures. I have MS Query Analyzer on my workstation and am "familiar" with it. I don't yet have rights to SAVE any stored procedures on the Server. Are you referring to using a COMMAND LINE at the database lever to handle the data calc?

Is this where I go to assign a database and I choose ADD COMMAND? Is this the same as the Stored Procedure that I'm already using in this report? This is the stored procedure that I'm currently using that our Tech Dept created that identifies TYPE of files. I've added this as an SQL Expression:

dbo.ISDIRECTLIT({FILENUMBER})

This is what I understand a stored procedure to be.

Sorry to ramble. I guess my big question is, is a stored procedure the same as a COMMAND LINE "command" to Crystal?

Thanks
M

dylanyee

We could consider stored procedure and crystal report's "add command" as the same thing, but you have to be clear that
Stored Procedure is a pre-compiled sql command/statement that is stored in SQL Database where "Add Command" is a crystal report's object that stored sql command/statements.

"Add Command" can proceed any sql select statement, run stored procedure as well as process complicated SQL script like "if then else". We could make "Add Command" functioning like a stored procedure as well. Try create a new report and put the below simple SQL script in your "Add Command". It will take parameter a {?FILENUMBER} and return a result accordingly.
--------------------------
declare @abc datetime
declare @ind int
set @ind = {?FILENUMBER}
if @ind = 1
begin
set @abc = '2005/1/1'
end
else
begin
set @abc = '2006/1/1'
end

select @abc as MyDate
-----------------------------

Ok, back to your question. For you to easily create the crosstab report, you need the following SQL Result:

CLIENTID CATEGORY Month Counter
1000 NO LIT NEEDED Jan 06      3
1000 NO LIT NEEDED Feb 06      5
1000 NO LIT NEEDED Mar 06      2
1000 LIT STARTED Jan 06      10
1000 LIT STARTED Feb 06      25
1000 LIT STARTED Mar 06      40
1000 LIT COMPLETED Jan 06      25
1000 LIT COMPLETED Feb 06      10
1000 LIT COMPLETED Mar 06      5
2000 NO LIT NEEDED Jan 06      5
2000 NO LIT NEEDED Feb 06      2
2000 NO LIT NEEDED Mar 06      1
2000 LIT STARTED Jan 06      5
2000 LIT STARTED Feb 06      10
2000 LIT STARTED Mar 06      15
2000 LIT COMPLETED Jan 06      10
2000 LIT COMPLETED Feb 06      7
2000 LIT COMPLETED Mar 06      1

You could write your own complicated sql script to return the above result set. But if I were you, I will leave the job to my "Tech Dept". Ask them to write a stored procedure which will return the result above and I will just make use of the stored procedure it in my report.

dylan

Marcus Aurelius

ASKER

Ok so here is where I'm at.

I have another question, in a little different direction, although the formulas provide are great and usefull.

My data detail looks like this:

FILENUMBER ACTCODE STATUSCODE
1234 TR
1234 RR
1234 CR
1234 HS
1234 RC
1235 TR
1235 RR
1235 PS
1235 PF
1235 RC

Is there a way that I can accomplish this?

FILENUMBER ACTCODE STATUSCODE STRINGVAR CODING
1234 TR TR,
1234 RR TR,RR,
1234 CR TR,RR,CR,
1234 HS TR,RR,CR,HS
1234 RC TR,RR,CR,HS,RC,
GROUPFOOTER TR,RR,CR,HS,RC,
1235 TR TR,
1235 RR TR,RR,
1235 PS TR,RR,PS,
1235 PF TR,RR,PS,PF,
1235 RC TR,RR,PS,PF,RC
GROUPFOOTER TR,RR,PS,PF,RC

THEN can I have a formula or variable...whatever.... that will SEARCH THROUGH the GROUP FOOTER string...and assign a LITIGATION TYPE (LITTYPE) to the file?

Like this.....

FILENUMBER ACTCODE STATUSCODE STRINGVAR CODING
1234 TR TR,
1234 RR TR,RR,
1234 CR TR,RR,CR,
1234 HS TR,RR,CR,HS
1234 RC TR,RR,CR,HS,RC,
GROUPFOOTER TR,RR,CR,HS,RC, LIT COMPLETED
1235 TR TR,
1235 RR TR,RR,
1235 PS TR,RR,PS,
1235 PF TR,RR,PS,PF,
1235 RC TR,RR,PS,PF,RC
GROUPFOOTER TR,RR,PS,PF,RC LIT COMPLETED
1235 TR TR,
1235 RR TR,RR,
1235 PF TR,RR,PF,
GROUPFOOTER TR,RR,PF, LIT STARTED

The "LIT COMPLETED" and the "LIT STARTED" are the text TYPE CODES that I would like to ASSIGN to each file. I feel like I can get the summarized totals by using the above technique.

But what I need is a formula that would.....SEARCH THROUGH THE STRING in the GROUP FOOTER.... (ie. TR,RR,CR,HS,RC,) and assign a code to the File at the group footer level. The formula needs to say.....

IF (LITCOMPILEDSTRING) in 'TR' or 'RR' or 'RC' then "NO LIT NEEDED"
ELSE
IF (LITCOMPILEDSTRING) in 'TR' or 'RR' and not(LITCOMPILEDSTRING) in 'RC' then "LIT STARTED"

I can assign the codings, but I need the formula to be able to search through the entire string to see IF a CODE is present in the string ONE OR MORE TIMES and if so... in the combination that I assign,....so that the formula can assign a TEXT NAME.

If I can assign a TYPE CODE to the FILE itself.... in the GROUP FOOTER...of the FILENUMBER,. then I think I should be able to summarize some counts by the TYPE CODE.....

Can I do it this way?

///Formula Name - {@AssignGroupVars}
whileprintingrecords;
global booleanvar bTR;
global booleanvar bRR;
global booleanvar bRC;
global booleanvar bCR;
global booleanvar bPS;
global booleanvar bPF;
if {RLS.Activity Code} = "TR" then
global booleanvar bTR := true
else if {RLS.Activity Code} = "RR" then
global booleanvar bRR := true
else if {RLS.Activity Code} = "CR" then
global booleanvar bCR := true
else if {RLS.Activity Code} = "PS" then
global booleanvar bPS := true
else if {RLS.Status Code} = "PF" then
global booleanvar bPF := true
else if {RLS.Activity Code} = "RC" then
global booleanvar bRC := true

Marcus Aurelius

ASKER

Sorry Experts... the formula at the bottom of my last post wasn't meant to be there... pasted by accident. Please ignore the @AssignGroupVars...for the moment.

Mike

Mike McCracken

Should be able to do it.
Addd formulas
In the report header
Name - DeclVars
Global StringVar LitStatus := "";

In the group header
Name - InitString
Global StringVar LitStatus;
LitStatus := "";

In the detail section
Name - BuildString
Global StringVar LitStatus;
LitStatus := LitStatus & "," & {RLS.Activity Code};

In the group footer
Name - FindStatus
Global StringVar LitStatus;

if (instr(LitStatus,"RC") > 0) then
"No Lit Needed"
else if (instr(LitStatus), " ") then
etc

Question, Is the last value a clue to the status?
If you need help with the last formula
What strings ae possible? What do they mean?

mlmcc

Marcus Aurelius

ASKER

mlmcc,

Here is what I'm tring to use right now. The {@3-No Lit Total} is the stringvar that creates the code string in the files group footer.
ie. TR,RR,RC,CR,PS,PS

I'm testing this formula for assigning the TEXT TYPE. It appears to be working BUT...I cannot place it in a crosstab for use. I guess this needs to be somekind of variable?

Anyway here is my latest attempt to categorize our LITIGATION FILE TYPES based on the "ridiculous" codings that goes on in our system.

One problem with this fomula is I get "null" type assigned and I can't figure out why yet. Maybe there is a character (space) in the code data element or something.

IF ISNULL({@3-No Lit Total})=TRUE THEN "MISC"
ELSE
if({@3-No Lit Total}) LIKE ["*TR*","*RR*","*RC*"] THEN
IF(({@3-No Lit Total}) LIKE ["*PF*","*CR*"]) THEN
"NO LIT NEEDED"
ELSE
if({@3-No Lit Total}) LIKE ["*TR*","*RR*","*PF*","*CR*"] THEN
IF(({@3-No Lit Total}) LIKE ["*RC*"]) THEN
"LIT NOT COMPLETED"
ELSE
if({@3-No Lit Total}) LIKE ["*TR*","*RR*","*PF*","*CR*","*RC*"] THEN
"LIT COMPLETED"
ELSE
"MISC"

THANKS

Marcus Aurelius

ASKER

also, for the above formula, how can I tell this section to say...."if TR,RR,PF and CR" AND "if RC is NOT present"....then LIT NOT COMPLETED????? I don't know how to do a "NOT" in the formula

if({@3-No Lit Total}) LIKE ["*TR*","*RR*","*PF*","*CR*"] THEN
IF(({@3-No Lit Total}) LIKE ["*RC*"]) THEN
"LIT NOT COMPLETED"

Mike McCracken

if({@3-No Lit Total}) LIKE ["*TR*","*RR*","*PF*","*CR*"] THEN
IF(NOT(({@3-No Lit Total}) LIKE ["*RC*"])) THEN
"LIT NOT COMPLETED"

or

if({@3-No Lit Total}) LIKE ["*TR*","*RR*","*PF*","*CR*"] THEN
IF(({@3-No Lit Total}) NOT LIKE ["*RC*"]) THEN
"LIT NOT COMPLETED"

mlmcc

Marcus Aurelius

ASKER

Thank you dylanyee and mlmcc I am using a combination of your two techniques.

I appreciate the expert advice on this one.

Mike

Mike McCracken

Glad i could help

mlmcc

dylanyee

glad I could help~

dylan