select statement in crystal 8 1

[adhhealth]

I have to customise a standard report in a manufacturing software called misys. The standard report gives me info in the following format:
product1 bin1
product1 bin2
product1 bin3 etc..
that means i'm getting the information of all the bins where product1 is located. My requirement is:
product1 bin1

i.e the bin located top most meaning "select
top1(mibinq.binum)".I am using pervasive sql..crystal does not allow me to change the select statement in the report.
query :
SELECT
MIMOMD."CWOHID", MIMOMD."REQQTY", MIMOMD."CMNT", MIMOMD."SRCLOC",
MIBINQ."BINUM",
MIITEM."DESCR", MIITEM."UOFM"
FROM
"MIMOH" MIMOH INNER JOIN "MIMOMD" MIMOMD ON MIMOH."WOHID" = MIMOMD."WOHID" INNER JOIN "MIBINQ" MIBINQ ON MIMOMD."ITEM" = MIBINQ."ITEM" INNER JOIN "MIITEM" MIITEM ON MIBINQ."ITEM" = MIITEM."ID"
WHERE
MIMOH."WOHID" = ''
ORDER BY
MIMOH."WOHID" ASC
Can you please suggest a way out??
Thanks,
Rajani.

 

[dgillz]

Click on report, edit selection formula, record.

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[adhhealth]

IN the record selection formula when i typed top1{@dsp_bin}
it says remaining text not part of the formula??

 

[adhhealth]

where dsp_bin is a formula field:
MIMaskToDisplayStr ({?BINFMT}, {MIBINQ.BINUM}, 24)

 

[dgillz]

MIMaskToDisplayStr() is not a standard crystal formula, so it must be a UFL. The error message you are getting indicates that the .dll that supplies crystal with the UFL is either not present or not registered on the machine you are trying to use to execute the report.

Are you trying to execute this report on a machine other than the one on which you wrote the report?

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[adhhealth]

No I am doing on the same m/c. The standard report we got from the misys software uses PDBBTRV.DLL ..the report as it is + minor changes runs fine ..I tried to convert driver to psodbc.dll and also tried..it still says remaining text doesnot appear to be a part of formula..
Thanks alot for the prompt replies!!If this problem is solved then I will be able to customise many of our reports ..
Regards,
Rajani..

 

[GJParker]

You will need to group by Product, then insert a Group Selection Formula like

{MIBINQ.BINUM} = Min({MIBINQ.BINUM},{MIITEM.DESCR})

HTH



Gary Parker
MIS Data Analyst
Manchester, England

 

[dgillz]

OK I am confused. Please copy and paste your record selection formula (not your SQL) into this thread, verbatim.





Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[adhhealth]

when i click on
report- edit selection formula- record
i see
{MIMOH.WOHID} = {?MOID}

 

[dgillz]

I am getting more confused by the minute. How does this relate to your earlier post:

In the record selection formula when i typed top1{@dsp_bin} it says remaining text not part of the formula??

Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[adhhealth]

Originally in the standard report i am trying to customise {MIMOH.WOHID} = {?MOID} -this is the record selection formula i see.. (this is un-related to @dsp_bin)
coming to customisation of my report:
i need to display only top row of the bin detail-by product.Hence, when you suggested to modify the record selection "Click on report, edit selection formula, record"
i tried to modify/append to the existing formula "{MIMOH.WOHID} = {?MOID}"
to "top1{@dsp_bin}"
and dsp_bin is a formula field.(i need the top row of this field by product)
MIMaskToDisplayStr ({?BINFMT}, {MIBINQ.BINUM}, 24)
Thank u!!

 

[GJParker]

did you read my earlier post and try this ?

Gary Parker
MIS Data Analyst
Manchester, England

 

[adhhealth]

Min" will give the first one in the alphabetical order right?? like bin1..
but i may have any bin in the top row ex: "bin10" may be stored in the top row..so i need to fetch whatever bin is in the top row!!

 

[GJParker]

ok all you need to do is place your fields in the product Group header and suppress the details section this will display the first record for each group.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[adhhealth]

Gary Parker,
Thanks a million it worked!!
But now each record is printing in a different page..i want all the records in one page!!can you suggest if something can be done??
Thanks again Gary!!
regards,
Rajani

 

[GJParker]

Check in the Section Expert and make sure you haven't got New Page After or New Page Before checked for any of your sections.



Gary Parker
MIS Data Analyst
Manchester, England

 

[adhhealth]

Thanks Gary problem solved!!!

 

[adhhealth]

GJParker,
I have a new problem. In the above case if the value in bin10 is zero I need to display the next record. Can we do this?