cancel
Showing results for 
Search instead for 
Did you mean: 

Summary with associated fields - How would I do this?

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

I have a group that summarizes the Maximum Date/Time from several operations for that day, showing the last DT within that group. Essentially showing the time the plant finished the days run for each operation group. I would like to be able to also show the Operation Number and the Machine Number that is associated with that maximum DT on the Summary line next to the Maximum DT. How would I do that? I'm using v8.5.

ThanksMike

Accepted Solutions (0)

Answers (19)

Answers (19)

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

Click! The light finally came on! Now that I have the data I need... No let me start a new thread.

Anyway, thank you both.

AFCS Video

1/29/08 6:53

670

8

AFCS015

1/29/08 9:53

150

12

DPS

1/29/08 6:28

919

9

OGP

1/29/08 2:44

891

52

OGS

1/29/08 10:09

262

49

1/28/2008

AFCS Video

1/29/08 23:24

660

1

AFCS015

1/29/08 22:46

150

9

DPS

1/30/08 6:52

919

40

OGP

1/30/08 1:42

281

45

OGS

1/30/08 6:15

262

49

1/29/2008

Former Member
0 Kudos

Post Author: yangster

CA Forum: General

Well there seems to be some confusion going on. You can achieve what you need in one of two ways.

Create an aggregate with sql inside the command but where you've been

misled and should have been corrected is that you can not do a max,

min, sum within sql without using the group by

select max(user_date), user_id, other_value

from table1

where whatever = 'yeah'

group by user_id, other_value

order by user_idThe other way you could achieve this is within crystal itself, group by some field, so in my example user id

then create a formula that would search for the maximum value within that group of data

@max_date

maximum(user_date, user_id)

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

kcheeb:

So when you created the empty report and selected one table and the single field, it wouldn't run either?

How do you get data to your reports?

I may have a misunderstanding after all. I get the data using the Add Database to report, then dropping the fields or formulas into the Graphical Layout. I let the program take care of those SQL details automatically. What I was attempting to do was to create an SQL expression in the SQL Expression Editor to use in the report to get the aggregate data I needed. My understanding was that I could just drop this onto the report the same as a field or formula.

Now you have me wondering, was I supposed to put this aggregate SQL in the Record Selection part of the report?

Come to think of it, I don't believe that would work. The SQL is using parameters that I have no control over. It's used in a webbased environment.

Thanks

Former Member
0 Kudos

Post Author: kcheeb

CA Forum: General

So when you created the empty report and selected one table and the single field, it wouldn't run either?

How do you get data to your reports?

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

Sure, either one that I use still gives me the error, though. So it looks like I will have to pursue the other option you mentioned waaaay back there. Although as I remember that didn't work either as I got the wrong result when I put the field in the group footer. It only showed me the last Operation Number that the report looked at. Not the one associated with the maximum value.

Mike

Former Member
0 Kudos

Post Author: kcheeb

CA Forum: General

It sure sounds like it to me, good thing it's friday!

Can you use the editor to create the Max statement for you? At least that way you can see if it's what you need???

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

LOL, yea lots of fun!

Anyway, I went step further, I created a new blank report added a single table, added a single field then looked at the SQL in the Show SQL Query... on the menu. I copied that SQL into the SQL Expression editor...<drum roll>... and I got the same error! I am beginning to think there is something more wrong here than just my ignorance.

Former Member
0 Kudos

Post Author: kcheeb

CA Forum: General

Having any fun yet?

Try a real simple sql statement, select * from end_of_run

See what happens with that one.

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

I finally found the SQL Designer. It was never installed on my computer, but it was on the original disk. I was able to run it from there.<sigh> It didn't make any difference, though. I still get the same error when I try to input that SQL statement.

Thank youMike

Former Member
0 Kudos

Post Author: kcheeb

CA Forum: General

It should have a list of the tables & columns available to your report and the ability to select them, sum, max, count ... them.

Sorry, I'm not much more help.

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

Okay, thanks.

Former Member
0 Kudos

Post Author: kcheeb

CA Forum: General

It should be available. What kind of datasource are you using for your existing reports?

Try the Database Expert.

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

kcheeb:

Not sure exactly what's going on there, you may want to try removing the quotes around the column names.

The other thing you could try is to use the 'SQL Painter' That's not the right term for it, but hopefully you get my drift. You should be able to create the same sql statement with CR's graphical sql tool.

Yea, I tried removing the quotes, but that didn't work either. I'm not familiar with a graphical sql tool. Is that available in CR 8.5?

Thanks

Former Member
0 Kudos

Post Author: kcheeb

CA Forum: General

Not sure exactly what's going on there, you may want to try removing the quotes around the column names.

The other thing you could try is to use the 'SQL Painter' That's not the right term for it, but hopefully you get my drift. You should be able to create the same sql statement with CR's graphical sql tool.

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

kcheeb:

Your SQL statement woudl be something like this :

select Max(DateColumn), OperationNumber, MachineNumber from tablename group by OperationNumber, MachineNumber

This would give you the Max date for each OperationNumber and MachineNumber

Thank you, but when I put this into the SQL expression editor I get this error.ODBC error:&#91;MERANT&#93;&#91;ODBC Oracle 8 driver&#93;&#91;Oracle 8&#93;ORA-00936: missing expression

quickly fgllowed by this one:Error in compiling SQL expression: Syntax error found here

This is the sql from the editor:select Max(END_OF_RUN."END_DTM"), END_OF_RUN."MAIL_OPERATION_NBR", END_OF_RUN."RUN_MACHINE_NBR"from END_OF_RUN group by END_OF_RUN."MAIL_OPERATION_NBR", END_OF_RUN."RUN_MACHINE_NBR"

BTW I have never tried using sql with CR before, so there is no telling what I might be doing wrong. 😕

Do you have any suggestions?

Thanks

Former Member
0 Kudos

Post Author: kcheeb

CA Forum: General

Your SQL statement woudl be something like this :

select Max(DateColumn), OperationNumber, MachineNumber from tablename group by OperationNumber, MachineNumber

This would give you the Max date for each OperationNumber and MachineNumber

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

kcheeb:

The other way would be to do it through SQL, using an aggregate function (Max) and group by the Operation & Machine numbers.

That sounds interesting. Could you give me some more detail on how I would do it that way?

Thanks

Mike

Former Member
0 Kudos

Post Author: kcheeb

CA Forum: General

If you have a group on the Date, in the Date Footer section, simply drop the Operation Number and Machine Number into the Footer section.

The other way would be to do it through SQL, using an aggregate function (Max) and group by the Operation & Machine numbers.

Former Member
0 Kudos

Post Author: mikebres

CA Forum: General

Anybody?