grouping of formula field in crystal report

[Former Member]

i have a formula field called rawmaterial in which i have written the following coding.

WhilePrintingRecords;

Shared StringVar MaterialNo;

StringVar ItemNoMain;

if {V_ARINVT_AVAIL_PROJECTED_DTL.SOURCE}="PO" THEN

ItemNoMain:= {ARINVT.ITEMNO}

ELSE

ItemNoMain:=MaterialNo

------------------------------------

the report displaying ItemNoMain correctly, but my problem is i want to group the itemNoMain data

[Former Member]

i could not write SQL statement that returns all the data because of the database link. i am struggling with this report for more than 1 week.

i try to link all the table in main report itself but i have linking problem, it is returning only the source='SO'

mat-table-link.png

the main table is v_arinvt_avail_proj. this view having all the records details. in this table, i am filtering only source='PO' and source='BACKLOG ' (i.e SO)

it is listing correctly.

i have attached my table data

in that if the source is PO then i need only to link those 2 table highlighted.

if the source is BackLog(ie SO) then i need to link all the table showed in that image.

I have tried left outer join, but the problem is if it "PO", arinvt_id in the view v_partno_arinvt would be empty, so it is not linking, it doesnot show the PO record since it has no link. please see the attachment below

table-data.png

table-data1.png

i have highlighted the blank one.

(if the source is 'PO' then ARINVT_ID returns RawMaterial

if the source is 'BACKLOG' then ARINVT_ID return ItemNo of Product, so we have to find out the Raw material used for that product. then i have to group all the raw materials from PO and SO. )

both data like rawmaterial and product all stored in the table called ARINVT.

Can you please help me to resolve this problem?

thank you.

[abhilash_kumar]

Hi Selvi,

Whenever you start a formula with 'whileprintingrecords', the formula becomes a 'print time' formula - meaning it executes when the record is being printed.

To be able to insert a Group, you need a formula that is not 'print time'. Also, you can't use a shared variable (a value from the subreport) to insert a group in the Main Report.

You should create a SQL query that returns all the data you need (including the material number and the Item Number) is a single select. You should then be able to insert a group based on a formula like so:

if {V_ARINVT_AVAIL_PROJECTED_DTL.SOURCE}="PO" THEN
{ARINVT.ITEMNO}
ELSE {MaterialNo}

-Abhilash

[Former Member]

it is not the time, i want to group by item no,raw.png

actually itemno from one table(v_arinvt_avail), in that table i have field called source. so , if source='po' i want to display the itemno as it, but if source='so' then i call subreport link with id. My sub report having 6 table link with that id, return the itemno(raw material).

it returned value stored in a formula rawmaterial

now i want to group by rawmaterial.

--------------------------------------------

formula rawmaterial coding as follows:

WhilePrintingRecords;

Shared StringVar MaterialNo;

StringVar ItemNoMain;

if {V_ARINVT_AVAIL_PROJECTED_DTL.SOURCE}="PO" THEN

ItemNoMain:= {ARINVT.ITEMNO}

ELSE

ItemNoMain:=MaterialNo(this is returned from subreport)

--------------------------------------------

i am new to crystal report, please tell me is there any other way to link the fields and group by itemno.

thanks.

[abhilash_kumar]

Hi Selvi,

You cannot group on a 'print time' formula.

-Abhilash