Tuesday, August 31, 2010

Shared Datasets...

There is a new feature in SQL Server 2008 R2 called Shared DataSets. In prior versions of reporting services one would create datasets to get the desired results/output. In cases where same datasets had to be used in different reports, one had to re-create the datasets. This situation comes up when you have the same parameters in different reports. In SQL 2008 R2, one can create shared datasets or convert a regular dataset to a shared dataset. The pre-requisite for creating a shared dataset is that it has to be done on a shared datasource. Once share datasource has been created, one can create a shared dataset or convert a normal dataset to a shared dataset. The shared dataset can be deployed like a shared datasource, shared datasets gives us improved maintainability. For a more detailed overview, click on the following link:
Prologika Forums:

Friday, August 27, 2010

SQL Server 2008 R2 Trial Version...

Recently I downloaded the evaluation version of SQL Server 2008 R2, the 32 bit version. I installed all the components like Integration Services,Reporting Services and Analysis services. The installation went of smoothly without any issues. I was interested in checking out the new features in the reporting services/ report builder 3.0. There are new data visualization features which have been added. Follow this link to download the evaluation version and look for more SQL Server 2008 resources.

Monday, August 16, 2010

Policy Management...

One of the features available in SQL Server 2008 is the ability to create and evaluate policies on different database components. The policy management feature allows an administrator to effectively maintain and enforce database standards on a server. For example some of the policies would like to check the compatibility level of all the databases on a SQL Server 2008 database server, checking database backups (how recent the backups are..). There are lots of  components on which policies can be created. The policies can be evaluated on demand or run on schedule. In addition to running standard scripts for database administration, policy management gives additional tool kit for the administrators. The policy management option appears Under Management in the Sql server Management studio. Here is a snippet of the policy management.

Friday, August 13, 2010

SSRS - Drill Down...

Usually while developing reports, I have the requirement to develop a summary and detail report. Based on the requirements I create the detail reports which are opened by the Javascript windows.open function. The reason why my users like this is because the the detail report appears in a separate window and if they have dual monitors they can lay the reports side by side. Recently I ran into an issue with this approach, the problem was the length of the URL string exceeded 260 characters. There are parameters which are passed to the detail report from the summary report, the URL string is built by concatenating the parameter values. The root cause was that the parameter values and labels were string values, this caused the http query string to exceed the limit of 260 characters. In order to resolve this I had two options:
1. Look at the tables feeding the parameter values, make sure there is a ID,description column. This would enable me to pass the ID's across to the detail for the parameter values.
2. The second option is to use the native SSRS drilldown to sub report option. The drawback of this approach is that the detail report would layover the top of the summary report. The user would have to click on the Back arrow in the browser to go to the summary report.

Wednesday, August 11, 2010

Loops Vs Set Based Approach...

Recently I had a requirement where given a range of dates, I had to perform the counts for each date within the date range. Initially I had started off approaching the problem by using a While Loop construct and running the select statement for each date in the date range. The solution worked but the performance was bad, the user had the choice of giving the date range, so the performance would get a hit if the date range is very wide. After researching for a while I came across a query in ORACLE which used the CONNECT and LEVEL clauses to handle the problem which i had. This was a set based approach with no looping involved and apparently performed very well on oracle. I decided to use this query as a starting point and created a CTE to be populated with the range of dates within the date range. I use the following query to create the CTE:

WITH t_dates(rptday) AS  
(SELECT CONVERT(VARCHAR(10),@ldt_failstartdate,121)

FROM t_dates
WHERE rptday < CONVERT(VARCHAR(10),@ldt_failenddate,121)
Once this was done, immedietly followed it up with the main SELECT statement which calculated the counts. The performance of this setup was much better than the Looping construct. This was an instance were I was able to replace the looping construct with a SET Based solution to achieve better results.

Tuesday, August 3, 2010

SSRS-Backgorund Colors-Part Two

Alternating background colors in a report is a request which comes very often as part of report development requirements. Alternate row colors can be get very tricky as one moves into matrix type reports, also when one starts to have multiple groups. Recently i had a problem in a report trying to alternate colors which had multiple groups. My report had  two adjacent groups, one of the groups in them had a child group. I had to alternate the background colors of the rows across the groups in the report. I started using the groupvar solution which i had posted earlier but ran into issues since there were multiple issues. Lisa Slater an SSRS expert know for her very detailed method of presenting innovative solutions help me out. Here is a link to her blog which has the definition of my problem and the solution.
Thank you Lisa for taking the time to analyse the problem and present a solution.