Crystal Reports

ron22

ron22

Cleared for Takeoff
Joined
Jun 19, 2008
Messages
1,446
Location
MN
Display Name
Display name:
Ron Hammer
Anyone here know Crystal Reports well?
 
Holy cow, does that still exist?

I used it heavily like 5 years ago...what do you need?
 
Well they are using Crystal Reports 2008:D

Crystal Reports is being used with Jobboss software (Crappy job shop management software) It is uses an Access Database but with limited access.

I have a factory Crystal Report that shows all the parts needed for a job that are not already "pulled" for the job.
My friends want the PO Due date added to the report so they can quickly look to see when all the parts will be there.

I modified the stock report by adding a link to the PO table. I linked the PO table by part number.

The problem is the PO table has the data from every PO for that part number. There is a PO open status in the table.

Stock report
Code: 
Material   Qty Still Needed
1001       10
1005        5

If I just add the due date to the report I get
Code: 
Material   Qty Still Needed     Due Date
1001        10                      1/1/2001
1001        10                      2/1/2010
1001        10                      3/1/2012
1001        10                      7/1/2013
This makes some sense since that is what is the PO table. The problem beside the multiple entries is that if there is no PO for a part (1005) it will not show it at all.

So then I added the code PO_Stauts <> "CLOSED" to the report I get
Code: 
Material   Qty Still Needed      Due Date
1001        10                       7/1/2013
It works to the point of only showing the open PO's but it still has the problem if there is no PO for a part (1005) it will not show it at all.

Ideally they would like the report to show
Code: 
Material   Qty Still Needed      Due Date
1001        10                       7/1/2013
1005         5
Possible add a comment that the part is not ordered yet.


They had a bunch of other reports that needed modifying also but I have them all working.

So anyideas?
 
Last edited:
Left outer join.

Now I made a decent living for 5 years eradicating systems like this.
 
I had no idea crystal could use excel as a data source. Amazing.

Are you querying via a SQL statement? Paste in the current one if so. I smelled outer join too, but I have no idea how excel plays into it.
 
Is an Excel database Access under the covers or just Excel tables treated as a database or flat files? At any rate, you need to get the query working using the right filter criteria (watch for stuff like case sensitivity, whitespace in fields, etc.) and then optimize it using an index on the filter criteria. CR can be dog slow if it's anything like what I used ca. 1998.
 
Ok first I am not sure what I was thinking. It is an Access Database :mad2:

Left outer join works to a point. If I delete the part were I am checking to make sure the PO is closed. Then it will list all part numbers even if there is no PO for the items

IF I add
{PO_Detail.Status} <> "closed"
In the Selection Expert in either Record or Group.

Then it will only list the ones with Open PO if there is no PO it will not display the part number
 
ron22 said:
Ok first I am not sure what I was thinking. It is an Access Database :mad2:

Left outer join works to a point. If I delete the part were I am checking to make sure the PO is closed. Then it will list all part numbers even if there is no PO for the items

IF I add
{PO_Detail.Status} <> "closed"
In the Selection Expert in either Record or Group.

Then it will only list the ones with Open PO if there is no PO it will not display the part number
Click to expand...

Possibly, depending on the structure add an
Or is null
After your <> 'closed'

When you use <> 'closed' you could have possibly just got rid of your outer join, by forcing data to be in the right side, which would defeat the purpose of the outer join.

NULL is neither "equal to" nor "not equal to" anything... it's either "IS NULL" or "IS NOT NULL".... So ya gotta ask for them explicitly if you want them.
 
Last edited:
I have a similar situation in Access, had to go with a union query.
 
ron22 said:
Anyone here know Crystal Reports well?
Click to expand...
IIRC (about 6-7 years ago) they had the best on-hold music I've ever heard. They even let you select the type of music you wanted rock, jazz, classical etc...

Not very helpful for your situation, but that memory has stuck with me for a while.
 
Seems like the data model is incomplete, as I don't see how the Material and PO are tied to the Job. I would think the Material having a not in inventory status would require a PO. So, how do you determine which POs are associated with the Job for which Materials have been ordered?
 
JMichael said:
So, how do you determine which POs are associated with the Job for which Materials have been ordered?
Click to expand...

You are correct this is not a perfect answer.
The company is a contract PCB assembly shop. So there is a good chance if the part is on order it is for this job. A lot of the parts are only used for one job. The common parts are ordered in large lots. So it should not matter if they are ordered for this job or another since there will be extras ordered. Plus if it shows you need 9 for the job and 9 are on order you can assume.
Like I said not perfect. But when a customer calls and wants to know if they can get a job sooner they just need a quick way to see when all the parts will be there. Plus see if they are even ordered yet.
It is not unheard of to steal parts from one job to get a other one done sooner.
 
bartmc said:
Possibly, depending on the structure add an
Or is null
After your <> 'closed'
.
Click to expand...

I will not be able to try this until later next week. I need to do some real jobs and make some $$$$
 
bartmc said:
Possibly, depending on the structure add an
Or is null
After your <> 'closed'

When you use <> 'closed' you could have possibly just got rid of your outer join, by forcing data to be in the right side, which would defeat the purpose of the outer join.

NULL is neither "equal to" nor "not equal to" anything... it's either "IS NULL" or "IS NOT NULL".... So ya gotta ask for them explicitly if you want them.
Click to expand...

Changed to ({PO_Detail.Status} <> "closed" or isnull({PO_Detail.Status})) No parts showed up
{PO_Detail.Status} <> "closed" alone works to show only open PO's
isnull({PO_Detail.Status} alone works to only show parts with no PO's
isnull({PO_Detail.Status}) or {PO_Detail.Status} <> "closed" only show up parts with no PO's
:mad2::mad2:
 
Last edited:
Ron -

It sounds like you need to make two separate queries, then combine both results in a third, it can't be done in one query. I've run into this quite a bit in Access. My inventory query I actually had to build seven individual queries, and then run a left join on the product_id with all 7 queries to get the proper results.
 
Last edited:
Seems like I remember Access handling joins/nulls in a weird way, too. Sounds like Ed might have the most viable solution, unless you want to throw the whole thing on MySQL where the join/null stuff is more predictable.
 
CJones said:
Seems like I remember Access handling joins/nulls in a weird way, too. Sounds like Ed might have the most viable solution, unless you want to throw the whole thing on MySQL where the join/null stuff is more predictable.
Click to expand...

Yeah, anytime you have one table that may not have all the matching records in another table in the query, I've always ended up having to two two separate queries, and then left join the two queries. Especially when you throw a WHERE OR or IsNull in there. 16 years of slogging around in nothing but Access, I'm used to it.
 
EdFred said:
Yeah, anytime you have one table that may not have all the matching records in another table in the query, I've always ended up having to two two separate queries, and then left join the two queries. Especially when you throw a WHERE OR or IsNull in there. 16 years of slogging around in nothing but Access, I'm used to it.
Click to expand...

Holy flashback! I've been out of this stuff for years, but ... Wow. Good times...
 
EdFred said:
Yeah, anytime you have one table that may not have all the matching records in another table in the query, I've always ended up having to two two separate queries, and then left join the two queries. Especially when you throw a WHERE OR or IsNull in there. 16 years of slogging around in nothing but Access, I'm used to it.
Click to expand...

One of our customers uses an Access database to track (prioritization, pick status, shipment status) their small parcel shipments. It works great until the transportation department decides to change the format of the Excel spreadsheet that the database imports. Then it becomes 12 straight ours of "WTF?!" and "How the hell does this even work at all!?" to get it to deal with the new data layout.

I am convinced that MS Access runs on the "Magic" database engine.
 
CJones said:
One of our customers uses an Access database to track (prioritization, pick status, shipment status) their small parcel shipments. It works great until the transportation department decides to change the format of the Excel spreadsheet that the database imports. Then it becomes 12 straight ours of "WTF?!" and "How the hell does this even work at all!?" to get it to deal with the new data layout.

I am convinced that MS Access runs on the "Magic" database engine.
Click to expand...

We use it to run our entire business. 16 years and still chugging along.
 
CJones said:
One of our customers uses an Access database to track (prioritization, pick status, shipment status) their small parcel shipments. It works great until the transportation department decides to change the format of the Excel spreadsheet that the database imports. Then it becomes 12 straight ours of "WTF?!" and "How the hell does this even work at all!?" to get it to deal with the new data layout.

I am convinced that MS Access runs on the "Magic" database engine.
Click to expand...

You just described why I was employed for half a decade.... We called those "our bread and butter" i.e. getting rid of access/excel applications.
 
Ok I kind of figured out how JobBoss does there querys. The have a htm file using VBScript and SQL to create a temporary file. This temporary file is the one Crystal Reports uses.
What I did originaly is modify the report in Crystal Report that uses the Temp data then created a ODBC connected to the Access file (limited control).
Because there is limited control to the file you can not open it up in Access and do normal Access stuff.
Now I just need to learn SQL :mad2: I did say this was for a friend not for $$$
Code: 
		sql =  "SELECT DISTINCT Material_Req.Material_Req AS nMaterial_Req,"_
					& "Material_Req.Job AS sJob,"_
					& "Material_Req.Material AS sMaterial,"_
					& "Material_Req.Description AS sDescription, " _
					& "Material_Req.Drawing_ID As sDrawing, " _
					& "Material_Req.Est_Qty AS nEst_Qty,"_
					& "Material_Req.Act_Qty AS nAct_Qty,"_
					& "Material_Req.Pick_Buy_Indicator, " _
					& " '' AS sVendor, " _
					& "Job.Customer_PO AS sPO, " _
					& "Material_Req.Due_Date AS dDue_Date, " _
					& "Q1.nOn_Hand_Qty, " _
					& oCmn.sSQLConvertToString("1", sDBType) & " as sHeader " _					
					& "FROM (Job INNER JOIN Material_Req ON Job.Job = Material_Req.Job) " _
					& "LEFT JOIN " _
						& "(SELECT Material_Location.Material, " _
						& "Sum(Material_Location.On_Hand_Qty) AS nOn_Hand_Qty " _
						& "FROM Material_Location " _
						& "GROUP BY Material_Location.Material " _
					& ")AS Q1 ON Material_Req.Material = Q1.Material " _
					& sWhere

Then I can do like Ed says and make to querys and then combine them. Sad part is the fields I need are there (sPO & dDue_Date) they just put stupid info in there.
 
You must log in or register to reply here.
Back
Top