SSRS : Multivalued Parameters

I am creating a simple SSRS report to demonstrate the workarounds available to pass multivalue parameter to Stored Procedures. Stored procedures are capable of receiving scalar values as input for its parameters, and it cannot receive a row set as an input parameter. We don’t have any explicit functionality for passing multivalue parameters to a stored procedure in SSRS, and it has to be achieved only using workarounds

Following is the sample table, through which i have created the sample report in SSRS,

DimProduct

it contains only few fields such as ID, Code, Name and Size. Following is the SSRS report that is created with the sample table

report

this report has been created with one parameter as shown below,

parameters

the parameter displays name [product’s name] and take values as codes [product codes].

selection

The procedure is created as follows

alter Procedure GetProducts
(
@Product nvarchar(50)
)
as
select code, Name, Size
from Dimproduct
where Code in (@Product)

Most of us will first try to pass that mutlivalued parameter to the stored procedure using IN operator and it will not result any data. Why IN operator doesn’t work as expected ?

I further checked the SQL Profiler to see how SSRS gets data using the Stored Procedure, and found it has been executing the procedure as

exec GetProducts @Product=N'C001,D001'

This clearly show that the report is concatenating the two selected products into one, to make it as scalar value. So, a procedure can receive only scalar value and since we don’t have any values in table that matches with the concatenated string, procedure doesn’t return any value

To make it more clear, the concatenated string value [‘C001,D001’] is not same as passing values in IN operator [in (‘C001′,’D001’)]. They are completely different.

This post may not look like new to you all, because you all can search and find many similar posts in many blogs, forum etc. And most of us know this can be resolved using a user defined split function in stored procedure which will split the values from the concatenated string and can provide individual values/row set for further operations.

Please refer, https://rdineshkumar.wordpress.com/2012/09/06/split-comma-separated-values/

I am trying to pass on some of the other techniques that i have learnt to solve this, this particular problem can also be solved without using a user defined function. Let’s discuss those methods

Using Charindex function:

We can make use several other inbuilt functions such as Charindex() to resolve these type of issues, and below is the modified procedure that uses charindex() to check for the matches in the tables

alter Procedure GetProducts
(
@Product nvarchar(50)
)
as
select code, Name, Size
from Dimproduct
where charindex(code,@product)>0

the most important thing required for this method to work is the uniqueness of the code. Instead of sending the product names i have chosen the product codes as they are unique. Most of the cases all codes will be unique as they are the business keys and i have always inclined towards it.

what will happen if pass product names to check with Charindex function ? take a look at our sample table it contains names with characters that gets repeated in others

for ex:

Let’s say, we are selecting ABC as the input parameter.

so when using charindex() function, the input value ABC can be found in ABC and as well as in other product names that starts with ABC [ABCD, ABCDE, ABCDEF]. This could result into erroneous solutions. So always prefer to use this method only when you have a unique fields in the table

Following query would also be useful, if you don’t like creating an additional object [UDF for split functionality]. Try to replicate the operations in Stored procedures that really happens in UDFs

alter Procedure GetProducts
(
@Product nvarchar(50)
)
as
Declare @ProductList table (ProductID nvarchar(max))

while LEN(@Product)>0
begin
if CHARINDEX(',',@Product,1)>0
begin
insert into @ProductList
select SUBSTRING(@Product,1,CHARINDEX(',',@Product,1)-1)
set @Product=SUBSTRING(@Product,CHARINDEX(',',@Product,1)+1,LEN(@Product))
end
else
begin
insert into @ProductList
select @Product
break
end
end

select code, Name, Size
from Dimproduct prd
join @ProductList lst on prd.Code = lst.ProductID

This would ideally produce the same results as expected, i will try to post some other methods if i get any. Please feel free to comment if you have any suggestions and queries. Thank u all.

Happy Reporting 😉

Advertisements

T-SQL : Gaps and Islands Problem – 2

There are lot of ways to find the gaps in a sequential numbers, and some of them are listed below. I have posted some 3 methods to achieve the expected. The goal is to find the gaps between the sequential number. The query should return the Gap start point and Gap end point. Please check these queries and any suggestions/comments are always welcomed.

declare @table table (num int)
insert into @table
select 1 union all
select 2 union all
select 3 union all
select 6 union all
select 8 union all
select 11 union all
select 12 union all
select 13 union all
select 15 union all
select 17 union all
select 28

--Query 1
select GapStart,GapEnd from
( select m.num+ 1 as GapStart
,(select min(num) - 1 from @table as x where x.num > m.num) as GapEnd
from @table as m left outer join @table as r on m.num = r.num- 1 where r.num is null
) as x where GapEnd is not null 

--Query 2 
select a.num1 GapStart,b.num2 GapEnd from 
(select row_number()over(order by num) ID,num,num+1 num1 ,num-1 num2 from @table)a,
(select row_number()over(order by num) ID,num,num+1 num1 ,num-1 num2 from @table)b
where a.ID+1 = b.ID
and (a.num1<b.num2 or a.num1 = b.num2) 

--Query 3 
select GapStart, GapEnd from 
(select m.num,m.num+1 as GapStart,ROW_NUMBER() over (order by m.num+1) as ser
from @table m
left join (select num-1 as rowno from @table) mm
on m.num=mm.RowNo
where mm.RowNo is null and
m.num+1<(select MAX(num) from @table ))x
join 
(select m.num,m.num-1 as GapEnd,ROW_NUMBER() over (order by m.num-1) as ser from @table m
left join (select num+1 as rowno from @table) mm
on m.num=mm.RowNo
where mm.RowNo is null and
m.num-1>(select MIN(num) from @table ))y
on x.ser=y.ser

Output:
Capture

Happy Querying 😉

T-SQL : Delete duplicates in a table using Query

Following are the queries that will delete all available duplicates in a table, please refer the query below

Declare @Duplicates table (Name nvarchar(50), Age int, BookingID int)

insert into @Duplicates
select 'Dinesh',26,145 union all
select 'Latheesh',26,142 union all
select 'Sathish',26,144 union all
select 'Sathish',26,144 union all
select 'Sathish',26,144 union all
select 'Sathish',26,154 union all
select 'Dinesh',26,145 union all
select 'Dinesh',26,145  

delete del
from
(select
row_number() over(partition by Name, Age, BookingID order by Name)row_id,*
from @Duplicates) del
where del.row_id >1

select * from @Duplicates

Before deletion:

blog1

Output:

blog2

Alternatively, the same operation can be done using a CTE as shown below

Declare @Duplicates table (Name nvarchar(50), Age int, BookingID int)

insert into @Duplicates
select 'Dinesh',26,145 union all
select 'Latheesh',26,142 union all
select 'Sathish',26,144 union all
select 'Sathish',26,144 union all
select 'Sathish',26,144 union all
select 'Sathish',26,154 union all
select 'Dinesh',26,145 union all
select 'Dinesh',26,145  

;With del
as
(select
row_number() over(partition by Name, Age, BookingID order by Name)row_id,*
from @Duplicates) 

delete from del
where del.row_id >1

select * from @Duplicates

T-SQL : Query to fetch report subscription details from SSRS

In SSRS, to find who has created/modified the report subscription without using the management site, the following query can be used,

select
reps.Name as ReportName
,usr.UserName createdby
from Subscriptions subs
join Catalog reps on subs.Report_OID = reps.ItemID
join Users usr on subs.OwnerID = usr.UserID​

This is will help in finding who has created the subscriptions in report server DB, rather trying by each users in report manager. When it would be useful ? take for instance, if your customer wants some of the email IDs to be removed in the existing subscriptions. Unless we have the information that who has created the existing subscriptions, the email ids cannot be edited.

Happy Querying 😉

Categories: T-SQL

T-SQL : Get all folder names from SSRS Reports Manager/Server

We all know, we can query the report server database and get very useful information, such as reports details, directory and schedules etc. This particular thread belongs to one such category, this query will provide you all available folders from a report server


select
ltrim(rtrim(Name))
from [dbo].[Catalog]
where type =1
and ParentID is not null

I will soon post other queries that will provide much more useful properties/information from report server.

Happy Querying 😉

T-SQL : Get count of all tables in a database

The following query will list the count of records in all tables of a database. These queries are based on CURSORS,

Query 1:
Query to get individual results,

DECLARE @SQL        VARCHAR( max ),
        @TableName  VARCHAR (255 ),
        @SchemaName VARCHAR (50 ) = 'dbo'
DECLARE TableCount CURSOR FOR
  SELECT table_name
  FROM   INFORMATION_SCHEMA .tables
  WHERE  table_schema = @SchemaName
OPEN TableCount
FETCH next FROM TableCount INTO @TableName
WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'select ''' + @TableName
                 + ''' as TableName,count(*) [Count] from '
                 + @SchemaName + '.' + @TableName
      PRINT @SQL
      FETCH next FROM TableCount INTO @TableName
      EXEC (@SQL )
  END
CLOSE TableCount
DEALLOCATE TableCount

Query 2:
Results stored in singe temp table,

DECLARE @SQL        VARCHAR( max ),
        @TableName  VARCHAR (255 ),
        @SchemaName VARCHAR (50 ) = 'dbo'

CREATE TABLE #TableCount
  (
     TableName  VARCHAR (255 ),
     TableCount INT
  )

DECLARE TableCount CURSOR FOR
  SELECT table_name
  FROM   INFORMATION_SCHEMA .tables
  WHERE  table_schema = @SchemaName

OPEN TableCount
FETCH next FROM TableCount INTO @TableName
WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'insert into #TableCount
      select ''' + @TableName
                 + ''' as TableName,count(*) [Count] from '
                 + @SchemaName + '.' + @TableName
      PRINT @SQL
      FETCH next FROM TableCount INTO @TableName
      EXEC (@SQL )
  END
CLOSE TableCount
DEALLOCATE TableCount
SELECT *
FROM    #tableCount
DROP TABLE #tableCount

Happy Querying 😉

%d bloggers like this: