Skip to Content
avatar image
Former Member

grouping of formula field in crystal report

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Jan 12 at 05:38 PM

    Hi Selvi,

    You cannot group on a 'print time' formula.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 12 at 07:59 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 12 at 08:05 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 15 at 02:47 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded