Global variable WhileReadingRecords

[makan44]

Hello,

This is what I am trying to do.
I want a global variable to hold the count of records (i.e. count of records where SegmentGroup = 2) SegmentGroup is just an integer field in my stored procedure. (i don't want to use a subselet or anything like that in my stored procedure, i want crystal formula to give me the count)

So what i want is this count to be totalled up and populated so that i can conditionally supress a section or sections based off the value of the count (i.e. count > 5 or whatever)

In theory i want the count populated before the report is printing so i thought while reading records would do that and I could put formula anywhere on the report and see the same total in any section of the report.


I could get it to give me total in report header if I Insert Summary MAX of formulaD, but what i really want is a global variable populated with a count of records that have SegmentGroup = 2 and be able to refer to that Global variable anywhere in the report and have it be the same value no matter what section of report i am using or refering to it in.

I think i am missing something conceptually, I am newer to Crystal Reports - I am vb programmer. Any help would be appreciated!

My formulas are ver similar to this excerpt from help...

Rem Formula C
Global x as Number
x = 10
formula = x

Rem Formula D
'call the function WhileReadingRecords
WhileReadingRecords
Global x as Number
x = x + 1
formula = x

 

[synapsevampire]

You might find that posting technical information ratehr than descriptions will help:

Crystal version
Database/connectivity used
Example data
Expected output

Crystal works akin to a SQL statement, in that a group header would be the same as a group, except just as it begins displayign the details for that group's data, and the group footer is after that group's data.

BTW, you don't need to state Global, that's the default.

You'll also find that most use Crystal syntax (very basic like) rather than Crystal Basic as it's been around much longer and is better documented.

Anyway, rather than stating " that Global variable anywhere in the report and have it be the same value no matter what section of report i am using or refering to it in.", why not state where you need it?

Since you're a beginner, I would also gently suggest that you seek architectural direction rather than decribing which function you will use and where, you may find that someone here has a better approach to the requirements.

-k

 

[makan44]

Well the reason I explained it the way I did was because it is really just a generalized question. I have completed a client solution and it works great except for one small anoying thing, i'm not looking to change the design at this point and i can live with it as is.
I'm using SQL Server 2000, Crystal 8.5 & OLE DB connection. The point is I have a recordset being returned from my stored procedure (let's say 20 record are comming back, one of the fields is called SegmentGroup, 11 of the 20 records have a value of 2 in the SegmentGroup field.

With me so far?

so the 1st formual i wrote is...

'init_gVar_x
Global x as Number
x = 0
formula = x

This formula would be considred a constant formula right & be evaluated BeforeReadingRecords - PrePass #1

So at this point I have an initialized global variable that = 0 right - Yaaay

I experimented with putting this formula in report header, details section, a group header,report footer, it shows zero everywhere - GREAT thats what i expected.

So now all i want to do is have another formula increment x 1 time for every record that has SegmentGroup = 2 thus leaving me global variable x = 11, then i want to use that variable in the report to conditionally hide a section. Or at least try, i know there may be other methods of doing this better, but i wanted to try to do what i was thinking about.

So i wrote formula 2 hoping it would be executed as the records were being read (in my head I was thinking that x would = 11 before any printing of the report happened and i would essentially have a variable holding a value of 11 that i could use throughout the report - either to display for debugging or to refer to in a formatsection formula somewhere else in the report.


'call the function WhileReadingRecords
WhileReadingRecords

'eval_gVarX
Global x as Number
IF {PR_CRYSTAL_DRUGORDER_FORM_SANOFFC557201;1.SegmaenGroup} = 2 THEN
x = x + 1
END IF

FORMULA = x



You following me? Do you understand what I was trying to do? It may not be the best design or whatever but I wanted to try it.

Thats all i wanted to know. If i tried to explain my whole report design and the exact issues I was running into i would need to write a book. I am working with someone who has worked with Crystal 8.5 & 9.0 for 3 years so the design is ok. We just ran into one small issue and i was just thinking on the fly and don't understand why i can't do this.

Thanks for any info?

 

[rosemaryl]

Dear makan44,

There are (at a mininum) 3 ways to do what you want.

I would create a formula (crystal syntax) as follows:

If
{PR_CRYSTAL_DRUGORDER_FORM_SANOFFC557201;1.SegmaenGroup} = 2
THEN 1
else 0
//you can suppress the formula so you don't see it print

Now, and here is why knowing your version is important, in CR 8.5 you can right click the formula field in the detail section and choose Insert Summary, Click the Insert Summary for all groups checkbox and insert grand total. You now get the value as indicated.

If you want to see the value incrementing on the report ... create a running total using the running total expert.

In the Field Explorer right click Running Total, New, give it a name. There are three sections to populuate:

Field to summarize: {PR_CRYSTAL_DRUGORDER_FORM_SANOFFC557201;1.SegmaenGroup}
Summary Type: count

Evaluate:
Click radio button for Use a Formula. Click x-2 formula editor button and enter:
{PR_CRYSTAL_DRUGORDER_FORM_SANOFFC557201;1.SegmaenGroup} = 2

Reset:

Never (or whenever you want it to based on group or something).

Click ok. Put in details.

You will see it count only the SegmentGroup when it = 2 and will see the total as it is built.

The third way is using variables.

A. You are incorrect that it is static and would use WHILEREADINGRECORDS.

You will actually need three formulas to do what you want. In the crystal world this is referred to as the "3 formula Manual Running Total Method"

//Formula 1
//Initializes or Resets the variable depending upon
//placement ...

Numbervar x := 0;
x
//Place in Report Header to have it count all records
//on report. Page Header to count the ones on the page
//group header to count for group ...

//Formula2 Evaluates the records.
//Placement where you want evaluation to occur
//I am assuming the detail section from the description of
//what you are doing.

Numbervar x := IF {PR_CRYSTAL_DRUGORDER_FORM_SANOFFC557201;1.SegmaenGroup} = 2
THEN x+1
else x;
x
//end formula

//Formula3 Displays value

Numbervar x;
x

If values don't display as expected, you may have to add a WhilePrintingRecords to each formula.

Read the info in help on BeforeReadingRecords, WhileReadingRecords, WhilePrintingRecords, and evaluate after. While you are in the help file, go ahead and do a search for multi-pass and study the flow chart to better understand Crystal's processing.

Regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[makan44]

Thanks Rosemaryl,

I believe the "3 formula Manual Running Total Method" option you explained is what i was trying to figure out.

I have often used the 1st method you explained (Inset Summary) for group totals, but this was the 1st time I was attempting to use a variable in more then 1 formula. I think i was close and just knowing i needed the 3rd formula is big help. One of the issues i had was not getting a value if my report pulled back an empty recordset. But now i see that this should not be a problem with "3 formula Manual Running Total Method" because if the 2nd formula never evaluates due to no records in details, the 3rd formula will still diplay the value of x regardless & it will = 0 because of initializtion in formula 1.

This should work perfect for me.
I will switch to crystal syntax as well.

Thanks for everyones advice & help!