Saturday, March 21, 2009

RunningValue Function (Reporting Services) – Calculating Running Totals

In one of my previous posts I talked about Calculating Running Totals in T-SQL.  Now I will show you how you can calculate the running totals in Sql Server Reporting Services(SSRS).  Yes, it is very much possible with RunningValue function in SSRS and also very easy compare to doing it in SQL.  

The syntax for RunningValue function goes like this -

RunningValue(expression, function, scope)
  • expression : The expression on which to perform the aggregation, for example, [Quantity].
  • function : The name of the aggregate function to apply to the expression, for example- Sum.
  • scope :The name of a dataset, data region, group or Nothing.

See the table below and RunningTotal column which is Cumulative SUM of Quantity column. 

ShipId OrderId ShipDate Quantity RunningTotal
1 1 2009-02-01 12:06:16.820 10 10
2 1 2009-02-02 16:16:16.820 15 25
3 2 2009-02-01 13:26:16.820 20 45
4 2 2009-02-03 17:40:16.820 12 57
5 2 2009-02-04 13:05:16.820 15 72
6 3 2009-02-03 14:25:16.820 25 97
7 3 2009-02-04 15:50:16.820 50 147
8 3 2009-02-05 19:40:16.820 30 177
9 4 2009-02-06 15:30:16.820 20 197
10 4 2009-02-07 16:20:16.820 10 207
11 5 2009-02-05 17:05:16.820 35 242

For achieving that in SSRS, add extra column in your Report’s Layout Page and put following expression in it-

=RunningValue(Fields!Quantity.Value, Sum, Nothing)

Yes, it is easy and simple, isn’t it?  But what about when you want the RunningTotals for group of records?  Like for above example you want the Running Totals for each orderId separately.  See the table below :

ShipId OrderId ShipDate Quantity RunningTotal
1 1 2009-02-01 12:06:16.820 10 10
2 1 2009-02-02 16:16:16.820 15 25
3 2 2009-02-01 13:26:16.820 20 20
4 2 2009-02-03 17:40:16.820 12 32
5 2 2009-02-04 13:05:16.820 15 47
6 3 2009-02-03 14:25:16.820 25 25
7 3 2009-02-04 15:50:16.820 50 75
8 3 2009-02-05 19:40:16.820 30 105
9 4 2009-02-06 15:30:16.820 20 20
10 4 2009-02-07 16:20:16.820 10 30
11 5 2009-02-05 17:05:16.820 35 35

Now for achieving that you need do followings :

  • First go to Report Layout page and add a new Group.   Give some name to the group.  If you don’t gave any name the default name would be something like “table1_Group1”.  Just note that.  And add OrderId to “Group On” expression.
  • Now change the expression at RunningTotal column as
    =RunningValue(Fields!Quantity.Value,SUM,"table1_Group1")

        Now if you have noticed I have replaced the scope Nothing to Group name “table1_Group1” .

Well after adding the extra group in the report you might see blank spaces after every  OrderId change. If you don’t want those blanks and want continuous report, then go to the property page of the new group added and set Visible to “false”.

Mangal Pardeshi

SQL MVP

Posted by Unknown at Saturday, March 21, 2009 16 comments
Labels:

Monday, March 16, 2009

Script All the Stored Procedures in The Database

In Sql Server 2005 and 2008 you can script the stored procedure in Management Studio by right clicking on Store Procedure name and clicking on “Script Store Procedure as” and then “Create To”.

But if you want to script all the Stored Procedures in the database programmatically, then here is the simple T-SQL query for it -

To script All the Stored Procedures in the Database :

SELECT    O.Name as ProcName
        ,M.Definition as CreateScript
        ,O.Create_Date
        ,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.objects as O
ON M.object_id = O.object_id
WHERE O.type = 'P'

If the Stored Procedure is created with ENCRYPTION option then you will get the NULL in the definition column.

Similarly,

To script All the Views in the Database :

SELECT    O.Name as ProcName
        ,M.Definition as CreateScript
        ,O.Create_Date
        ,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.objects as O
ON M.object_id = O.object_id
WHERE O.type = 'V'

To script All the Functions in the Database :

SELECT    O.Name as ProcName
        ,M.Definition as CreateScript
        ,O.Create_Date
        ,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.objects as O
ON M.object_id = O.object_id
WHERE O.type = 'FN'

For scripting all Triggers small modification is required, instead of sys.objects I joined the sys.triggers with sys.sql_modules.

To script All the Triggers in the Database :

SELECT    O.Name as ProcName
        ,M.Definition as CreateScript
        ,O.Create_Date
        ,O.Modify_Date
FROM sys.sql_modules as M INNER JOIN sys.triggers as O
ON M.object_id = O.object_id

Mangal Pardeshi
SQL MVP

Posted by Unknown at Monday, March 16, 2009 4 comments
Labels: ,

Tuesday, March 3, 2009

How To Split A Comma Delimited String

In one of my previous posts I wrote about “How to Create a Comma Delimited List”.  Now I’ll show you an example with reverse action, that is Splitting a Comma Delimited String or array() of values.

There can be different types of Delimiters also : like Comma ' , ', vertical bar ' | ', Single space or a Tab.

For example here is our Sample Table -

Id AllNames
1 A,B,C
2 A,B
3 X,Y,Z

And here is the expected output -

Id Names
1 A
1 B
1 C
2 A
2 B
3 X
3 Y
3 Z

Create Sample Data :

-- Create Table for  Sample Data
CREATE TABLE Test
(
ID INT,
AllNames VARCHAR(100)
)
GO
-- Load Sample Data
INSERT INTO test SELECT
1, 'A,B,C' UNION ALL SELECT
2, 'A,B'  UNION ALL SELECT
3, 'X,Y,Z'
GO

-- Verify the Sample Data
SELECT Id, AllNames
FROM Test

And here is the query for How to split a comma delimited string :

;WITH Cte AS

(
    SELECT
        id,
        CAST('<M>' + REPLACE( Allnames,  ',' , '</M><M>') + '</M>' AS XML) AS Names
    FROM Test
)
SELECT
    ID,
    Split.a.value('.', 'VARCHAR(100)') AS Names
FROM Cte
CROSS APPLY Names.nodes('/M') Split(a)

 

Acknowledgement : Well seriously don’t know.  Somewhere I came across this solution while answering / participating  on MSDN Sql Server Forums.  So credit goes to my fellow Moderators/answrers on MSDN Forums.

Actually whatever expertise I gained on T-SQL and Sql Server is by participating / answering to the posts on online forums.  I’m one of those freaks who hate to read books ;)

- Mangal Pardeshi

Posted by Unknown at Tuesday, March 03, 2009 22 comments
Labels: ,

Monday, February 23, 2009

Create A Comma Delimited List From a Column

In this article I will try to show you a simple example on how to Create a Comma Delimited List from a column also called as CSV (Comma Separated Values).

For example here is our Sample Table -

Id Names
1 A
1 B
1 C
2 A
2 B
3 X
3 Y
3 Z

And here is the expected output -

Id AllNames
1 A, B, C
2 A, B
3 X, Y, Z

Though in above example I have used Comma, sometimes it may required to use other delimiters as well, like Single space (A B C) or Vertical bar (A | B | C) or may be some other delimiter.

Create Sample Data -

--Create sample table
CREATE TABLE Test
(
Id INT,
Names VARCHAR(100)
)
GO
-- Load sample data
INSERT INTO Test SELECT
1,'A' UNION ALL SELECT
1,'B' UNION ALL SELECT
1,'C' UNION ALL SELECT
2,'A' UNION ALL SELECT
2,'B' UNION ALL SELECT
3,'X' UNION ALL SELECT
3,'Y' UNION ALL SELECT
3,'Z'
GO

SQL Server 2005 / SQL Server 2008 Solution for Comma delimiter.
1st Solution -

SELECT T1.Id 
           ,AllNames = SubString (( SELECT ', ' + T2.Names
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id

If the column “Names” doesn’t contain any spaces in between its values then here is another solution -

2nd solution -

SELECT T1.Id
           ,AllNames = REPLACE (( SELECT T2.Names AS 'data()'
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), ' ', ', ')
FROM Test as T1
GROUP BY Id

Advantage with 2nd solution is that if the data type of column whose value you want to concatenate is INT or any other noncharacter data type you don’t need to CONVERT it into varchar data type. In 1st solution you will need to convert the column to Varchar if its original data type is numeric. But 2nd solution will fail in case there are some in-between spaces in column values.

Now if you want to create the list using other delimiter, replace comma from above query with your required delimiter.

E.g. for Vertical Bar

SELECT T1.Id 
           ,AllNames = SubString (( SELECT '| ' + T2.Names
FROM Test as T2
WHERE T1.Id = T2.Id
FOR XML PATH ( '' ) ), 3, 1000)
FROM Test as T1
GROUP BY Id

Mangal Pardeshi

SQL MVP

Posted by Unknown at Monday, February 23, 2009 9 comments
Labels: ,

Friday, February 20, 2009

SQL Server Versions and Editions.

Lets see how to find which Sql Server Version is installed and Service Packs applied on your machine-

To know Execute

SELECT @@version

If you are on Sql Server 2000 or later use following which will give you a more elegant output -

SELECT SERVERPROPERTY('productversion') as VersioNumber
,SERVERPROPERTY ('productlevel') as SPLevel
,SERVERPROPERTY ('edition') as ServerEdition

And here is detailed chart which will show you SQL Server version numbers and the corresponding product or service pack level -

Sr NoVersion NumberReleaseSP Level
10.110.0.1600.22SQL Server 2008RTM
----
9.19.0.1399.00SQL Server 2005RTM
9.29.0.2047.00SQL Server 2005SP1
9.39.0.3042.00SQL Server 2005SP2
9.49.0.4035.00SQL Server 2005SP3
----
8.18.00.194.00SQL Server 2000RTM
8.28.00.384.00SQL Server 2000SP1
8.38.00.534.00SQL Server 2000SP2
8.48.00.760.00SQL Server 2000SP3
8.58.00.2039.00SQL Server 2000SP4
----
7.17.00.623.00SQL Server 7.0RTM
7.27.00.699.00SQL Server 7.0SP1
7.37.00.842.00SQL Server 7.0SP2
7.47.00.961.00SQL Server 7.0SP3
7.57.00.1063.00SQL Server 7.0SP4
----
6.516.50.201.00SQL Server 6.5RTM
6.526.50.213.00SQL Server 6.5SP1
6.536.50.240.00SQL Server 6.5SP2
6.546.50.258.00SQL Server 6.5SP3
6.556.50.281.00SQL Server 6.5SP4
6.566.50.415.00SQL Server 6.5SP5
6.576.50.416.00SQL Server 6.5SP5a
6.586.50.479.00SQL Server 6.5SP5a update
----
6.016.00.121.00SQL Server 6.0RTM
6.026.00.124.00SQL Server 6.0SP1
6.036.00.139.00SQL Server 6.0SP2
6.046.00.151.00SQL Server 6.0SP3

- Mangal Pardeshi

Posted by Unknown at Friday, February 20, 2009 1 comments
Labels: , ,
Subscribe to: Posts (Atom)