formula help with arrays 5

[unknownly]

Hi All,

I have report a with two parameter values to be enter by the user.
1) type:
2) accounts:

accounts parameter can be left empty by the user(no values enter) which would take for values which falls for that Type parameters.

Report works fine. But Iam facing trouble witht the Report header. In the above case the report header show the 1st value only as a header. But what if I want to show all the account values which fall under the same Typeparameter)

say for eg:
if Type : FinACC
then Accounts are 100,300,350,500,900
and say the user didn't enter the value for Accounts(parameter) then my report show details for all these accounts(100,300,350,500,900)for the Type= FinAcc(parameter)
But in the report header it shows as

100 report

how can i show it as

100,300,350,500,900 report

I tried using the join function but it gives error.

Any help is greatly appericated..

Thanks,

Sweetie

 

[synapsevampire]

When you receive an error, please post it.

You're probably dealing with a number, and since join works on strings, you'll need to convert them to strings for display:

whileprintingrecords;
stringvar array TheNumbers;
numbervar counter;
redim TheNumbers[ubound({?multi number})];
for counter := 1 to ubound({?multi number}) do(
TheNumbers[counter] := totext({?multi number}[counter],0,""
);
join(TheNumbers,", &quot

-k

 

[Ngolem]

accounts parameter can be left empty by the user(no values enter)

I don't understand this....Crystal will not accept a "null" value for a parameter

Jim Broadbent

 

[unknownly]

Crystal report is use a Peoplesoft Query where in the PS Query the values are passed thru a run control id something..
Iam new to this too...just learning

Thank you guys very much

Sweetie

 

[unknownly]

SV,

Iam sorry that I didn't print the error I thought I did.

It's a string and the error Iam getting is when using join

A string array is required here!

I have tried your formula too and it gives error

Array required!

and cursor blinks at the {?multi number} in

redim TheNumbers[ubound({?multi number})];


Thank you,

Sweetie

 

[unknownly]

What is a "ubound" and what does it do?
Iam using CR 8 version

Thank you,
Sweetie

 

[Ngolem]

Ubound is the upper value for the array element

for example

StringVar array test(5);

the value of Ubound(test) is 5
*********************************
say for eg:
if Type : FinACC
then Accounts are 100,300,350,500,900
and say the user didn't enter the value for Accounts(parameter) then my report show details for all these accounts(100,300,350,500,900)for the Type= FinAcc(parameter)
But in the report header it shows as

100 report

how can i show it as

100,300,350,500,900 report
********************************

the reason it is only showing "100 report" in the report header is that being in the report header it can only show the value of the first record...it doesn't know the reset of the values yet til the report is processed.

show me the formula that you use to get "100 report" displayed.

Jim Broadbent

 

[unknownly]

It jsut a field + report

@ titleheader

{tab1.accounts} + "report"

Iam sorry for giving an ex for numbers. Actually, the data is string.


Acct_100,Acct_300,acct_350,acct_500,acct_900 Report

instead of

100,300,350,500,900 report

Thank you,
Sweetie

 

[Ngolem]

it is as I suspected...

@ titleheader

{tab1.accounts} + "report"

There is no looping through this formula so you will get the first value of {tab1.accounts} ....actually if you placed the formula elsewhere All you will get is the LAST value of {tab1.accounts} .

Now one way to get all of the Account numbers into the report header is to use a subreport to do it

This subreport would have the same criteria as the main report (you would pass the parameter values from the main report to the subpreport. But all the report would do is create/display the string of Account numbers that you will process in the main report

IN THE SUBREPORT You would suppress all the sections except the report footer

You would group by {tab1.accounts}

In the Report Header for the subreport you would have the formula

//@initialize
whilePrintingRecords;
StringVar result := "";

In the Group 1 ({tab1.accounts}) section you would have

//@CalcTitle
whilePrintingRecords;
StringVar result ;

result := result + {tab1.accounts} + ", ";

In the Subreport report footer (which is the only section not suppressed) place the formula

//@displayAcctNos
whilePrintingRecords;
StringVar result ;

left(result,length(result)-2) + " report";

That should do it for you

Jim Broadbent

 

[unknownly]

I tried the same thing to see if iam able to get these as i wanted though not in the right place as I wanted. but I have done this in
Group header section
details section
group footer section

when I check the formulas it says no error found but
when I preview the report
I says

A String can be atmost 254 character long.

Is there any other work around if not the subreport..because Iam not sure how it works thru peoplesoft. Iam new to it.

Thank you,

Sweetie

 

[Ngolem]

you have a string limit of 254 characters/formula...you must have amny many account numbers if you exceed this

You can modify the formulas as follows :

//@initialize
whilePrintingRecords;
StringVar result1 := "";
StringVar result2 := "";
StringVar result3 := "";

In the Group 1 ({tab1.accounts}) section you would have

//@CalcTitle
whilePrintingRecords;
StringVar result1 ;
StringVar result2 ;
StringVar result3 ;

if length(result1) < 240 then
result1 := result1 + {tab1.accounts} + &quot;, &quot;
else if length(result2) < 240 then
result2 := result2 + {tab1.accounts} + &quot;, &quot;
else if length(result3) < 240 then
result3 := result3 + {tab1.accounts} + &quot;, &quot;
;

In the Subreport report footer (which is the only section not suppressed) place the formula

//@displayresult1
whilePrintingRecords;
StringVar result1 ;
StringVar result2 ;

if result2 = &quot;&quot; then
left(result1,length(result1)-2) + &quot; report&quot;
else
result1;

//@displayresult2
whilePrintingRecords;
StringVar result2 ;
StringVar result3 ;

if result3 = &quot;&quot; and result2 <> &quot;&quot; then
left(result2,length(result2)-2) + &quot; report&quot;
else
result2;

//@displayresult3
whilePrintingRecords;
StringVar result3 ;

if result3 <> &quot;&quot; then
left(result3,length(result3)-2) + &quot; report&quot;
else
result3;

Place each of these display formulas in a separate subsection of the report footer of the Subreport and make each formula field as wide as the report and enable the &quot;can grow&quot; for each one.

In the Section expert for the subreport enable &quot;suppress blank section&quot; for each report footer subsection...this should work for you

Jim Broadbent

 

[unknownly]

Jim Broadbent,

Just to let you know.

Thank you. It works but my lead wouldn't approve it because
the users have to enter the user id and run control id twice
once for the main report and once for the sub report.

I wish there was another work around if not he subreport.

We finally decided on generic report header

Since the report will be scheduled thru. peoplesoft.

I really appericate for your time and I have learned something new today. But i still can't figure it out why it did not work when i did the same thing in group footer.
any way I thank you again.

Sweetie

 

[Ngolem]

you could always put the &quot;header&quot; into the Report footer....it would cycle through and collect the Account numbers in the main report

//@initialize (place in the Report Header suppressed)
whilePrintingRecords;
StringVar result1 := &quot;&quot;;
StringVar result2 := &quot;&quot;;
StringVar result3 := &quot;&quot;;

In the Group for {tab1.accounts} section you would have

//@CalcTitle (suppress)
whilePrintingRecords;
StringVar result1 ;
StringVar result2 ;
StringVar result3 ;

if length(result1) < 240 then
result1 := result1 + {tab1.accounts} + &quot;, &quot;
else if length(result2) < 240 then
result2 := result2 + {tab1.accounts} + &quot;, &quot;
else if length(result3) < 240 then
result3 := result3 + {tab1.accounts} + &quot;, &quot;
;

In the report footer place the formulas following in separate sections as you did earlier in the subreport.

//@displayresult1
whilePrintingRecords;
StringVar result1 ;
StringVar result2 ;

if result2 = &quot;&quot; then
left(result1,length(result1)-2) + &quot; report&quot;
else
result1;

//@displayresult2
whilePrintingRecords;
StringVar result2 ;
StringVar result3 ;

if result3 = &quot;&quot; and result2 <> &quot;&quot; then
left(result2,length(result2)-2) + &quot; report&quot;
else
result2;

//@displayresult3
whilePrintingRecords;
StringVar result3 ;

if result3 <> &quot;&quot; then
left(result3,length(result3)-2) + &quot; report&quot;
else
result3;


Jim Broadbent

 

[unknownly]

Thanks a lot!!!

 

[FVTrainer]

Here is another approach, that I think works (it looks good in testing; I was very intrigued by your problem). It doesn't use a subreport. Instead it depends on evaluation time. I'm sorry that the sample doesn't use your fields. It is written against the Customer table in the Xtreme Sample database. The 2nd formula may be difficult to follow. If you have questions feel free to ask.

Also, the sample only shows using 3 arrays. If your list of accounts was greater than 254 * 3, then you'd need additional array(s).

Create 5 formulas:

Formula: VariableDeclarations
--------------------
BeforeReadingRecords;
global NumberVar x;
global NumberVar y;
global NumberVar z;
global NumberVar w;
Global StringVar Array arCustomerID;
Global StringVar Array arCustomerID2;
Global StringVar Array arCustomerID3;
global NumberVar StringLength;

Formula: CustomerList
---------------------WhileReadingRecords;
Global NumberVar x;
Global NumberVar y;
Global NumberVar z;
Global NumberVar w;
Global StringVar Array arMaster;
Global StringVar Array arCustomerID;
Global StringVar Array arCustomerID2;
Global StringVar Array arCustomerID3;
Local StringVar strCustomerID := Cstr({Customer.Customer ID},&quot;0&quot;,0);
Global NumberVar StringLength;

If Not (strCustomerID in arMaster) then
(x := x + 1;
Redim Preserve arMaster[x];
arMaster[x] := CStr({Customer.Customer ID},&quot;0&quot;,0);
StringLength := StringLength + Length(strCustomerID) + 1; //add one to allow for comma separation
SELECT StringLength
CASE 0 to 254:
(w := w + 1;
Redim Preserve arCustomerID[w];
arCustomerID[w] := CStr({Customer.Customer ID},&quot;0&quot;,0)
CASE 255 to 508:
(y := y + 1;
Redim Preserve arCustomerID2[y];
arCustomerID2[y] := CStr({Customer.Customer ID},&quot;0&quot;,0)

CASE 509 to 782:
(z := z + 1;
Redim Preserve arCustomerID3[z];
arCustomerID3[z] := CStr({Customer.Customer ID},&quot;0&quot;,0)

DEFAULT:

//do nothing
&quot;0&quot;;
)

Formula: ShowArray1
--------------------
whilePrintingRecords;
Global StringVar Array arCustomerID;
Join(arCustomerID, &quot;,&quot

Formula: ShowArray2
--------------------
whilePrintingRecords;
Global StringVar Array arCustomerID2;
Join(arCustomerID2, &quot;,&quot

Formula: ShowArray3
--------------------
whilePrintingRecords;
Global StringVar Array arCustomerID3;
Join(arCustomerID3, &quot;,&quot

Place the first formula in the report header. Put the 2nd Formula in the details section. Create a text box in the report header and place the remaing formulas in the textbox.

 

[unknownly]

FVTrainer,


Thank you. It just works prefect! and exactly what I wanted.

You all guys are really awesome in here.


Sweetie

 

[unknownly]

Hi all,

FVTrainer-This array formula you gave. it works fine but I need to change it little bit and need your help.

How can I reset the array when a field flag changes.
From the above formula
Local StringVar strCustomerID := Cstr({Customer.Customer ID},&quot;0&quot;,0);


This is what I have in my formula:
Local StringVar strCustomerID := {selectvalue},&quot;0&quot;,0);

all the user input values are stored in one field but there are different by the object Flag(have about 6 flags now)

How can I do this to reset the arrays list when the object flag changes

Please help!


Thanks,

Sweetie