We help IT Professionals succeed at work.

Record Selection on the Latest Year

Student7
Student7 asked
on
832 Views
Last Modified: 2008-04-24
Crystal V11.  Trying to select only records for the last year for which there is data in each report group.

Example: Group A may have data for years 2001, 2002, & 2003.  I only want the records from 2003.
Example: Group B may have data for years 2006 & 2007.  I only want the records from 2007.

The year is recorded in a database field as a string with length of 2  ie "07"
Comment
Watch Question

CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Here's what my command select ends up looking like:
Select * from JC_PHASE_COST_BALANCE1 inner join
(SELECT JC_PHASE_COST_BALANCE1.Job_Number, Max(Year([JC_PHASE_COST_BALANCE1.Year])) AS mYear
FROM JC_PHASE_COST_BALANCE1
GROUP BY JC_PHASE_COST_BALANCE1.Job_Number) as q1
On JC_PHASE_COST_BALANCE1.Job_Number= q1.groupfield and  year(JC_PHASE_COST_BALANCE1.Year) = q1.myear

Note: JC_PHASE_COST_BALANCE1 is a view, not a table.

Q1: The command will not save "Database connect error.  Invalid Column name 'JC_PHASE_COST_BALANCE1.Year'   I have verified both the view name and the column name as correct
Also error: Invalid column name groupfield

Q2: JC_PHASE_COST_BALANCE1.Year is a string, length 2. Will Max(Year([JC_PHASE_COST_BALANCE1.Year]) work on a string?

Q3: In the final report, I'm going to end up linking to a TABLE JC_JOB_MASTER_MC.  Any problem doing this?  When I simplify the command to just Select * from JC_PHASE_COST_BALANCE1 (to get it working) and link to the table, I get the warning "More than one datasource or a stored procedure has been used in this report.  Please make sure that no SQL Expression is added and no server-side group by is performed.  What is this telling me not to do?  Is the group by in the command still OK?

Q4: Does the command even need the GROUP BY?  Couldn't I do the grouping in the report?
CERTIFIED EXPERT

Commented:
So you only record the year, not the date?

Select JC_PHASE_COST_BALANCE1.* from JC_PHASE_COST_BALANCE1 inner join
(SELECT JC_PHASE_COST_BALANCE1.Job_Number, Max(JC_PHASE_COST_BALANCE1.Year) AS mYear
FROM JC_PHASE_COST_BALANCE1
GROUP BY JC_PHASE_COST_BALANCE1.Job_Number) as q1
On JC_PHASE_COST_BALANCE1.Job_Number= q1.Job_number and  JC_PHASE_COST_BALANCE1.Year = q1.myear

Author

Commented:
Great, this is working.  Would you mind providing a short explanation of the warning in Q3 above? ie an example of what this is telling me not to do and what problems it would cause?

Thanks for your help on this.
CERTIFIED EXPERT

Commented:
I can't provide a justification for this (mainly because I don't have a complete understanding) , but it is often the case that where you use different data sources in one report that this message appears ands the stated constraints apply. Even though all your data is coming from the same database, CR views it as two different sources - a sql command and a database table.
Again, if you use a sql command as a data source, or a stored procedure, CR does not allow you to use SQL Expressions in the same report  (sql expressions are sometimes an alternative to formula fields and benefit from being evaluated on the server rather than within CR).
There is a report option which says ' perform grouping on server'. The constraint message says you can't use it in this case.  Grouping on the server is possible where you don't want detail records at all in the report - you are only interested in summary info.  Frankly, I don't know whether CR sees the Group by in the subquery as grouping; I would guess not - and assume that grouping is only an issue at the outermost level of the sql statement.  In this case there is no such Group By.


Author

Commented:
Thank you for your help and providing the additional information.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.