Convert rows into comma separated column using single query with XML

Convert rows into comma separated column using single query with XML

Have you ever try to covert list of rows into an comma separated column? There are many solution available based on the coalesce but I found an interesting fact about the XML Path by which we  can easily achieve the same functionality without much of hassle.

In the below example I have created a query to extract table and column names from the information_schema  for two test tables

SELECT table_name, 
       column_name 
FROM   information_schema.columns 
WHERE  table_name IN ( 'DimProductCategory', 'DimCurrency' ) 

 

output

Now if we just want to display two rows for each table and concatenate the columns into single value with a delimiter then we can simply achieve this by below query.

SELECT Distinct col2.table_name, 
       Stuff((SELECT ',' + column_name
              -- Stuff used here only to strip the first character which is comma (,). 
              FROM   information_schema.columns col1 
              WHERE  col1.table_name = col2.table_name 
              FOR xml path ('')), 1, 1, '') 
FROM   information_schema.columns col2 
WHERE  table_name IN ( 'DimProductCategory', 'DimCurrency' ) 

Further Learning

Stuff : http://msdn.microsoft.com/en-us/library/ms188043.aspx 

FOR XML PATH : http://blog.sqlauthority.com/2009/02/12/sql-server-simple-example-of-creating-xml-file-using-t-sql/

SSRS lookup and lookup set – Similar to Excel

On the other day I came across a interesting functionality in the SSRS. They are Lookup and Lookup set functions , these functions are very useful for multi source reports
Simple Abstract :
Lookup : Just like the Excel Vlookup function which can get the data from another dataset
Lookupset: Same like lookup but returns all the matching results and will be very useful for displaying all the matching results, This can be converted to comma separated form using Join function.
Check the Below Blog for details
http://www.bidn.com/blogs/DustinRyan/bidn-blog/2037/lookup-and-lookupset-functions-new-in-ssrs-2008-r2
 

Technorati Tags: Excel vLookup in SSRS

Import Active Directory (AD) Groups and user names via SSIS into SQL server table

Before getting into the technical details, I will explain the need for this.

1. If you manage SQL server Security via AD Groups due to high volume of business users then you would have already hit the problem and looking for solution to import the mapping details of groups vs users
2. If you are using SSRS suit for the reporting needs and if you have large user base then AD group based security is the best way to segregate user access. But once again to answer who has what rights you need this mapping information.

Step 1: Create the table structure necessary to import the SSIS Groups and Names in two tables

Table 1: ActiveDirectoryUserGroups – This tables gives you the relationship between users and their group name they belongs to

CREATE TABLE [dbo].[ActiveDirectoryUserGroups](

[id] [int] IDENTITY(1,1) NOT NULL,

[GroupName] [varchar](1000) NULL,

[username] [varchar](1000) NULL

)  

GO

Table 2:ActiveDirectoryGroupRelation – This table gives you the relationship between Groups

CREATE TABLE [dbo].[ActiveDirectoryGroupRelation](

[id] [int] IDENTITY(1,1) NOT NULL,

[GroupName] [varchar](1000) NULL,

[ParentGroupName] [varchar](1000) NULL

) 

GO

 

Step2: Create the necessary connection to your database where the above table exist

Step 3: Create SQL task to clear the data before load.

(Alternatively, you can import the changes alone via lookup or in other words use the incremental load approach . But that is not the point of this blog so to keep this simple I will just truncate the records before load)

Step 4: Create a data flow task in the control flow to fetch mapping between groups

(Name it suitably  E.g “DFL_ActiveDirectory_Group_relation_load”)

Create Three Variables in the data flow task scope

Note: Replace Domain with your mail Domain. E.g. “Pinnacle-Int” and “Extension” with your Domain extension E.g. “Com”

Create a “Source Script Component”. as shown below.

Make sure to declare all three variables as read only variables as shown below

Choose your choice of scripting language

 

Set the outputs correctly

Paste the below code in the Scripter

Public Overrides Sub CreateNewOutputRows()

        Dim de As New DirectoryEntry

        Dim searcher As New DirectorySearcher

        Dim search_result As SearchResultCollection

        Dim result As SearchResult

        Dim props As ResultPropertyCollection

        Dim MemberOfList As StringBuilder

        Dim values As ResultPropertyValueCollection

        Dim name As String

        Dim groups As ArrayList

        Using (de)

            de.Path = Me.ReadOnlyVariables("Path").Value.ToString

            Using (searcher)

                searcher.SearchRoot = de

                searcher.Filter = Me.ReadOnlyVariables("Filter").Value.ToString

                searcher.SearchScope = SearchScope.Subtree

                ' Retrive ActiveDirectory column list

                searcher.PropertiesToLoad.Add("samaccountname")

                searcher.PropertiesToLoad.Add("memberof")

                'sort the result

                searcher.Sort = New SortOption("samaccountname", SortDirection.Ascending)

                ' you can set your own limits

                searcher.PageSize = Me.ReadOnlyVariables("MaxRecord").Value

                'Retrieve the results from Active Directory

                search_result = searcher.FindAll()

                MemberOfList = New StringBuilder 'Members/Groups list.

                Dim entry As DictionaryEntry

                For Each result In search_result

                    props = result.Properties

                    For Each entry In props

                        values = entry.Value

                        If entry.Key.ToString = "samaccountname" Then

                            name = GetSingleValue(values)

                        End If

                        If entry.Key.ToString = "memberof" Then

                            groups = GetGroups(values)

                            For Each group In groups

                                ListofusersoutputBuffer.AddRow()

                                ListofusersoutputBuffer.Name = name

                                ListofusersoutputBuffer.MemberOf = group

                            Next

                        End If

                    Next

                Next

            End Using

        End Using

    End Sub

    Private Function GetSingleValue(ByVal values As ResultPropertyValueCollection) As String

        For Each val As Object In values

            Return val.ToString()

        Next

        Return Nothing

    End Function

    Private Function GetGroups(ByVal values As ResultPropertyValueCollection) As ArrayList

        Dim valueList As New ArrayList()

        For Each val As Object In values

            Dim memberof As String = val.ToString()

            Dim pairs() As String = memberof.Split(",")

            Dim group() As String = pairs(0).Split("=")

            valueList.Add(group(1))

        Next

        Return valueList

    End Function

Connect the Appropriate Destination as shown below

Do the Appropriate mappings for ActiveDirectoryGroupRelation

Test whether the load is successful

Step 5: Create a data flow task in the control flow to fetch mapping between user and groups

(name it suitably E.g “DFL_ActiveDirectory_user_group_relation_load”)

Follow the same similar steps as above. Only difference is load them with below Variables.

Create Three Variables in the data flow task scope

Note: Replace Domain with your mail Domain name. E.g. “Pinnacle-Int”  and “Extension” with your Domain extension E.g. “Com”

Also make sure to load “ActiveDirectoryUserGroups” table this time

Your Final solution should look like below

Hope that is helpful!

Naming Conventions for Report objects, Data Sources, and Datasets

Use naming conventions for data sources and datasets that document the source of data.

1. Data sources. If you do not want to use an actual server or database due to security reasons, use an alias that indicates to the user what the source of data is.
   1. Start with DS and followed by databasename(Avoid server name as this will change during the deployment) (e.g. DS_AdventureWorks)
2. Datasets. Use a name that indicates which data source it is based on.
   1. Start with DT and followed by datasorurce name and followed by meaningful name about the dataset (e.g. DT_DS_AdventureWorks_Customerinfo)

All the Report items should be named with three letter abbreviation(as shown below) followed by a meaningful name
E.g. the name of a Tablix which shows the policy summary will be “TBXPolicySummary”

Report Item	Three letter Abbreviation
Text Box	TXT
Line	LNE
Table	TBL
Matrix	MTX
Rectangle	REC
List	LST
Image	IMG
SubReport	SRE
Chart	CHT
Gauge	GGE
Map	MAP
DataBar	DTB
Sparkline	SLN
Indicatior	IND

Best Practice for SSRS report development

I have put together a list of best practices for SSRS development based on my experience. Please feel free to add your thoughts
1. Always use the common template to promote a uniform reporting Experience
2. Always convert the complex queries into views and select from the view to produce a result in the report. This will help with your maintenance
· E.g. Any query with more than two Joins will be considered as complex query
3. Don’t use “Select *” in the report query
4. If you require multi stage result building or if you are using T-SQL then convert them into Procedures and call the procedure from SSRS
5. Optimise the report Query: Always follow the best practice whilst writing the SQL code. The performance of the query will directly impact the SSRS report performance so tune the query before using it in the reporting
6. Introduce only the necessary parameters as more than 6 parameters will give a poor user experience
7. Assign the default value to most of the parameters by discussing with users
· Remember if you are assign default parameters to all the parameters then the report will get auto loaded when the user select the report. If the execution time is greater than 1 minute then please go for “null” destination snapshot to cache the report.
8. If the user chooses “All” for multi select parameters then don’t send the parameter list, instead send “All” to the SQL and handle it. This will give a great boost to the performance
i. E.g if we have parameter called Branch and if the user chooses “ALL” then in the SQL use where (Branch in (@branch) or 'All' in (@branch))
9. Don’t retrieve more than what is needed. If the report delivers more than 5-6 pages (or more than 300-400 rows) of data then check with user to deliver them via E-Mail during the offline hours.



10. Use all the space
· Profile the data to understand the maximum length required. As a rule of thumb wrap the text and don’t wrap the numbers and dates
· Don’t assign more space than is required for columns
11. Before starting the report set the report width property to one of the standard paper sizes. This will give you an idea of the maximum size you can get (E.g. The width of A3 is 42 cm in Landscape mode)

12. Once developed export the report into required formats and make sure the users are happy with the extract format
· If the users want to export the report into PDF then make sure the report width fits in a page
· Use the Logical page breaks if needed for rending into different pages (Ex. Pushing each year of account into separate page)
13. If the report looks very lengthy in the report viewer then use soft page breaks via theInteractiveHeight and InteractiveWidth properties.
14. Move the Calculated fields into views. If there are calculated fields in the report then it is better to create them in a view as this will promote two things:-
· Ease of Maintenance
· Less processing load on the report server



15. Use the right data type for the parameters
E.g. Use the DateTime data type for date parameters instead of using strings. There are 3 reasons to do this:
1) Stop the bug "Cannot read the next data row for the data set"
Although a hardcoded string will work, it will not work for all users regional date/time settings.
E.g. a string data type parameter with a value of "26/01/2006" is correct for "dd/mm/yyyy", but it is wrong for "mm/dd/yyyy"
2) When SQL Reporting Services is using the DateTime data type parameter, it will get the datetime value on the user’s setting (aka the Culture DateTime format).
3) The users also get the advantage of a date/time picker control, which automatically works out the correct regional date format. This solves the US/Australian date problem. (i.e. DD and MM are reversed).
16. If your data is getting refreshed via ELT/overnight jobs then please include an additional textbox and label in the header to show the last refreshed date/time.