"Select Distinct Records" is Grayed Out

[bstafford21]

Using Crystal 9

How do you use this option for "Select Distinct Records" if it is grayed out?

Thanks for any help

Bill

 

[lbass]

I think that's only grayed out if you are using a command or maybe a view or stored procedure as your datasource, in which case you should be selecting distinct within the originating query.

-LB

 

[dgillz]

What type of database are you using and how are you connecting to it? This option is also not available if you are using a native connection rather than ODBC.

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[bstafford21]

We are using Betrieve (Pervasive) as the database. Should be able to use I thought.

Any updates to Crystal 9 or other new versions that will accomadate betrieve files?

Also still looking for any other method to delete duplicate rows.

Cheers
Bill

 

[dgillz]

Bill,

Are you connecting directly to the. ddfs or are you using an ODBC data source?

If you are connecting to the .ddfs, don't. Use ODBC instead.

In terms of duplicate rows, Please explain your table structure and relationtions. If you have a one-to-many or many-to-many relationship, then what many people call "duplicate rows" really are not, it is expected behavior.

If you have one-to-many or many-to-many relationships, please explain what you are trying to accomplish, and we will find a way to do it.


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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[bstafford21]

Hi,

I believe this is connected directly to the Field.ddf file.

In order to do the ODBC connection, how do I go about doing that? I thought I had it the correct way and don't know how to do it any other way, unless it was Excel or PowerPoint.

Thanks for the help
Regards
Bill

 

[dgillz]

Setup an ODBC DSN. You most likely already have one. If you want to know how to set one up, go to control panel, admin tools, ODBC data sources, system DSN tab, click add. Select Pervasive ODBC Engine Interface from the list, and follow instructions.

After you have done this, open up yur report in Crystal and set to datasource location, changing it to look at the ODBC DSN you just created.

I am still pretty convinced this will not solve your problem, nor did you address what you are trying to accomplish as I asked.

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[bstafford21]

Hi, sorry about that, I have the following in my Crystal 9 Report

GROUPHEADER 1
COMPONENTS.COMPONENTS PART No

DETAILS
(1)SO_Items.Due Date (2)SO_Items.SO No (3)WO.WO No.
(4)Part.Part No. (5)SO_Items.Qty (6)COMPONENTS.Qty Var
(7)@Material Qty ={SO_Items.Qty}*{COMPONENTS.Qty Var}

GROUP FOOTER
Current Material Inv = This is what I am trying to achieve



Example:
Group Header:
Component Part No. Eperam-5x900x1000

Details:
Due Date SO WO PartNo SO Qty Comp Var @MatQty
12.12.07 12345 54321 0001-00001 5,000 0.16 800
12.12.07 12345 54321 0001-00001 5,000 0.16 800

Group Footer:
Raw Material Inventory= Stock.Qty on Hand

Due to some parts requiring 2 Component Materials to
produce the finished part no 0001-0001, then everything in DETAILS section is listed twice (DUPLICATED) 2 lines with the same thing whenever I add the Stock.Qty on Hand field in the Group Footer.

So the problem is with Material Inventory Stock that is used to make the finished part.

Thanks for any help and hope this makes clear what I am using and doing.

Cheers
Bill

 

[dgillz]

Not really. Please show your table structures and linking.

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[bstafford21]

Okay I will try to give more detail.

The betrieve files used for the Crystal report:

Group Footer:
Components Used to Make Finished Part = Components.PartNo

DETAILS:
SO_Items.Due Date
SO_Items.SO No
SO_Items.Qty

WO.WO No.

Part.Part No

COMPONENTS.Qty Var (MRP multiplier used to calculate amount of material needed)

Can't look at the link now as I am having a problem.

Error "SaCommonControls.dll Can't be loaded"

Most if not all are linked by Part.no

What else do you need to know and how to explain, show or past the link to here.

@Material Qty ={SO_Items.Qty}*{COMPONENTS.Qty Var}
This is the total calculation used to determine actual amount of material needed for each component based on the Qty of the Customers Sales Order.

GROUP FOOTER
Current Material Inv = This is what I am trying to achieve

 

[lbass]

Your issue isn't really clear. If it is just the display of duplicate rows, then if every field is repeating in the detail section, select all fields in the row and format them to "suppress if duplicated". If the issue is that your calculation is inflated because of duplicate rows, then use a running total that evaluates on change of some unique ID field instead of an inserted group summary.

-LB

 

[bstafford21]

My issue is in the Group Footer not giving the correct value of the Stock.onHand field.

When I add the Stock.QtyonHand field, this is when
the rows are duplicated, not so bothered my that because yes I can use the Suppressing and Runing Total.

That Group Footer Stock.QtyonHand field may have 2 to 3 values that it should add up before bringing into the report. Can I also keep this field from producting duplicate rows?

 

[lbass]

Not unless you remove the Stock table from the report and add it in a subreport that is linked to the group. You could then use a shared variable to bring the stock value in and do the calculation.

-LB

 

[dgillz]

Stafford,

Instead of summing the qty on hand take the maximum of it, and hide the detail rows.

I asked for a table layout and linking, and you gave me what you would like your report to look like. There is a limit to what I can do to help you if you are not responsive.

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

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[bstafford21]

I appreciate every response and all the help you give. I think maybe I don't know how to give you what you want to see.

If I could do a screen shot and send it to you, you could see the table layout. If your talking in reference to the Pervasive table I can also do that method. I don't know how to do this just by typing it here. Is there a way to send attachments on here or can I send it personally to you?

I can do the same for the links, except at the moment I am having a problem viewing it and getting an error whenever I click on it. I searched crystal knowledge and nothing comes up with that error. "SaCommonControls.dll can't be loaded"

I really hope I can fix this, so please don't give up on me.

Cheers
Bill

 

[sjbleth]

Hi,

I had two different databases linked together (Excel and SQL) which did not allow the Select Distinct Records to be selected. I removed the SQL database and was able to check the Select Distinct Records option from the Excel file. I then added the SQL database back in to the report. I refreshed the report and it seems to work just fine. Hope this helps.