T-SQL and Crystal Report
Well thanks to click on this option this page is dedicated as you guess to Crystal report and T-SQL, I had been working with reports for the last five years and every day is always a thing that you learn, and this is natural in the IT world the only static is the change, the rest is in constantly change, and thanks for that.
The approach for development systems (SDLC) stick to this reality, after the system has finished there is a improve stage to maintain update the application or a particular module.
Well lets talk about the T-SQL and Crystal report, I will try to isolate the article from the version of the two products it self.
There are sometimes when the report is so complex to obtain the data required for the user that the run time is so slow and the consumption of resources huge that regardless the configuration of your environment i.e a production database and a Data warehouse, always we need to make changes in the table in order to optimize the query that is used as data source for our final report.
I tried the typical approaches like make a View with indexes, create a SQL and tuned the tables involved in the query statement but still no luck.
So I found that run a T-SQL reduce the execution tiime without to hammer the database.
Lets make a example to make more clear this, first create the following procedure in Northwind database:
"create procedure sp_example
@dateini datetime, @datefin datetime
as
SELECT Orders.OrderID AS OrderID, Orders.CustomerID AS ASCustID, Customers.CompanyName, Customers.ContactName, Customers.ContactTitle,
Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate
FROM Orders INNER JOIN
Employees ON Orders.EmployeeID = Employees.EmployeeID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID
where Orders.OrderDate>=@dateini
and Orders.OrderDate<@datefin"
this stored procedure is simple but my intention is show a technique in crystal and T-SQL, as you can see the stored procedure has two parameters that we have to provide in order to run successfully, the initial date and the final date, the data type is date time format.
Now everything is in Crystal side so we start to create the report using the standard wizard, depending the version of your CR you have to select that include the stored procedure objects, when the Cr finished to parse the query, click on Next as I said the rest depend of the version that is running, the dynamics variables has been added and when you run the report it will prompted for these two valuables, enter and enjoy.
I included the video for CR 8.5 click here, select full screen to appreciate this video
The video for the CR XI next will be here
Please send your comments in order to improve this article.
The approach for development systems (SDLC) stick to this reality, after the system has finished there is a improve stage to maintain update the application or a particular module.
Well lets talk about the T-SQL and Crystal report, I will try to isolate the article from the version of the two products it self.
There are sometimes when the report is so complex to obtain the data required for the user that the run time is so slow and the consumption of resources huge that regardless the configuration of your environment i.e a production database and a Data warehouse, always we need to make changes in the table in order to optimize the query that is used as data source for our final report.
I tried the typical approaches like make a View with indexes, create a SQL and tuned the tables involved in the query statement but still no luck.
So I found that run a T-SQL reduce the execution tiime without to hammer the database.
Lets make a example to make more clear this, first create the following procedure in Northwind database:
"create procedure sp_example
@dateini datetime, @datefin datetime
as
SELECT Orders.OrderID AS OrderID, Orders.CustomerID AS ASCustID, Customers.CompanyName, Customers.ContactName, Customers.ContactTitle,
Orders.OrderDate, Orders.RequiredDate, Orders.ShippedDate
FROM Orders INNER JOIN
Employees ON Orders.EmployeeID = Employees.EmployeeID INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID
where Orders.OrderDate>=@dateini
and Orders.OrderDate<@datefin"
this stored procedure is simple but my intention is show a technique in crystal and T-SQL, as you can see the stored procedure has two parameters that we have to provide in order to run successfully, the initial date and the final date, the data type is date time format.
Now everything is in Crystal side so we start to create the report using the standard wizard, depending the version of your CR you have to select that include the stored procedure objects, when the Cr finished to parse the query, click on Next as I said the rest depend of the version that is running, the dynamics variables has been added and when you run the report it will prompted for these two valuables, enter and enjoy.
I included the video for CR 8.5 click here, select full screen to appreciate this video
The video for the CR XI next will be here
Please send your comments in order to improve this article.
