Today I was working on an SSRS report for one of our clients and came across a specific formatting requirement around formatting Minutes as HH:MM. Browsed a little bit but could not get it working. Then sat for a while & tried few tricks by playing around with the expressions and finally got it working :-)

Here is how I solved the problem.

The data available in the database is numeric and represents Total Minutes. When I initially wrote some expressions to derive Hours and Minutes from the Total Minutes I was able to get the data something like this:

10 Minutes -> 0:10

65 Minutes -> 1:5 etc

However the need was to display the data something like below:

10 Minutes -> 00:10

65 Minutes -> 01:05 etc

Now to get this formatting, first split the Minutes into Hours & Remaining Minutes as follows:

Hours part of TotalMinutes = Floor(Fields!Minutes.Value/60)

Remaining Minutes part of TotalMinutes = Fields!Minutes.Value Mod 60

Now to display the data in the intended format put the following expression in the textbox of the detailed row of the Table/Matrix of SSRS:

=Format(Floor(Fields!Minutes.Value/60),"00") + ":" + Format((Fields!Minutes.Value Mod 60),"00")

Below is a sample report with formatted minutes.

 

Hope you will find this useful. If yes feel free to leave a comment below.

Note:  This article/demonstration is based on SSRS 2008. The options should be almost the same in SSRS 2005 as well.

Reports are one of the most important components in any BI projects. SQL Server Reporting Services (SSRS) is one of the various reporting tools available in the market. SSRS comes bundled with SQL Server and is free when you buy SQL Server License.

One of the most common need in any reporting tools is to be able to export/save the report in a different format. Reporting Services offers various exporting options like Excel, PDF etc. Often times few reports are suitable for exporting to a particular format and does not fit perfectly in other formats. Example: A huge report with some 50 fields is most suitable to be exported to Excel and will not fit into other formats like PDF/Word etc.

Following are the formats supported by SQL Server 2008 Reporting Services:

• XML file with report data
• CSV (comma delimited)
• Acrobat (PDF) file
• MHTML (web archive)
• Excel
• TIFF file
• Word

Often business users like formats like Excel, PDF etc and do not want the other exporting formats to be available/displayed in the pull down menu for choosing an exporting format.

We can control the list of exporting options available for the users using the "rsreportserver.config" file. Here is how we do it.

Let us say we need to remove the "Word" option from the dropdown for exporting option.

Initially all the exporting options would be available in the Report Manager as shown below.

 

Now go to the SQL Server/SSRS Installation directory and locate "rsreportserver.config" file. In my case it is located in the path: "D:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer".

Open the "rsreportserver.config" file with notepad and locate "<render></render>" tag. This tag contains sub tags called as "<extension></extension>" for various exporting options. The "<extension></extension>" tag for exporting to Word for example is as follows:

<Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering"/> 

Now comment this statement/tag as follows:

<!-- <Extension Name="WORD" Type="Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering"/> -->

Now refresh the Report in the Report Manager/Server using the browser refresh button and see that the option for exporting to Word disappears from the list as shown below.

 

Now the business can be relaxed to see that they only have limited set of exporting options which they are interested in. This will make the presentation better and the users happy :-)

Note: This demo is based on SQL Server 2008 Reporting Services. In other versions of SQL Server the options might slightly vary.

This is just for fun :-)

Here is the universal law of code "A code can neither be created not be destroyed, it can only b copied from one application and pasted into another" :-)

We often use Remote Desktop Connection (RDC) for connecting to Development/Test/UAT Servers for various activities like Development, Testing, Deployments etc. People also refer to this in short as RDC/RDP/TS (Terminal Services Connection) etc.Today I was approached by one of my colleagues who wanted to adjust the window size of the Remote Desktop Connection. At first even I did not know how to adjust the window size, but after spending a couple minutes I got it working :-)

Here is how we can connect to remote desktops/servers and also can adjust the window size of Remote Desktop Connections. 

• Click on Start -> Run
• Type "mstsc" and click "OK". This will open the Remote Desktop Connection dialog box.

• Click on "Options>>" in the right bottom corner of this dialog box.

• Click on the "Display" tab.

• In the Display tab adjust the pointer towards "Less" to reduce the size of the RDC window and towards "More" to increase the size of the RDC window. Keeping it at "More" will create open a Full Screen window so that the user can exclusively work in the remote desktop/server and can navigate between the windows in the RDC connection using "ALT + TAB".
• Click on "Connect" and enter the credentials and hit Enter to connect to the remote desktop/server.

Now enjoy a dedicated Full Screen Window/Console for your Remote Desktop Connection.

SQL Server Reporting Services (SSRS) is a great BI tool offering lot of powerful features. SSRS 2008 has lot more features and is much more powerful than SSRS 2005. Irrespective of whether its SSRS 2005 or SSRS 2008, there are many features common between the two versions. Functions are one such powerful options/capabilities in SSRS.

In this article I will present detailed steps for the use of Functions in SSRS.

Often in reporting/BI projects using SSRS we use lot of calculations/expressions in many fields in the reports. There are scenarios where in same formula/calculations is used across many fields in the report. Functions come in handy especially in this kind of scenarios.

Let us take a look at this sample report with simple calculations for finding Sum, Difference, Product & Percentage.

Create an SSRS report with the following query in the dataset:

SELECT 1 AS ColumnA, 2 AS ColumnB
UNION ALL
SELECT 3 AS ColumnA, 4 AS ColumnB
UNION ALL
SELECT 5 AS ColumnA, 6 AS ColumnB
UNION ALL
SELECT 7 AS ColumnA, 8 AS ColumnB
UNION ALL
SELECT 9 AS ColumnA, 10 AS ColumnB
UNION ALL
SELECT 11 AS ColumnA, 12 AS ColumnB
UNION ALL
SELECT 13 AS ColumnA, 14 AS ColumnB
UNION ALL
SELECT 15 AS ColumnA, 16 AS ColumnB
UNION ALL
SELECT 17 AS ColumnA, 18 AS ColumnB
UNION ALL
SELECT 19 AS ColumnA, 20 AS ColumnB

Now lets drop a Table object on to the report designer with the following six fields as shown in the below screenshot.

• Column A
• Column B
• Sum of A & B
• Difference of A & B
• A Multiplied by B
• A as a % of B
  

Now go to "Report > Report Properties". Report Properties dialog box will open and in this window click on "Code" in the left pane. Enter the following code with four different functions for calculating Addition, Difference, Product & Percentage in the Code window as shown in the below screenshot.

' Function for Addition
Function GetSum(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarSum AS Integer 
VarSum = A + B
Return VarSum
End Function

' Function for Difference
Function GetDiff(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarDiff AS Integer 
IF (A>=B) THEN
VarDiff = A - B
ELSE
VarDiff = B - A
END IF
Return VarDiff
End Function

' Function for Multiplication/Product
Function GetProduct(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarProduct AS Integer 
VarProduct = A * B
Return VarProduct
End Function

' Function for Percentage
Function GetPercent(ByVal A AS Integer, ByVal B AS Integer) As Integer
Dim VarPercent AS Integer 
VarPercent = (A/B)*100
Return VarPercent
End Function

Enter the following expressions in the detailed row of the report for the six fields as shown in the below screenshot.

Expr1: "=Fields!ColumnA.Value"
Expr2: "=Fields!ColumnB.Value"
Expr3: "=Code.GetSum(Fields!ColumnA.Value,Fields!ColumnB.Value)"
Expr4: "=Code.GetDiff(Fields!ColumnA.Value,Fields!ColumnB.Value)"
Expr5: "=Code.GetProduct(Fields!ColumnA.Value,Fields!ColumnB.Value)"
Expr6: "=CStr(Code.GetPercent(Fields!ColumnA.Value,Fields!ColumnB.Value)) + " %"" 

Now go to the Preview tab and check out the results as shown in the below screenshot.

And the results are amazing. Having functions for scenarios where the same calculation is used in many places in a report is really helpful and makes the code more modular & neat.

Note: This has been tested & presented as per SSRS 2008.

If you find this post helpful then feel free to leave a comment below :-)

Many times we will be working on server by doing a Remote Desktop Connection/Terminal Services Connection. Several times there is a need for opening multiple instances of the same application with a different UserID.

To open multiple instances of the same application we can use RUNAS Command.

Syntax:

C:\>runas /profile/user:Domain\UserID "Fully Qualified App/Executable Path"

Example:

Here is an example for opening SQL Server Management Studio.

• Type the following command on Command Prompt and Hit Enter.

C:\>runas /profile /user:Learning\Datta "D:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

• You will be prompted to enter the password for the account "Learning\Datta". Enter the password and Hit Enter.
• This opens up SQL Server Management Studio with the account "Learning\Datta".
  

Note: Please note that when you enter the password the letters or equivalent number of astericks will not be visible to you.

This example illustrates a simple usage of the RUNAS Command. RUNAS command offers options of specifying various other parameters which might be useful at times.