Multi Select – Browsing Problem. SQL Server Reporting Service

Bug in Reporting Service

In parameters option for Multi Select is available from SQL 2005 Reporting Service.

If we choose Multi Select for the parameters then while browsing the report the width of that parameters remain fixed to default length even though value of parameters are long.
So viewing data from drop down becomes difficult and we have to scroll horizontally in drop down box to view data of parameters

I think its just a small requirement why microsoft has ignored it . Its really become a problem for many of my clients. We cant use multi select option freely in the reports because of its fixed length behaviour.

I hope Microsoft will change it in next versions.

Except Function – MDX Analysis Service 2005

SQL Server Analysis Service 2005
Except in MDX

Usage:-
Except (Set1, Set2, ALL)

When we supply two sets this function evaluates first set and removes tuples which are in the second
set.

If we specify ALL then the function will retain duplicates from the first set but duplicates from second set will be removed.

Example

SELECT [Measures].[Reseller Sales Amount] ON 0 ,
[Geography].[Country].MEMBERS ON 1
FROM [Adventure Works];

This will return all country with ALL Member “All Geographies”.
Now we want to remove this “All Geographies”

We will use Except here.

SELECT [Measures].[Reseller Sales Amount] ON 0 ,
EXCEPT([Geography].[Country].MEMBERS,[Geography].[Country].[All Geographies]) ON 1
FROM [Adventure Works]
;

This will remove “All Geographies” and display all countries.

User Defined Table Types :- SQL Server 2008

We sometimes need to use table variable in our query.
Every time in the query we have to define the table variable.

Example

declare @test table
( id int,
data varchar(10))

select * from @test

We can make it easy now!
What happened if we can create user defined type of table which has definition of our table variable .
Its now possible in SQL Server 2008

We will create table type named as [test]

create type [test] as table
(id int,
data varchar(10))

Now we can use it in our declare statement easily. We dont need to define table variable every time now.

declare @test as test
select * from @test

Points to take care while using table type

1.Default values are not allowed
2.Primary key must be a persisted column.
3.Check constraint can not be done on non persisted computed columns
4.Non clustered indexes are not allowed.
5.It can’t be altered. You have to drop and recreate it.

Functions deprected after SQL Server 2008

Deprecated Option in SQL Server 2008

Some features are deprecated from next version of SQL Server 2008, here are some of the commons used functions which are deprecated and their replacements.
Look at BOL for more info

sp_dboption
This feature will not be supported in next version of SQL Server. Use Alter Database instead of this.

*= and =*
These are non Ansi syntax for table joins , use regular Ansi Sytax like left join or right join.

Databaseproperty
Databaseproperty will be replaced by DatabasepropertyEx

Compability levels :- Compability levels 80 and 90 , levels from last 2 versions only will be supported.

SET ROWCOUNT for INSERT, UPDATE, and DELETE statements
Use TOP (n) for instead of ROWCOUNT for this statements.

Compute /Compute By :- Use ROLLUP instead of this functions.

Grouping Sets- Cube and Rollup

Last article we show about grouping sets.

As we know that grouping set is union of different result sets generated by group by clause. Here in group by clause we can use also cube and rollup functions.

Example
create table #t
(id int,
code varchar(10),
department varchar(10),
val int)

insert into #t values (1,’a’,’d1′,10)
insert into #t values (1,’a’,’d1′, 10)
insert into #t values (1,’b’,’d2′,20)
insert into #t values (1,’b’,’d2′,20)
insert into #t values (2,’c’,’d3′,30)
insert into #t values (2,’c’,’d3′,30)
insert into #t values (2,’d’,’d4′,40)
insert into #t values (2,’d’,’d4′,40)

Here we want rollup of code and department
and also we want grouping of ID and union of this two resultset.

select id,code,department,SUM(val) sumofval from #t
group by GROUPING sets (id,rollup ( code,department))

id code department sumofval
NULL a d1 20
NULL a NULL 20
NULL b d2 40
NULL b NULL 40
NULL c d3 60
NULL c NULL 60
NULL d d4 80
NULL d NULL 80
NULL NULL NULL 200
1 NULL NULL 60
2 NULL NULL 140

Here we want cube of code and department
and also we want grouping of ID and union of this two resultset.

select id,code,department,SUM(val) sumofval from #t
group by GROUPING sets (id,cube ( code,department))

id code department sumofval
NULL a d1 20
NULL NULL d1 20
NULL b d2 40
NULL NULL d2 40
NULL c d3 60
NULL NULL d3 60
NULL d d4 80
NULL NULL d4 80
NULL NULL NULL 200
NULL a NULL 20
NULL b NULL 40
NULL c NULL 60
NULL d NULL 80
1 NULL NULL 60
2 NULL NULL 140