Convert Rows to Column

Using the below query we can convert the rows to column sperated by a delimiter.
In the query I am using ';' as the delimiter and you can change the delimiter of your choice by replacing ';'.
Data from Table:

Query:

Use AdventureWorks2008R2
GO
DECLARE @eMailList nvarchar(max) 

SELECT @eMailList = COALESCE(@eMailList + ';', '') + 
   CAST(eMail AS nvarchar(max))
FROM Employees

Select @eMailList as eMailList

Output of the above Query:

Multiple Cube vs Single Cube Approach

Shifting my focus to SSAS, so here is the post on whether to go for Multiple cube or Single cube.Well, if you ask me what's my opinion then I will say wait for the post to complete. 

First of all, let me put all the relevant information and then we will be in a good position to have a call on this.

Let me start briefing about the general terms that I will be using in this post like Database, Cube, Measures, Dimensions etc.

Database is the collection of data, in SSAS it can be collection of cube data.

Cube also termed as OLAP cube, is a data structure that helps you analyze & visualize your data in multiple dimensional way. It is set of related measures (or facts) and dimensions.

Measures are the numeric facts (or data).

Dimensions are the attributes, for which Measures are categorized.

So, what exactly is Single/Multiple cubes?
If your database contain only one cube, then you have a single cube wherein all the users refer to the same cube.
And, if your database contains more than one cube, each cube for specific users then its a part of multiple cube.




Single Cube Approach - Pros & Cons

• Users can easily fetch data from multiple measure groups even if they not related. Performance won't be degraded in cross quering measure groups.
• Security Management is tough here, for example, we want certain users to have the access to certain measure groups and not all. So, restrictions won't be easy.
• Less user-friendly, as every measure is present in a single location so navigating through long list of measures is a tough ask.
• Complexity is bound to increase with everything loaded into one.
• Change Management, any change made, how small it may be will result in whole cube structure change, that may effect users who are not associated with that change.

Multiple Cube Approach - Pros & Cons

• Performance can be a worry if user performs cross-querying based on multiple cubes.

Just to elaborate more on that, MDX provides a function to cross-querying 2 cubes using LookUpCube() function.

• Security Management is relatively easy, as you have cube for each specific usage (only certain users will be there). So, we can apply security restrictions on each cube.
• User-friendly, limited number of measures groups and that too related to each other.
• Less complex structure.
• Change Management is relatively simpler, changes will be applied to specific cube so others users won't be affected by that.

These were some of the differences that can help you plan your approach according to your requirement.

Now, my call on this will be:

Initially, go for the One cube approach,add unrelated dimensions and measures to a certain extent.

But, once you get the feeling that beyond this point, adding more things can create confusion and complexity, immediately move to Multiple cube approach

Ranking our Reports

One of the interesting requirement both as a user and developer, is to have rankings worked out in your report.Everyone either in school exams, sports etc. get really excited to know how they fare and what's their rank.

Likewise, as we are human to have that nature instilled as an employee too, we want to know how we perform in our organization.

In this post, we will see how you can create a report ranking for your employees too.

Though, we got a function in MDX called Rank(), its not that easy to get it done.

General syntax for Rank,

Rank(Tuple_Expression, Set_Expression [ ,Numeric Expression ] )

Some theory of this function,

- Rank function determines the one-based rank for the specified tuple

by evaluating the specified numeric expression against the tuple. If a 

numeric expression is specified, the Rank function assigns the same 

rank to tuples with duplicate values in the set. This assignment of the 

same rank to duplicate values affects the ranks of subsequent tuples 

in the set. 

Note: I will take an example from Adventure Works, to illustrate the 

same. 

We have a Customer dimension containing all the customers and 

Internet Order Count values.

Now, how can we assign a rank based on the Internet Order count 

values to each of our customers. We will see that in a moment,

Steps:

•  First of all, we will create an Ordered Set, that will order the customers based on the measure, in this case, Internet Order Count. Though, an ordered set is not required and a simple set will work too.

• Then, we will use our rank function on this set.

Here it is, we have ranked our customers based on the order count.

SSRS Calendar and Date Restriction

Interactive reports are always in demand, the ease it gives our users for usage. One of the important component to any report will be the Date Parameter/Filter, to get insight of historical data as well as current data.

Adding a calendar will solve the purpose.

SSRS provides a data type known as Date/Time for this purpose.

Now, that's simple we just use the Date/Time and got the Calendar. So, life's simple :) not for developers you know.

One of the requirements we usually get is to restrict the calendar, what we mean by this - user should be allowed to select only selected dates for which we got the data.

How to achieve that? Till now I haven't came across perfect solution for that.

Note: Though you can assign a default value to a Date parameter, you can't do that for available values.

Available Values section should be assigned - "No Default value", otherwise you will lose the Calendar.

Default values can be anything like

= Today    = Now     =DateAdd("d",-1,Today)

So, how to achieve the restriction of Dates?

We can use Custom code for that. Custom code will be a function that will validate the dates selected by the user.

Code

Function CheckDateParameters(d1 as Date, d2 as Date)

Dim msg as String

msg = ""

If (d1 <DateAdd("d",-200,Today ) ) Then                                         'If From Date is less than 200 days

msg="Please select 'From Date' again, it should not be before than " 

& DateAdd("d", -200, Today ) & "." & vbCrLf & msg

End If

If (d2 >DateAdd("d",0,Today ) ) Then   

'If To Date is greater than Current date

msg="Please select 'To Date' again, it should not be later than " 

& DateAdd("d", 0, Today ) & "." & vbCrLf & msg

End If

If (d1 > d2 ) Then                                                                        'If From Date selected is greater than To Date

msg="From Date should not be greater than To Date" & vbCrLf & msg

End If

Return msg

End Function

Create a textbox in your report and use the expression:

=Code.CheckDateParameters(Parameters!FromDate.Value,Parameters!ToDate.Value)

So, whenever the conditions mentioned in your Code breached by the user a message with show up in your report.

DrillDown Charts

Drilling down usually refers to navigating a hierarchy of information. It helps in encapsulating or wrapping the data details into levels.

User can expand or collapse the data present in the form of hierarchy according to their usage.

We have seen this thing in our reports in the form of Tabulated data.

SSRS provides us the feature in the form of Toggle that can be used in our tablix to have this drill-down hierarchy.

That was easy, i mean we have the control available to toggle the visibility of table data.

How about doing the same to charts?

Can we also do the same in our charts in the way. I am sorry, we don't have any Toggle kind of thing available for charts.

So, what's the catch! If you can play with expressions and parameters then you can achieve that.

I tried on achieving the same and came out with a workaround (I will say it a workaround J ) 

Lets start then,

Categories used in my example: Year, Month, Date
Values used in my example: Amount

• Create a parameter Level 
• Set the visibility to Hide.
• Assign Default value to 0
• Assign Available values to 0, 1, 2

 Note: Parameter Level will be used for conditionally checking the hierarchy shown in the chart.

•  Create a chart, add a category (I used Year for instance) and a Value (used Amount)
• I want to show Year --> Month --> Date drill-down structure in my chart so I have used the fields accordingly.
• In the Category group, I have used the expression, both on the Group On and Label, as

= Switch ( Parameters!Level.Value = 0, Fields!Year.Value,

Parameters!Level.Value = 1, Fields!Month.Value,

Parameters!Level.Value = 2, Fields!Date.Value

) 

• Then, another important aspect that needs to be added is action on the Bars, so as to drill-down furthur.
• Add an Action to the Bars, using Go To Report feature.
• Select the same report, add a parameter Level with value

= Switch ( Parameters!Level.Value = 0, 1,

Parameters!Level.Value = 1,2,

Parameters!Level.Value = 2,0

)

• Now, the basic structure is done, you can add images and manipulate visual labels etc. based on the requirement.

So, here's the report:

 

 

Same can be done to other chart types as well, like the Pie chart.

Textbox aligment problem in report

I was creating a list report. The report was looking fine with all format in PDF, but in SSRS preview pane and Report manager, format was really bad. All the textboxes were showing apart from each other. I tried with all alignment but it was failing. After a massive research, finally this issue got fixed.

·         So resolve such type of issue, check out with the following options, I can hope this will work fine

• Arrange all the rectangles that are to be displayed  in one rectangle
• Check the position of the textboxes in Address row.  There should be some gap between Address and its value textbox. 
• Are you using two different Textboxes for Address and Address Values and same for others values? Instead use Address and AddressValue in single Textbox, then the alignment would be better. ="Address : " & Fields!AddressValue.Value
• Arrange all Header textboxes in one rectangle and add another rectangle. Now add rectangle which have textboxes in newly added rectangle, under that rectangle add the tablix control.

T-SQL Query to find the SQL Server Protocols Status

Here is a T-SQL Query that I have developed to get the status of the SQL Server Protocols.
This query reads data from the registry.

DECLARE @InstanceName nvarchar(50)

DECLARE @value VARCHAR(100)

DECLARE @value_Out VARCHAR(100)

DECLARE @RegKey_InstanceName nvarchar(500)

DECLARE @RegKey nvarchar(500)

SET @InstanceName=CONVERT(nVARCHAR,isnull(SERVERPROPERTY('INSTANCENAME'),
'MSSQLSERVER'))

CREATE TABLE #SQLServerProtocols

(ProtocolName nvarchar(25),

Value nvarchar(10),

Data bit)

if(SELECT Convert(varchar(1),(SERVERPROPERTY('ProductVersion'))))<>8

BEGIN

SET @RegKey_InstanceName='SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL'

EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = @RegKey_InstanceName,

  @value_name = @InstanceName,

  @value = @value OUTPUT

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Sm'

Insert into #SQLServerProtocols (Value,Data)

EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = @RegKey,

  @value_name = 'Enabled'

EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = @RegKey,

  @value_name = 'DisplayName',

  @value = @value_Out OUTPUT

UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Np'

Insert into #SQLServerProtocols (Value,Data)

EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = @RegKey,

  @value_name = 'Enabled'

EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = @RegKey,

  @value_name = 'DisplayName',

  @value = @value_Out OUTPUT

UPDATE #SQLServerProtocols set ProtocolName=@value_Out
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\TCP'

Insert into #SQLServerProtocols (Value,Data)

EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = @RegKey,

  @value_name = 'Enabled'

EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = @RegKey,

  @value_name = 'DisplayName',

  @value = @value_Out OUTPUT

UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null

SET @RegKey='SOFTWARE\Microsoft\Microsoft SQL Server\'+@value+'\MSSQLServer\SuperSocketNetLib\Via'

Insert into #SQLServerProtocols (Value,Data)

EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = @RegKey,

  @value_name = 'Enabled'

EXECUTE xp_regread

  @rootkey = 'HKEY_LOCAL_MACHINE',

  @key = @RegKey,

  @value_name = 'DisplayName',

  @value = @value_Out OUTPUT

UPDATE #SQLServerProtocols set ProtocolName=@value_Out 
where ProtocolName is null

END

SELECT ProtocolName, IsEnabled=CASE WHEN Data=1 THEN 'Enabled' 
ELSE 'Disabled' END FROM #SQLServerProtocols

DROP TABLE #SQLServerProtocols