Crystal Reports

Discussion in 'OT Technology' started by RaginBajin, Jan 13, 2003.

  1. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    Does anyone know Crystal Reports out there?

    We are using version 8.0.1.1, and we are trying to pull data from multiple databases. It's the same query across all of them. We can not figure out how to take all the results and put them inside just one report.

    Does anyone know of any good tutorials out there?

    And does anyone know how to do this multi-db report?

    Thanks
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Multiple queries across multiple databases would most likely require a Union Query to join them altogether before running the report. A report must have a single recordset to pull data from - that's where Union Queries come in handy. And before you go down this road, you may want to create a table with the result set (temporary as it is) since Union Queries rely heavily on indexing and I've been victim of those indexes not pointing to the correct data.
     
  3. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    BTW, my brother is a project manager for Crystal so if there's a specific question, I might be able to ask him for you (if he has time)
     
  4. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    Actually it's not multiple queries.. It's the same query across all fo them. We can't get the report to all appear on one page.. That's where we are having issues.
     
  5. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Okay - you need explain a bit more. The report is based off of a single query that has data from multiple databases? So where is the problem - it doesn't fit on a page, it doesn't group things correctly? It's a bit vague right now. I mean if there's a db1 and a db2 and they both have a table called table1 inside of them and you want to return the same information from both of them (for example, all records with field1 = 1/1/2001), then you have to use a union query - it basically merges the data from two separate queries (which you must do since there are two tables) and returns the results into one recordset. Which in turn I suggested you make a table out of it
     
  6. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    I'll try to describe exactly what we have.. And maybe you can let me know if what I said makes sense....

    We have 8 databases that we pull data from. We can not create another table on our machines.

    We need to run the query on each of those 8 db's, pull the data out of them and report them on one report.

    The first problem is that we are unsure about the correct way in going about pulling the info.

    We tried creating the report a way we found in one of the books, we tried pulling it from 2 db's. When we do pull it, it places them in 1 report but the second report is overlapping the first report.

    Hopefully you can make sense of my debauctle of a thread.
     
  7. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Are the tables and fields identical in each of the databases? Or does each database contain its own info? For example, standarad customer order system, you have may decide to archive your orders in another database every year. The table is the same but the information is different from your current data. Then Mr. Taxman decides to audit all of you orders for the past 7 years. And he asks to present the total number of Nike Air's sold in 7 years. Well you could run the report seven times, or you could join the data together and then run the report. This is what a Union Query does - it queries the data and then sorts them into similar fields. So if table1 in database 1 had a field called cost, and table2 in database 2 had a field called cost, they would all end up in the same column when you joined the info together - just as if they came from the same recordsource.

    Now, let's say I'm off track here and you just have different data in each database. How do they relate to each other? Is one a customer database and the other an order database? In which case you simply join the tables within the query.

    Bottom line, the easiest way to make a report regardless of what app is to simplify it down into one recordset.

    One thing I haven't mentioned is using subreports. Let's say you have one database that keeps track of operational costs, another that keeps track of admin costs, and another for sales. They have nothing in common except that you need to see the results from all of them on one page, and possibly add them all up for a net cost budget. In this case you could start with a blank report and add each report into the main report as a subreport. They would be separate entities within the main report but you could still reference the values within the report (well at least the totals).
     
  8. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    Ahhh... Ok.. Then the Union Query is what we need to do... We have active data that is across all the databases that are the same.. There is the same data across all the databases.. Just different records of course..

    How do we do setup the union query?
    Is it pretty straightforward in Crystal?
     
  9. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Oh jeez - now you are pushing my knowledge - I've never used a Union Query in Crystal. But all it is is x number of SQL queries joined together with the word UNION to join the SQL statements which will return all non duplicate records. UNION ALL will return all records regardless of whether or not they were duplicated. If you can do the first query in design view and then go into SQL, copy it, do the second query, go into SQL view and then insert your first query in front of everything, add the words UNION or UNION ALL at the end of your first query to join it with the second query, you're good to go. Oh and don't forget to take out that pesky semicolon in the middle if it's there!

    PS: Once you have done this, you won't be able to view the query in design mode.
     
  10. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    Ok so it's like a normal union.. But how does it link the other DB's into it?
     
  11. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Well normally you base the query off of one database that has all the links to the other tables (well that's how I do it anyway).
     
  12. RaginBajin

    RaginBajin Have you punched a donkey today?

    Joined:
    Dec 24, 2001
    Messages:
    8,740
    Likes Received:
    0
    Location:
    NoVA
    I just asked about creating db links and they said it would not be possible with the db load that we already put on the servers.. So we have to look at doing it another way..


    :::scratches head::::
     
  13. Kevin

    Kevin New Member

    Joined:
    Aug 27, 2002
    Messages:
    87,634
    Likes Received:
    0
    Location:
    Michigan
    Um...i clicked this because while im working my way through college, i wanted to find work doin crystal reports. Have extensive db experience (oracle mainly, with the usual access knowledge)

    anyway, just wanted to get any info i could from somebody who uses it at there job.

    Pay/ease/exp. req'd
     
  14. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    How bout you provide an answer to this guys question and then we'll tell you what you can expect to earn. :fawk:
     

Share This Page