how to save the value of column from the first record to a global variable and use it later?

[Marco Poblete]

I need to save a column value from the first row of a command object to a variable so I can use it at the bottom of the report. I sort of new in CR XI and I've spent a lot of time searching, maybe someone here can help me

 

[lbass]

How is the column sorted? By date? By item #? Is the column value that you want to grab the sorted value or is it some other field?

Depending upon your answered, the following might work:

Whileprintingrecords;
Numbervar x;
If {table.date}=minimum({table.date}) then
X := {table.quantity};

Then just reference in the report footer:

Whileprintingrecords;
Numbervar x;

You could alternatively set up a variable to count the records, like this:

Whileprintingrecords;
Numbervar cnt := cnt + 1;
Numbervar x;
If cnt=1 then
X := {table.quantity;

-LB

 

[Marco Poblete]

the case is that I get a resultset from a command object returning 11 rows looking like this:
customer-code,address, qty,amount.
it happens that sometimes the last rows can be returned with Nulls, but I need to print the address in the footer. since all the rows qould be for the same customer-code and address, I need to save the first or lastnon-null address to a variable and print it in the footer.
in your example where do i put those formulas? do I create a formula field?
hope you can help me further
MP

 

[lbass]

Questions:

Do you have any groups?

What if there are more than one customer on the page? What would you want to happen?

What footer section are you referring to? A group footer? A page footer? Report footer?

-LB

 

[Marco Poblete]

thanks for your response LB
-No Groups, this is like an invoice
so in the the detail section, it prints the items sold for a customer's invoice
I referring to a Page Footer (the II have to print the address , but since the last row is nulls I need to save the last value (or the firs as they wiil be for the same customer address)

MP

 

[lbass]

Put this formula in the detail section and suppress it:

Whileprintingrecords;
Stringvar x := “”; //replace these iPad quotes with your own, please!
If not isnull({command.address}} and
Trim({command.address}) <> “” then //replace these quotes
x := {command.address};

Put this formula in the page footer:

Whileprintingrecords;
Stringvar x;

-LB

 

[Marco Poblete]

when putting the formula in the Detail section I get error "the formula result must be a boolean"
MP

 

[Marco Poblete]

this is my version of what you suggested me to put in detail, what am I missing?

Whileprintingrecords;
Stringvar usps:= "";
If not isnull({Command.MailAddr}) and
Trim({command.MailAddr}) <> "" then
usps:={command.MailAddr};

 

[lbass]

You should be creating this formula in the field explorer->formula editor->new (pencil icon, I think). I think you must have entered it as a selection formula.

-LB

 

[Marco Poblete]

you said to put the formula in the Detail Section and suppress it Do you mean I have to create a formula field?
pleae advise
MP

 

[lbass]

Yes, create the formula and then place it in the detail section. Then right click on it->format field->suppress. THis is just so the address doesn’t appear again in the detail section. This formula is just going to carry the value and make it available in the page footer.

-LB

 

[Marco Poblete]

okay thanks for clarifying!. so I've create a formula field called SaveAdr with the above formula on it, added it to the detail and suppressed it . no errors so far. now how do I used the variable in the footer? I don't understand your suggestion from above :
----------------------
Put this formula in the page footer:
Whileprintingrecords;
Stringvar x;




- do you mean to create another formula field with that formula and insert it into the footer?

let me know
thanks!
MP

 

[lbass]

Yes.

-LB

 

[Marco Poblete]

Thank LB, very helpful your suggestions, but now I face a change in the scope of the report, what happens is that this will print in a pre-printed form that allows only 11 lines withe detail, but there is a 12th line in the form where I'm suppossed to print a parameter entered. i t seems simple si I put the parameter printing in the repport footer, but it turns out that the command object can return less than 11 lines for the detail so I need to create a logic where the report inserts blank lines when the lines to reach 11,any advise on how to do that? sorry to be a pain!
MP

 

[lbass]

Go into the section expert->report footer and check “print at bottom of page”.

-LB

 

[Marco Poblete]

but the parameter value has to always print in the 12th line, wouldn't bottom of the page make it print at the bottom part of the page?(too far from line 12th which is around the half of the page) or bottom of the page is like "bottom of the section?)
thanks in advance
MP

 

[lbass]

Is this report always just one page long? What is the parameter type and how is it used? Does it select a customer code for example? It might not be relevant to the solution, but please answer.

-LB

 

[lbass]

If the report is always for one customer, for example (use whatever field that has the same value for all details), then insert a group on {table.customer} and place the parameter in the group footer. YOu can suppress the group header, if you like.

Sorry for misunderstanding earlier.

-LB

 

[Marco Poblete]

great help, I'm getting closer to have it working!
thx
MP