Previous Function not allowing grouping - URGENT

[FrequentUser]

Attempting to use formula: {@Initial v Revisions}
if {@Plan #} = previous({@Plan #}) then
"Revision"
else "Initial"

However, it does not allow grouping on this formula.
Under the Crystal Reports X Help index it states:
"Note: Using this function in a formula forces the formula to be evaluated at print time. For more information on evaluation time considerations, see Evaluation Time."

Is this why no grouping or select experts can be performed on this formula?



Crystal Reports X, SQL Server, ODBC

 

[Madawc]

Grouping is done by individual assessment of each record, or rather each 'row', before checking what relationship it has with other records. Summary, previous etc. cannot be used; that's how Crystal does things.

What you could do is add the same table a second time, as an alias. I'm assuming their is some ID or date-indicator that shows you when these are different. You'll need to link where they are different, which I've never done but it should be possible.

On that basis, if the second record in the 'row' had a real value, then it is a revision. If the value is null then it is initial - and note that your ISNULL({your.value}) test must be done first. Crystal always stops and returns nothing if it hits a null without an ISNULL test.

Since this involves just one 'row', it should group OK.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[FrequentUser]

when I added the alias table APACT_1 the dates came in just the same as the original table.

You mentioned "link where they are different." I linked from the Date/time field in the original to the Date/time field in the alias table. Is that correct?

Crystal Reports X, SQL Server, ODBC

 

[Turkbear]

Hi,
The Aliased table's data is the same, obviously, as the original table so that link will find that the data would always be the SAME, not different and will, therefore join all the records with the same date.

Not sure what you want to do is possible in Crystal ( or in the Database either, for that matter..)

You would need to determine some way to link tha tables so that the correct records are linked and then add an additional link that would be specified as a '!=' link to check for revisons if the data model allows it..Cannot be sure from your description.
( Maybe if the DateTimes are different but the Plan# is the same..)









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

You could create a command like:

Select min(`table`.`date`) as mindate, `table`.`cust_ID`
from `table` `table`
Group by `table`.`cust_ID`

Then link the command to your main table on {table.cust_ID}. This will give you the minimum plan date per customer.

Next create a formula:

if {table.date} = {command.mindate} then "Initial" else
"Revision"

Then insert a group on this formula.

-LB

 

[FrequentUser]

Lbass,

What do you mean "from 'table'.'table'

I am not that familiar with SQL commands; do I need 2 tables there?

Also where you have 'cust_ID' I would have {@Plan#} =
If IsNull({VBRBLDG.PLANNO}) or length(trim({VBRBLDG.PLANNO})) = 0 then
{VBCBLDG.PLANNO}
Else
{VBRBLDG.PLANNO}

Can you even reference a formula in SQL commands?

Crystal Reports X, SQL Server, ODBC

 

[synapsevampire]

LB didn't type 'table'.'table', rather from `table` `table`, which means one table and then assigning an alias of the same name, redundant, redundant, but it's not using 2 tables (can't type that word only once yanno).

Since you appear to be looking for a previous versus from current I'm not certain that the solution is correct.

Things to consider before your next post:

We don't know what's in: {@Plan #}. That's a formula, and you don't supply what it does or what's in it.

We don't know what you are trying to do, try posting:

Example data (what's returned by the database)
Expected output (what you want fro the report)

The SQL Command might be your solution, but you should be seeking architectural advice rather than stating that we're to figure out how to use an unknown formula to do the unkown.

-k

 

[FrequentUser]

Ok, here it goes . . .

{@Plan#} =
If IsNull({VBRBLDG.PLANNO}) or length(trim({VBRBLDG.PLANNO})) = 0 then
{VBCBLDG.PLANNO}
Else
{VBRBLDG.PLANNO}

VBRLDG is table tracking Residential Building Permits.
VBCBLDG is table tracing Commercial Buildling Permits.
PLANNO is building plan number

Background: a single permit can only have either a Residential or Commercial Buidling Plan #, thus the formula to capture one or the other
Each plan however, can have multiple reviews (if rejected engineer resubmits with revisions)

Example data received:
{@EmpID} {@Plan #} {APACT.COMPDTTM} {APBLDG.APNO}
ASV Q-06-00232 05/02/06 060251464
06/08/06
07/09/06
ASV Q-06-00234 03/04/06 060255607
04/17/06

Add'l info:
{@EmpID}= {EMPLOYEE.EMPFIRST} & " " & {EMPLOYEE.EMPLAST}
simply returns ID of plan reviewer completing review
{APACT.COMPDTTM}= date each plan review is completed


Expected Output:
{@EmpID} {@Plan #} {APACT.COMPDTTM} {APBLDG.APNO}
ASV Q-06-00232 05/02/06 060251464
ASV Q-06-00234 03/04/06 060255607

*to display only the date of the first review performed which is the initial review.

Crystal Reports X, SQL Server, ODBC

 

[lbass]

Well, this is simpler than I thought. Insert a group on {@Plan#} and then go to report->selection formula->GROUP and enter:

{APACT.COMPDTTM} = minimum({APACT.COMPDTTM},{@Plan#})

This underlines the importance of providing complete information initially.

SV - I actually thought using the "redundant" table name was best practice. I'm in the habit of doing that with Oracle where the owner is first specified with the table name and then is followed by the table name as the alias. Also the "Show SQL Query" with the Xtreme database shows the double reference, so I just assumed again that that was best practice.

-LB

 

[synapsevampire]

No, I agree with aliasing, LB, I just wanted to type redundant redundant for the sake of the reader, and generally aliases are smaller and placed there to shorten typing later.

select A.field1, B.field2
from mywordynamedtable A, muotherlengthynamedtable B

So references to the tables can use A or B instead.

I wouldn't bother using aliases if you're using the same name, it doesn't buy you anything.

-k

 

[lbass]

Thanks for the clarification.

-LB

 

[FrequentUser]

Thanks for the help. The data is now showing the minimum date reviewed.

Crystal Reports X, SQL Server, ODBC