July 17, 2012

Multilevel Cascading With Select All - Issue Resolved

The long-standing issue with cascading parameters in SSRS is that when changing the selection of the “parent” parameter, the default selection of the dependent parameter is not always automatically changed.

After a considerable amount of R&D and considering the report cache, I have found a solution/workaround which not only solves the issue at hand, but also addresses up to 4 levels of cascading.

To address this issue, you need to write a query in your dataset in a manner so that the dependent parameter changes its value every time you change its parent parameter.

I used Northwind database (datasource) for testing.

The steps are as follows:
1. Open Report Builder.
2. Create data source.
3. Create datasets.
4. Create parameters.
5. Assign parameter values to datasets.

Here is the screenshot how it will look like:

Data Source

      1.       Right click on the data source folder on the left hand side pane.
      2.       Click on Add Data Source.
      3.       The following screen will pop up :


Dataset 1: Country

FROM Northwind.dbo.Customers

Dataset 2: City

SELECT l11.City1+'_'+ CAST(row_number() over( order by  l11.city1 desc) AS VARCHAR(50) )as city11
 SELECT l1.City+'_'+ CAST(row_number() over( order by  l1.City asc) AS VARCHAR(50) )as city1
                FROM (
                SELECT DISTINCT City
                                FROM Northwind.dbo.Customers
                                WHERE Country IN (@Country)

Dataset 3: Company Name

SELECT l11.CompanyName1+'_'+ CAST(row_number() over( order by  l11.CompanyName1 desc) AS VARCHAR(50) )as CompanyName11
 SELECT l1.CompanyName+'_'+ CAST(row_number() over( order by  l1.CompanyName asc) AS VARCHAR(50) )as CompanyName1
                FROM (
                                SELECT DISTINCT CompanyName
                                FROM Northwind.dbo.Customers
                                WHERE City IN(@City)
ORDER BY l11.CompanyName ASC

Dataset 4: Contact Title

SELECT l11.ContactTitle1+'_'+ CAST(row_number() over( order by  l11.ContactTitle desc) AS VARCHAR(50) )as ContactTitle11
SELECT l1.ContactTitle+'_'+ CAST(row_number() over( order by  l1.ContactTitle asc) AS VARCHAR(50) )as ContactTitle1
                FROM (
                                SELECT DISTINCT ContactTitle
                                FROM Northwind.dbo.Customers
                                WHERE CompanyName IN(@CompanyName)

ORDER BY l11.ContactTitle ASC

Dataset 5: Contact Name

SELECT l11.ContactName1+'_'+ CAST(row_number() over( order by  l11.ContactName desc) AS VARCHAR(50) )as ContactName11
SELECT l1.ContactName+'_'+ CAST(row_number() over( order by  l1.ContactName asc) AS VARCHAR(50) )as ContactName1
                FROM (
                                SELECT DISTINCT ContactName
                                FROM Northwind.dbo.Customers
                                WHERE ContactTitle IN(@ContactTitle)
ORDER BY l11.ContactName ASC


Parameter 1: Country

     1.       Right click on the Parameters folder on the left hand side pane.
     2.       Click on Add Parameter.
     3.       The following screen will pop up , click on General on left side

     4.       Now click on Available values

Parameter 2: City

      1.       Right click on the Parameters folder on the left hand side pane.
      2.       Click on Add Parameter.
      3.       The following screen will pop up, click on General on left side:

      4.       Now click on Available Values

5.       Now click on Default values

Parameter 3: CompanyName

      1.       Right click on the Parameters folder on the left hand side pane.
      2.       Click on Add Parameter.
      3.       The following screen will pop up, click on General on left side

4.       Now click on Available Values

5.       Now click on Default values

Parameter 5: ContactName

      1.       Right click on the Parameters folder on the left hand side pane.
      2.       Click on Add Parameter.
      3.       The following screen will pop up :

4.       Now click on Available Values

      5.       Now click on Default values

Now assign parameter values to datasets

For this steps are:
      1.       Double click dataset “CompanyName”.
      2.       The following screen will pop up :

      3.       Click Okay to both windows.
      4.       Now double click dataset “ContactTitle”.
      5.       The following screen will pop up :

 6.       Click Okay to both windows.
     7.       Now double click dataset “ContactName”.
 8.       The following screen will pop up :

9.       Click Okay to both windows.
      10.    Now run the report by pressing F5 function key from keyboard.

        I hope you like it. :)

June 7, 2012

Partitioning Database Table


Partitioning makes large tables or indexes more manageable, because partitioning enables you to manage and access subsets of data quickly and efficiently, while maintaining the integrity of a data collection.

The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. The table or index is treated as a single logical entity when queries or updates are performed on the data. All partitions of a single index or table must reside in the same database.

Partitioned tables and indexes support all the properties and features associated with designing and querying standard tables and indexes, including constraints, defaults, identity and timestamp values, and triggers. Therefore, if you want to implement a partitioned view that is local to one server, you might want to implement a partitioned table instead.

Deciding whether to implement partitioning depends primarily on how large your table is or how large it will become, how it is being used, and how well it is performing against user queries and maintenance operations.
  • Generally, a large table might be appropriate for partitioning if both of the following are true:
  • The table contains, or is expected to contain, lots of data that are used in different ways.
Queries or updates against the table are not performing as intended, or maintenance costs exceed predefined maintenance periods.

For example, if a current month of data is primarily used for INSERT, UPDATE, DELETE, and MERGE operations while previous months are used primarily for SELECT queries, managing this table may be easier if it is partitioned by month. This benefit can be especially true if regular maintenance operations on the table only have to target a subset of the data. If the table is not partitioned, these operations can consume lots of resources on an entire data set. With partitioning, maintenance operations, such as index rebuilds and defragmentations, can be performed on a single month of write-only data, for example, while the read-only data is still available for online access.

When to use:

You should partition your tables that has accumulated a lot of data over the time and you find that the performance is slowing down. It would not be a very good idea to partition a table it has  just a couple of hundred rows for example : Product Category table. You could partition your tables that contains a lot of transactions like Purchase Order tables, Sales Order tables, General Ledger Tables etc.

Steps to create Partitioning:
Step 1:
Open the SQL Server 2008 management studio and select the appropriate table that needs to be partitioned. Right click on the table and in the context menu select Storage >> Create Partition.

Step 2:

I am using the [Purchasing.WorkOrder] table in the 'AdventureWorks' sample database. When you select the above option a wizard dialog box appears, press the next button once.

June 6, 2012

Service Broker Solution


Service Broker was introduced in SQL Server 2005 to provide a reliable, scalable, and asynchronous message queuing system for local or distributed applications. Service Broker solutions can range from a simple application existing ion a single database to a complex application reaching across remote SQL Server instance.
The Service Broker is a new platform for building asynchronous – distributed database applications. In this article I will give a brief overview of the definitions of the most important aspects in the Service Broker platform, and give a sample application that leverages some of its features.

Service Broker Provides

With the new Service Broker platform, internal or external SQL Server instances can send and receive guaranteed, asynchronous messages by using Transact T-SQL. Messages can be sent from within the same database, different database, or even remotely located SQL Server instances.

Service Broker Working

The Service Broker communicates with a newly developed protocol called the Dialog, which allows for bi-directional communication between two endpoints. The Dialog protocol specifies the logical steps required for a reliable conversation, and makes sure that messages are received in the order they were sent. The following diagram below from Microsoft SQL Server 2005 books online, illustrates how the Dialog protocol is being utilized in the Service Broker platform:

The following is another overview diagram of the Service Broker, it shows the main entities of the Service Broker such as a Message, Contract and Service which will be explained further in the next section. However let's look at the dialog conversation part which is a high level protocol used by the Service Broker to make sure messages are received in order and with no loss. The dialog conversation protocol as we see is not a transportation protocol as it is an application protocol to maintain the message Exactly-Once-In-Order (EOID). The EOID, is the concept of ordering messages no matter what order they were received in from the transportation channel.
The Service End Points as we see below are the communication end points between two Service Brokers. They are the ones who support the transportation protocols such as TCP/IP, HTTP, SOAP etc…
The end points are created on each Service Broker, to allow for remote communication between different SQL Server instances. There will be further explanation for each of the following in the next section.

Enabling Service Broker

Check if service broker is enabled :
SELECT is_broker_enabled FROM sys.databases WHERE name = 'MyDatabaseName'

Enable service broker :

June 4, 2012

Date & Time Format Conversion

Some date and time conversion functions are:

SELECT convert(varchar, getdate(), 100) – mon dd yyyy hh:mmAM (or PM)
                                        – Oct  2 2008 11:01AM          
SELECT convert(varchar, getdate(), 101) – mm/dd/yyyy 10/02/2008                  
SELECT convert(varchar, getdate(), 102) – yyyy.mm.dd – 2008.10.02           
SELECT convert(varchar, getdate(), 103) – dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) – dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) – dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) – dd mon yyyy
SELECT convert(varchar, getdate(), 107) – mon dd, yyyy
SELECT convert(varchar, getdate(), 108) – hh:mm:ss
SELECT convert(varchar, getdate(), 109) – mon dd yyyy hh:mm:ss:mmmAM (or PM)
                                        – Oct  2 2008 11:02:44:013AM   
SELECT convert(varchar, getdate(), 110) – mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) – yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) – yyyymmdd

SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YYYY’)           – 01/03/2012
SELECT dbo.fnFormatDate (getdate(), ‘DD/MM/YYYY’)           – 03/01/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/DD/YYYY’)            – 1/03/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YYYY’)             – 1/3/2012
SELECT dbo.fnFormatDate (getdate(), ‘M/D/YY’)               – 1/3/12
SELECT dbo.fnFormatDate (getdate(), ‘MM/DD/YY’)             – 01/03/12
SELECT dbo.fnFormatDate (getdate(), ‘MON DD, YYYY’)         – JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘Mon DD, YYYY’)         – Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘Month DD, YYYY’)       – January 03, 2012
SELECT dbo.fnFormatDate (getdate(), ‘YYYY/MM/DD’)           – 2012/01/03
SELECT dbo.fnFormatDate (getdate(), ‘YYYYMMDD’)             – 20120103
SELECT dbo.fnFormatDate (getdate(), ‘YYYY-MM-DD’)           – 2012-01-03

SELECT CURRENT_TIMESTAMP                        – 2012-01-05 07:02:10.577
– SQL Server DATEADD function
SELECT DATEADD(month,2,’2012-12-09′)            – 2013-02-09 00:00:00.000
– SQL Server DATEDIFF function
SELECT DATEDIFF(day,’2012-12-09′,’2013-02-09′)  – 62
– SQL Server DATENAME function
SELECT DATENAME(month,   ’2012-12-09′)          – December
SELECT DATENAME(weekday, ’2012-12-09′)          – Sunday
– SQL Server DATEPART function
SELECT DATEPART(month, ’2012-12-09′)            – 12
– SQL Server DAY function
SELECT DAY(’2012-12-09′)                        – 9
– SQL Server GETDATE function
– local NYC – EST – Eastern Standard Time zone
SELECT GETDATE()                                – 2012-01-05 07:02:10.577
– SQL Server GETUTCDATE function
– London – Greenwich Mean Time
SELECT GETUTCDATE()                             – 2012-01-05 12:02:10.577
– SQL Server MONTH function
SELECT MONTH(’2012-12-09′)                      – 12
– SQL Server YEAR function
SELECT YEAR(’2012-12-09′)                       – 2012

Download complete file