Two data bases

Question

Hello,
I need to display data from two data bases. I have seen posts refering to using an Inner Join statement but I can't seem to get it correct. There is a matching field I can use to link the two. The first data source is "TTrack" and the field is "ttfn00" from "dbo.ttMain." The second data source is "GreatAm_Data" and the field is "No" from "dbo.Escmst_Escrow."

Your assistance is greatly appreciated -

Answer 1

You can't join data between two different data sources in the same design object (tablix, chart, list). 

If both of your databases are on the same server , you can join the tables together by using sql and specifying the database names (i.e. select * from database1name.dbo.ttMain tm
inner join database2name.dbo.Escmst_Escrow ee on tm.ttfn00 = ee.No
)

If both of your databases are on different servers, you have to rethink this.   You can display on a report together if each dataset is displayed in its own design element (i.e. a tablix can only pull data from 1 dataset).  You could display these tablixes side by side or one on top of the other.  However, it sounds like your data wouldn't make sense to do this way. 

You could also leverage sub reports where the first report will pull from one data set and passes the matching field to the subreport.  The subreport will use the parameter to filter the query on the subreport.  This could be expensive since the subreport runs each time.

Dan

Answer 2

That did it! Both data bases are on the same server.

One more question - How would I add more tables from the two data bases?

Lee -

Answer 3

You would need to continue to join the tables together with their common fields using similar syntax to my previous post.  You will need some understanding of the data since joining tables without understanding their structure can lead to misleading results.

It can be daunting, however, writing reports without sql knowledge can be extremely difficult.  Take some time to search the net for sql tutorials (sites like this help: http://www.w3schools.com/SQl/sql_join_inner.asp).

Dan

Answer 4

After I read through the code you provided I came to that conclusion. It looks like I can get this going now.

I've been writing in crystal for years but I don't know a thing about sql. Trying to replicate my crystal reports to SSRS has been very difficult.

Anyhow, I appreciate you help with this one -

Lee - 

Answer 5

SSRS has a graphical designer as well, however, it appears that you can only "add tables" from the same database that your data source is linked to.  Is there any way you can extract the pre-generated sql from Crystal?  We converted from Business Objects a few years ago and it gave us a quick jumpstart to rewriting these queries (however, some of the queries were very inefficient).

Dan

Answer 6

I've been using the graphical designer exclusively up to now. I could extract the sql from crystal but if there are any errors I don't think I could figure out the problem. I might try it a little as I get more comfortable with sql.

Thanks for the link, it looks like there's a lot of good stuff on there. Now that I know my way around SSRS a little I think it's time to invest in a good sql book.


Thanks again -

Answer 7

Hi The_Lee, can you please let me know your views on crystal reports? We have a requirement and I am unable to decide between crystal reports/ crystal server and  SQL reporting services.

I would appreciate your feedback on this please.

Thank you.

Answer 8

Hello -
Sorry for the late response. I just saw your post.

SSRS is very powerful but can be very frustrating if you do not know how to hand code the reports. It doesn't have any of the built in features you will find in Crystal. For example if you want to see all records with a date greater than 1/1/09 you can use the selection manager and the dropdowns. In SSRS you will need to figure out the proper format to put in the Filter box. However, once you know the proper formatit is actually faster to put together in SSRS.

If you don't know sql SSRS will have a very steep learning curve. I've used it for about 2 months and am now able to create the reports fairly quickly and I have begun migrating all my Crystal reports over to SSRS. SSRS processes the queries much quicker than Crystal, especially if you're pulling data from two databases. I finally figured out my issue in the above thread. Crystal use to error out and crash after attempting to run the query for about an hour and a half. SSRS can run the same thing in under 6 seconds!!

Crystal is much easier to get started on if you do not have experience with SQL. The solid understanding I had of Crystal definitely helped me get up to speed with SSRS in a short time.

Sooo, that's the long answer - Here's the short answer - if you're looking at long term results go with SSRS. If you just need to get some reports out in a short time go with Crystal.

One more thing I should mention - all my experience with crystal is using version 8.5 and 9. The newer versions my have significant advances I am unaware of.

Hope this helps -
Lee

Answer 9

Thank you for your response. Just one more question. Without needing to write any asp.net/c# code , is it possible to filter data like :
user inputting -> ID in a textbox,  or
populating a field in a combobox and getting the data and reporting or
updating the database ( i know this is not possible).(if this not possible , can you please show me  a sample code to connect to SQL SERVER and display data. I know I can google this but I couldnt understand  c# or point me to  a link where I can understand? which one is easy c# or asp.net? how to add ref to sql server , is it drag and drop ? I would appreciate any info on this please)


My requirement is that,

I will show a report, then user will click on a link ( field) on that report to get another report or update that data in the database.
Till now I used PHP for this but now I want to migrate.

Is this possible with SSRS?