Trying to create a report that would be dead easy in plain SQL but trying to do it without a SQL view is beyond my CR capability and it turns out the SQL view tends to get removed during a DB upgrade for the 3rd-party application software.
Crystal Reports 11
MS SQL Server 2008
Here's the basic content of the data in question (info on an employee's pay for one pay period):
payroll_num, employee_id, company_id, payroll_date, gross_pay, contribution_401k, loan_repayment_401k, ss_deduction,
(and some others--that's enough to illustrate the issue)
The report needs to list (for a given payroll number):
Some info about the employee (available by joining in the employee table; no problem)
gross_pay, contribution_401k, loan_repayment_401k, and some others from the payroll row in question (also no problem).
But, on the same line, year-to-date totals for gross-pay, 401K contributions, 401K loan repayments, and so on.
My solution was to create a SQL view, that for a given payroll_num, employee_id, & company_id would return the summed values from the various rows in the payroll table for the appropriate year for the specified payroll_num (just a surrogate for the payroll_date; but that's what the client wants to use).
That worked great; but then the client got a software upgrade and a DB upgrade too--and my slick little SQL view got dropped (or just not copied over to the new DB) in the upgrade process--whatever the process, it's gone now. These folks aren't technical enough to be happy with just knowing that they've got to reload the SQL view every time there's an upgrade. So, if possible, I'm trying to figure out if there is a way to create this kind of summary info in some kind of SQL formula, or some other way inside Crystal. But, it is unclear to me how to do this, because I want to include a function value in the SQL formula, and that does not seem to be possible.
I need a formula that will do a sum( gross_pay ) [for instance] by selecting from the payroll table (but not the instance the main query uses) where the year of the pay date is the same as the year of the pay date of the specified payroll_num.
So, I'd like a SQL function that would do something like this (this is just an attempt to illustrate the concept, I know it won't work):
[tt]
select sum( gross_pay )
from payroll
where employee_id = @employee_id
and company_id = @company_id
and datepart( year, payroll_date ) = @pr_year
[/tt]
The @-functions I've included here would return these respective values: employee_id, company_id, and year of the row from the main query. I put these in as @-functions only to illustrate that I need the values from the main query, but the subquery here has to read the data from a different instance of the payroll table than the main query uses.
Expressed in SQL, what I envision the entire report doing would look something like this (avoiding the use of the SQL view that provides the year-to-date values):
[tt]
SELECT ... -- stuff from employee table
, pay.gross_pay
, pay.contribution_401k
, pay.loan_repayment_401K
, ( select sum( pr.gross_pay )
from payroll as "pr"
where pr.employee_id = pay.employee_id
and pr.company_id = pay.company_id
and datepart( year, pr.payroll_date )
= datepart( year, pay.payroll_date )
) as "year_to_date_gross_pay"
, -- similar pattern for the other year-to-date sums
FROM payroll as "pay"
JOIN employee as "emp"
on ( pay.employee_id = emp.employee_id
and pay.company_id = emp.company_id
)
[/tt]
So, I'm hoping one of you fine folks can tell me:
o Is there a way to do this as I envision using formulas of some kind? (The data's all got to be on one line in the report; the YTD sums cannot be in a separate subreport, for instance); or
o Some other way to do what I need to do, without using the convenient SQL view, of course. If it can be self-contained in Crystal, that'd be great for the client.
If there's docs or another thread you can point me to, that's appreciated.
Thanks!
John Craig
Alpha-G Consulting, LLC
Crystal Reports 11
MS SQL Server 2008
Here's the basic content of the data in question (info on an employee's pay for one pay period):
payroll_num, employee_id, company_id, payroll_date, gross_pay, contribution_401k, loan_repayment_401k, ss_deduction,
(and some others--that's enough to illustrate the issue)
The report needs to list (for a given payroll number):
Some info about the employee (available by joining in the employee table; no problem)
gross_pay, contribution_401k, loan_repayment_401k, and some others from the payroll row in question (also no problem).
But, on the same line, year-to-date totals for gross-pay, 401K contributions, 401K loan repayments, and so on.
My solution was to create a SQL view, that for a given payroll_num, employee_id, & company_id would return the summed values from the various rows in the payroll table for the appropriate year for the specified payroll_num (just a surrogate for the payroll_date; but that's what the client wants to use).
That worked great; but then the client got a software upgrade and a DB upgrade too--and my slick little SQL view got dropped (or just not copied over to the new DB) in the upgrade process--whatever the process, it's gone now. These folks aren't technical enough to be happy with just knowing that they've got to reload the SQL view every time there's an upgrade. So, if possible, I'm trying to figure out if there is a way to create this kind of summary info in some kind of SQL formula, or some other way inside Crystal. But, it is unclear to me how to do this, because I want to include a function value in the SQL formula, and that does not seem to be possible.
I need a formula that will do a sum( gross_pay ) [for instance] by selecting from the payroll table (but not the instance the main query uses) where the year of the pay date is the same as the year of the pay date of the specified payroll_num.
So, I'd like a SQL function that would do something like this (this is just an attempt to illustrate the concept, I know it won't work):
[tt]
select sum( gross_pay )
from payroll
where employee_id = @employee_id
and company_id = @company_id
and datepart( year, payroll_date ) = @pr_year
[/tt]
The @-functions I've included here would return these respective values: employee_id, company_id, and year of the row from the main query. I put these in as @-functions only to illustrate that I need the values from the main query, but the subquery here has to read the data from a different instance of the payroll table than the main query uses.
Expressed in SQL, what I envision the entire report doing would look something like this (avoiding the use of the SQL view that provides the year-to-date values):
[tt]
SELECT ... -- stuff from employee table
, pay.gross_pay
, pay.contribution_401k
, pay.loan_repayment_401K
, ( select sum( pr.gross_pay )
from payroll as "pr"
where pr.employee_id = pay.employee_id
and pr.company_id = pay.company_id
and datepart( year, pr.payroll_date )
= datepart( year, pay.payroll_date )
) as "year_to_date_gross_pay"
, -- similar pattern for the other year-to-date sums
FROM payroll as "pay"
JOIN employee as "emp"
on ( pay.employee_id = emp.employee_id
and pay.company_id = emp.company_id
)
[/tt]
So, I'm hoping one of you fine folks can tell me:
o Is there a way to do this as I envision using formulas of some kind? (The data's all got to be on one line in the report; the YTD sums cannot be in a separate subreport, for instance); or
o Some other way to do what I need to do, without using the convenient SQL view, of course. If it can be self-contained in Crystal, that'd be great for the client.
If there's docs or another thread you can point me to, that's appreciated.
Thanks!
John Craig
Alpha-G Consulting, LLC