If you want to create a report in 5 mins, you should follow this tutorial.
All requirement software can be found there. I installed the following:
The tutorial instructs you how to create the report (RDL file): how to define the data source, layout and how to preview the report.
Once you’re done, you can use Report Manager to deploy the report. In my environment Report Manager is available at http://localhost/Reports$SQLExpress and looks as below:
It is possible that Report Manager is available at a different URL on your machine. To check the URL go to Control Panel\Administrative Tools, then Internet Information Services (this is how to get this screen under Windows XP). On the window you get expand Internet Information Services\XXX (local computer)\Web Sites\Default Web Sites. You should see anything that is similar to Reports and ReportServer. As I described before, in my case those virtual directories were named with $SQLExpress suffix at the end.
Anyway, once you get to the Report Manger, click Upload File button. In the new screen choose the file to upload (RDL file you’ve created with the tutorial). Then in main screen of Report Manager select the report you’ve added. Now you should see the report. As the ouput is long, it is paginated so you can use Previuos Page and Next Page buttons to show different parts of the report. You can also choose the format in which the report is presented; by default you can view it in the following formats:
- embedded in the website
- PDF file
- Excel file
That’s all. Now you can create any report you want.
I believe Reporting Services are very powerful tool for report generation. Unfortunately the installation process can be time consuming as you might face some problems with the cofiguration… Of course such problems can be the reason of unsufficient knowledge in SQL Server configuration and management, etc.
Anyway, blank Report Manager screen can be one of the problems, which displays as shown below:
Solution
This issue can be easily fixed by unsetting Enable anonymous access feature for either Reports or ReportServer virtual directory in IIS. In my case (on Windows 2003) I had to switch that of for both that virtual directories.
To do that follow the steps below:
- go to Properties of either virtual directory in the IIS manager
- select Directory Security tab in the Property window
- click Edit in the Authentication and access control section
- disable Enable anonymous access feature
- close all windows by clicking OK buttons in the windows that showed up
I’ve heard that SQL Server developers happen to have problems with CONVERT function when try to obtain a value of datetime type. Recently, I’ve had that problem during a Data Transformation Services (DTS) package implementation.
One of the task in that package was “Transform Data”, which transferred data between a source and destination. To retrieve that data, I used SQL query within which I needed to add a number of milliseconds to a given date. The following snippet shows what I really wanted to execute:
dateadd (millisecond, ?, convert(datetime,?))
In abovementioned snippet, both question marks (?) are different parameters used in that query. The former is sINTEGER (the number of millseconds) whereas the latter is the date kept as VARCHAR. The latter parameter needs convertion to DATETIME.
Unfortunately, I could’t achieve what I really wanted. When I put the abovemeantioned snippet in the query in “Transform Data Task Properties” window, I couldn’t even save it – got the following error: “… argument data type datetime is invalid for argument 2 of dateadd function”.
I spent some time analyzing what was going on. I ran thas query in Query Analyzer with hardcoded values and it was ok. Finally, I used Profiler tool to see what exactly happened when the task (and consequently that query) was run. That gave me the explanation: the second parameter was unnecessary converted by the engine which executed that package. After that convertion, the value of that parameter was wrong and finally was converted second time to a wrong DATE value.
The solution to that problem is quite easy: the described query can be put in a stored procedure which is executed during the transformation. The SQL engine had difficulties defining the data types of the parameters. Luckily, in a stored procedure, the data types are explicitly defined.
Published on
November 7, 2007 in
sql.
Tags: sql.
I learned SQL (basic and quite advanced stuff) at the univerity a few years ago. I’ve been using SQL commands quite often; it’s difficult to imagine an application or a web page without a database in the back-end.
Recently, I’ve discovered I remember little about using JOIN command in SELECTs. There are a few types of JOINs; however, I’ve been using mostly INNER JOIN. I decided to change that and refresh my knowledge in that field. This way, I created the examples queries and put them in the post you’re reading.
Continue reading ‘SELECT + JOIN’
Recently, I’ve learned how to write DTS (Data Transformation Services) in SQL Server 2000. In general, this tool allows transformation of data: copying between different databases (by different vendors) and files (Excel, CSV, etc.). The idea is simple; the usage often priceless.
The execution path of a package is defined using a kind of graph, where nodes are tasks to execute. To operate on data one can write VB/ActiveX script and SQL queries (either direct queries or encapsulated within stored procedures).
Everything would be great if it were easier to write the packages… I believe it’s quite difficult to write the first package, especially with loops – not everything is obvious why/how to use. Also, there’s much less information on the Internet (guides, code examples, fora) than for example for Java programming language…
Anyway, I found the following links useful: SQL DTS and this
