SQL-crystal reports 1

[tempo1]

hi everyone,
I'm facing all kind of difficulties creating my SQL reports into Excel sheets. Is it worthwhile changing into "Crystal reports" ? The fact is, I have no idea what "Crystal reports" is. Is it a package i have to buy ? Is it part of the SQL SERVER 2000? Is it just a code ? Is there a tutorial which someone can recommend ?
Thanks.

 

[rahulroy08]

Hi,

Crystal Reports is a report generating tool, which helps you generate reports by connecting to a specified database.

So I see that you are trying to export your SQL reports to excel.I suggest using Crystal Reports helps you export your reports into various other formats other than just excel.


Rahul.

 

[Ach005ki]

If you have MS Excel, then you can return data from a database (such as SQL Server) via the menu... Data, Import External Data, New Database Query.

This will then prompt you to select a "Data Source" ... if you don't already have an ODBC data source, then you will need to create one (usually accessible from your Control Panel; look under Administrative Tools and you should find a Data Sources (ODBC) icon).

Once you've configured your Data Source Name, you can then use MS Query to pull the info from your SQL database ...

MS Query offers a simple GUI and a "helpful" wizard. The GUI lets you drag and drop tables and fields into your query, but it isn't the easiest tool to use if you write your own SQL code (as the query text box is 50 chars x 10 lines) and MS Query prefers to create its SQL code by using the older ANSI89 (?) join syntax of putting the join in the WHERE clause...

Select table1.col_a, 
     , table1.col_b
     , table2.col_1
     , table2.col_2
  from table1
     , table2
 where table1.col_a = table2.col_1

whereas the more recent ANSI92 syntax puts the join in the FROM clause...

Select table1.col_a, 
     , table1.col_b
     , table2.col_1
     , table2.col_2
  from table1
 inner 
  join table2
    on table1.col_a = table2.col_1

So, if you try to use the latter syntax in your code, or if using more than one left outer join in a query, or correlated subqueries, or queries using derived tables ... MSQuery "disapproves" of and it does so by withdrawing the GUI, withdrawing the facility of parameters in your code and reverting to providing only the very small SQL code box.

All this said, it will still run your code - good luck on debugging it in MS Query (believe me, I've had some fun with this monkey recently).

Mark, somewhere near Blackburn Lancs!

 

[ESquared]

I suggest SSRS. Sequel Server Reporting Services.

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[LNBruno]

I'd suggest bone knives and bear skins over CR....

< M!ke >
I am not a hamster and life is not a wheel.

 

[ruckrock]

Just to let you know....we use CR here at my job and its a pain in the ass. Anytime you change something and have to recompile it, its not fun.

I mean, it does server is purpose I guess, but its rather technical and time consuming to use.

 

[tempo1]

Thanks,
Ach005ki, I tried to follow your instructions but failed in a certain point:
From the "Choose data source" window i selected "Excel files*" and then clicked "Browse" but couldnt find any "data source". where can i find the SQL query result out of which i want to make a report ?
ESquared, could you tell me where to find SSRC.

Thanks a lot!

 

[TysonLPrice]

If might be worth your time to look into running your queries directly from Excel. There have been times I've gotten better results in that it automatically formats them. Go to Data, Get External Data, Create new query.

From that point you'll need to set up your connections. Once that is done and you saved your query you can just run them from there.

 

[tempo1]

Thanks TysonLPrice
This is interesting bu my queries are far more complicated yielded by sp or scripts.
Thanks anyway

 

[Skittle]

Tempo1 - with regard to you not being able to find a data source, you still have to create an ODBC file on your computer you can browse to from Excel. I always find a file dsn is the best one to use as you can store it in a network folder and protect it with AD permissions. Much better than a machine based ODBC link stored on your hard drive.


Dazed and confused

 

[Ach005ki]

Tempo - I'm sorry, I meant to run your queries from excel via MS Query (if you have to return them to Excel).

Take a look at some of my other posts for details on some of the challenges I've faced with having to use Excel's MS Query tool to return data from a SQL Server 2000 instance of an ERP system.

I'm certainly no pro, and only class myself as a tech user, but quite happy to now admit that MS Query will return data from quite complex queries, and allow you to do many other complex tasks but the code-viewing window is quite restricted (as per my moan above)... far better if you can edit your queries in another editor and then paste the text in to MSQuery's code window

Mark, somewhere near Blackburn Lancs!