Multiple RECORDS

[Former Member]

Please I'm runing a report in crystal report and is giving me multiple records. How can i filter the report to display just one records for a PART NUMBER FIELDS that has multiple records. I'm runing on Windows Server and Oracle 10g DATABASE. Please advice. Thanks and God bless you.

[Former Member · Accepted solution]

Could you little more specific.From what I understood from your question this would be the solution...

Insert a group for the Part Number field....

[Former Member · Accepted solution]

wow thanks for your reply. Okay. I'm building a report in crystal report and i know that i have multiple records in my database but i'm looking for a way to filter the records i'm getting to give me single record for each part number field that i wanted pull.

Another problem that i'm facing is that after building my formular then try to drag the formular field to report the whole data on the report changed don't know why the formular could change data on the report. Please advice. your efforts and time are appreciated. Thanks

[Former Member · Accepted solution]

Okay....first you need to insert a group for the field (Part Number) like I mentioned in my previous thread then place all your fields in that group. If the data in all the other fields is the same then you should see only one row of data. Please include the formula and any other information that could help to answer your question.

Thanks!

[Former Member · Accepted solution]

Thanks for your reply. this is the formular that was changing my report " ( {PARTS_MASTER.QTY_OH}-{PARTS_MASTER.QTY_RESERVED} + {PO_DETAIL.QTY_ORDERED}-{PO_DETAIL.QTY_REC} ) " Appreciate your time. Thanks

[Former Member · Accepted solution]

where do you have this formula placed?? and could you please describe the layout of your report?? Such as groups and what you have in the detail section...

[Former Member · Accepted solution]

okay i'm building the report from scratch. I have not get to the formular stage yet. i cannot get the right grouping for the result i want. i just want single data to display. e.g

DIAGRAM A

PN QTY_OH QTY_RESERVE QTY_ORDERED

MXSW300 300 10 290

FDE21094 200 0 200

YQW789 180 2 178

DIAGRAM B

BEFORE MY REPORT SHOWING

PN QTY_OH QTY_RESERVE QTY_ORDERED

MXSW300 300 10 290

MXSW300 300 10 290

MXSW300 300 10 290

MXSW300 300 10 290

MXSW300 300 10 290

MXSW300 300 10 290

MXSW300 300 10 290

MXSW300 300 10 290

FDE21094 200 0 200

FDE21094 200 0 200

FDE21094 200 0 200

FDE21094 200 0 200

FDE21094 200 0 200

FDE21094 200 0 200

YQW789 180 2 178

YQW789 180 2 178

YQW789 180 2 178

YQW789 180 2 178

YQW789 180 2 178

YQW789 180 2 178

YQW789 180 2 178

YQW789 180 2 178

YQW789 180 2 178

I WANT TO SHOW LIKE DIAGRAM A. PLEASE ADVICE SIR. THANKS

[Former Member · Accepted solution]

Ok....Got to Insert-> Group (under the common tab select "Part Number field) then press "OK"

Once the group is created, place all the fields (PN /QTY_OH /QTY_RESERVE /QTY_ORDERED) in your Group Footer.

Refresh the report and this should work....

[Former Member · Accepted solution]

Thank you sir for your respond. I want each row of the same PN, QTY_OH, QTY_RESERVED AND SO ON..... To show once.

just one row with same records at a time not grouping all the mutiple records together. Thanks

[Former Member · Accepted solution]

This will work for you, trust us

Create a goup on the part number

Put all the information regarding the part in your group header as if it were the detail line, and then suppress the detail lines and the group footer. This will print one record per part number even though it will retrieve all records.

Note Putting the information in the group header and suppress the detail & group footer will display the first record only in the group. You could also put the information in the group footer & suppres the group header instead.

But I am curious as to why you are getting duplicate records...

[Former Member · Accepted solution]

Oh thanks for your respond. I found the solution to that. I use SELECT DISTINCT RECORDS under DATABASE MENU. Please i'm still having some issue with the report.

Now I have already create a formula called ON ORDER that my report is showing this way below:

PN QTY_OH QTY_RES CAL ON ORDER

NAS2833T8PT 36,030.00 6,500.00 29,530.00 1,560.00

NAS2833T8PT 36,030.00 6,500.00 29,530.00 25,000.00

This part has the same part number but has 25,000 open quantities

Now i need to add 1,560.00+25,000.00 PLUS (CAL) 29,530.00 together

so i need to have formula called TOTAL

TOTAL = 1,560.00 + 25,000.00 + 29,530.00 = 56,090.00

TOTAL = 56,090.00

THEN I WANT TO HAVE A LINE THAT WILL SHOW

PN QTY_OH QTY_RES CAL ON ORDER TOTAL

NAS2833T8PT 36,030.00 6,500.00 29,530.00 26,560.00 56,090.00

Please advice ASAP. Thank you so much all......

[Former Member · Accepted solution]

Okay. You still need to group on the PIN.

Then summaryise the ON ORDER in the group footer.

Add a formula in the group footer that is the CAL. Something like

@CAL

Maximum(CAL)

will work.

Then put another formula in the footer

@TOTAL

@CAL + sum(ON ORDER, PIN)

[Former Member · Accepted solution]

okay i got the maximum and get ON ORDER grand total but the formula for TOTAL ROW "saying the formula has error did i want to save it anyway" @CAL + sum(ON ORDER, PIN) please what is the correct formula to calculate " CAL PLUS TOTAL OF ON ORDER"

CAL + GRAND TOTAL OF ON ORDER. Thank you for your time. Appreciate all. Thanks

[Former Member · Accepted solution]

@CAL + sum(ON ORDER, PIN)

The second field in this statement may be the problem. Go to your report field list and replace it with the sum for ON ORDER in the group

[Former Member · Accepted solution]

PLEASE explain that section of replacing ON ORDER WITH SUM clearly. Still giving error. Thanks

[Former Member · Accepted solution]

It highlight the whole formula saying " The remaining text does not appear to be part of the formula"

[Former Member · Accepted solution]

>

> @CAL + sum(ON ORDER, PIN) 
> 
> The second field in this statement may be the problem.  Go to your report field list and replace it with the sum for ON ORDER in the group

Go to the bottem of the report field list

There you will find a list of the summaries you have placed in the group

Find the group for PIN and look for the summary for ON ORDER

Highlight sum(ON ORDER, PIN) in your formula

Double click on the summary for ON ORDER in the field list and it will replace what you have highlighted

If that doesn't work, post your formula here

Edited by: DebiHerbert on Sep 11, 2010 12:24 AM / 6:24PM EST