Crystal Reports join question

Hello all.

I am migrating an access report to crystal reports and having trouble. I have two tables that link each other, but they are different data types. This works fine in access because I can cast one of the fields using cstr(). See query below:

==============================================
SELECT Sum(Tax_Detail.Tax_Amt) AS SumOfTax_Amt, Taxer.Collection_Account

FROM Invoice_Header INNER JOIN (((Invoice_Detail LEFT JOIN Tax_Detail ON cstr(Invoice_Detail.Invoice_Detail) = Tax_Detail.Owner_ID) LEFT JOIN TaxCode ON Invoice_Detail.Tax_Code = TaxCode.TaxCode) LEFT JOIN Taxer ON Tax_Detail.Taxer = Taxer.Taxer) ON Invoice_Header.Document = Invoice_Detail.Document

WHERE (((Tax_Detail.Owner_Type)=0) AND ((Invoice_Header.Document_Date) Between '3/1/2007' And '3/31/2007'))

GROUP BY Taxer.Collection_Account;
==============================================

Does anyone know how I can create this join from Crystal?

Thanks,
bubutYou will need to use Crystals add command through the database expert. There you can paste your sql and it should work.
GJsql

Crystal Reports 11 crashes when adding sub-report

I'm a new user of Crystal Reports 11. I'm able to create a new report without trouble. As soon as I try to add a sub-report to this report, CR crashes and this happens everytime. I uninstalled and re-installed CR thrice, but this problem still persists.

I'd really appreciate if anyone could share why this might be happening?Refer support page of this site
www.BusinessObjects.com|||Hi!

I just wanted to tell you, I migrated from cr 10 to 11 yesterday and had a cr 10 report with a subreport in it, wich I opened in cr 11. And it worked well.
Hope it works out for you.
/Helenius

Crystal Report formula

I am currently working on a payroll distribution report and I am have some trouble. I have pulled in the employees for the payroll along with the gross and net amounts of their paychecks. The only other peice that I need is the OT amount which comes from a different table. I have linked the two tables and when I try to pull in the OT amount with a (IF THEN, so I can try to only pull in the dollar amount for anyone who has an OT code) statement it gives me $0.00 listed for all of their pay codes. I need the OT dollar amount for just those that had OT and $0.00 for those that did not. Each person has several pay lines and I need everyone not just those with OT so filtering out the OT code will not work. Any suggestion would be deeply appreciated.

Example of what I am getting
Gross Net OT
398 John Doe 500.00 150.00 0.00 (This is a base pay)
398 John Doe 500.00 150.00 50.00 (I only need this line if an individual had OT)

What I need to see:
Gross Net OT
398 John Doe 500.00 150.00 50.00 (Had OT)
399 Sue Brown 600.00 250.00 0.00 (Did not have OT)If your OT table doesn't allow for nulls, you wouldn't need an IF statement. If so, you'd just check if it was null - if so put 0, if not put the value.
I.E.: if IsNull({Overtime.OT}) Then $0 Else ${Overtime.OT} //the $ sign is for converting to currency

Or am I not understanding your question?

Cross Server Error Handling

We are having trouble with cross server error handling.
From what we've seen, if we run a query on Server X that
goes cross server to Server Y and then errors on Server Y,
the error number (@.@.error) doesn't get passed back to
Server X. How do we handle error handling then? How can
we get that error code back?"Bodel" <anonymous@.discussions.microsoft.com> wrote in message
news:0d7301c46e9e$728edd30$a601280a@.phx.gbl...
> We are having trouble with cross server error handling.
> From what we've seen, if we run a query on Server X that
> goes cross server to Server Y and then errors on Server Y,
> the error number (@.@.error) doesn't get passed back to
> Server X. How do we handle error handling then? How can
> we get that error code back?
I'm not completely sure on this, but...
Have you tried running it as part of a Distributed Transaction
Rick Sawtell
MCT, MCSD, MCDBA|||I have tried a variety of things including trapping the
error msg in the output parameter of a stored proc on
Server Y and then execute that on Server X. But that
doesn't work either. I can't find any way to bring it
back using just T-SQL. But maybe I don't understand what
you mean?
>--Original Message--
>"Bodel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0d7301c46e9e$728edd30$a601280a@.phx.gbl...
>> We are having trouble with cross server error handling.
>> From what we've seen, if we run a query on Server X that
>> goes cross server to Server Y and then errors on Server
Y,
>> the error number (@.@.error) doesn't get passed back to
>> Server X. How do we handle error handling then? How
can
>> we get that error code back?
>
>I'm not completely sure on this, but...
>Have you tried running it as part of a Distributed
Transaction
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

Cross Server Error Handling

We are having trouble with cross server error handling.
From what we've seen, if we run a query on Server X that
goes cross server to Server Y and then errors on Server Y,
the error number (@.@.error) doesn't get passed back to
Server X. How do we handle error handling then? How can
we get that error code back?
"Bodel" <anonymous@.discussions.microsoft.com> wrote in message
news:0d7301c46e9e$728edd30$a601280a@.phx.gbl...
> We are having trouble with cross server error handling.
> From what we've seen, if we run a query on Server X that
> goes cross server to Server Y and then errors on Server Y,
> the error number (@.@.error) doesn't get passed back to
> Server X. How do we handle error handling then? How can
> we get that error code back?
I'm not completely sure on this, but...
Have you tried running it as part of a Distributed Transaction
Rick Sawtell
MCT, MCSD, MCDBA
|||I have tried a variety of things including trapping the
error msg in the output parameter of a stored proc on
Server Y and then execute that on Server X. But that
doesn't work either. I can't find any way to bring it
back using just T-SQL. But maybe I don't understand what
you mean?

>--Original Message--
>"Bodel" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0d7301c46e9e$728edd30$a601280a@.phx.gbl...
Y,[vbcol=seagreen]
can
>
>I'm not completely sure on this, but...
>Have you tried running it as part of a Distributed
Transaction
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

Cross Server Error Handling

We are having trouble with cross server error handling.
From what we've seen, if we run a query on Server X that
goes cross server to Server Y and then errors on Server Y,
the error number (@.@.error) doesn't get passed back to
Server X. How do we handle error handling then? How can
we get that error code back?"Bodel" <anonymous@.discussions.microsoft.com> wrote in message
news:0d7301c46e9e$728edd30$a601280a@.phx.gbl...
> We are having trouble with cross server error handling.
> From what we've seen, if we run a query on Server X that
> goes cross server to Server Y and then errors on Server Y,
> the error number (@.@.error) doesn't get passed back to
> Server X. How do we handle error handling then? How can
> we get that error code back?
I'm not completely sure on this, but...
Have you tried running it as part of a Distributed Transaction
Rick Sawtell
MCT, MCSD, MCDBA|||I have tried a variety of things including trapping the
error msg in the output parameter of a stored proc on
Server Y and then execute that on Server X. But that
doesn't work either. I can't find any way to bring it
back using just T-SQL. But maybe I don't understand what
you mean?

>--Original Message--
>"Bodel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:0d7301c46e9e$728edd30$a601280a@.phx.gbl...
Y,[vbcol=seagreen]
can[vbcol=seagreen]
>
>I'm not completely sure on this, but...
>Have you tried running it as part of a Distributed
Transaction
>
>Rick Sawtell
>MCT, MCSD, MCDBA
>
>.
>

Cross DB ownership chaining

Hello,
I am having trouble setting up cross db ownership chaining. Did I omit
anything?
1. Using SQL Server 2000 SP3a.
2. Login as 'sa'.
3. Create new database A1.
4. Create new database A2.
5. Create TableA in database A2.
6. Create view vTableA in database A1 as select * from A2.dbo.TableA
7. Add UserA to database A1.
8. Grant select permission on vTableA to UserA.
9. Enable cross database ownerchip chaining option in database A1 and A2.
10. All objects are owned by dbo and both databases are owned by 'sa'.
11. Log in as UserA and run Select * from vTableA in database A1, get error
"Server user 'UserA' is not a valid user in database 'A2'.".
Unless I'm missing something, I was expecting the last step to work. Any
help greatly appreciated.
PaulUserA needs a security context in database A2, although no object
permissions need be granted. You can either add the user to database A2 or
enable the guest user in database A2.
Hope this helps.
Dan Guzman
SQL Server MVP
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:43D9406C-1C28-4A86-BF39-9A2D7E1B2CFB@.microsoft.com...
> Hello,
> I am having trouble setting up cross db ownership chaining. Did I omit
> anything?
> 1. Using SQL Server 2000 SP3a.
> 2. Login as 'sa'.
> 3. Create new database A1.
> 4. Create new database A2.
> 5. Create TableA in database A2.
> 6. Create view vTableA in database A1 as select * from A2.dbo.TableA
> 7. Add UserA to database A1.
> 8. Grant select permission on vTableA to UserA.
> 9. Enable cross database ownerchip chaining option in database A1 and A2.
> 10. All objects are owned by dbo and both databases are owned by 'sa'.
> 11. Log in as UserA and run Select * from vTableA in database A1, get
> error
> "Server user 'UserA' is not a valid user in database 'A2'.".
> Unless I'm missing something, I was expecting the last step to work. Any
> help greatly appreciated.
> Paul|||(thump on head) Thanks Dan, that did it.
"Dan Guzman" wrote:

> UserA needs a security context in database A2, although no object
> permissions need be granted. You can either add the user to database A2 o
r
> enable the guest user in database A2.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:43D9406C-1C28-4A86-BF39-9A2D7E1B2CFB@.microsoft.com...
>
>