Format date and time on SSRS report

We could format the date and time in SSRS in simple way.

1. Right on the field in which column you want to format

2. Type  “Format(Fields!DOCMODIFIED.Value,"dd/MM/yyyy")” in the expression box and Click ok

Below is a list of various format codes available in SSRS and some examples

Format Code	Description	Example
d	Single Digit Date Format	9
dd	Double Digit Date Format	09
ddd or dddd	Full Day Name	Wednesday
M	Single Digit Month Format	2
MM	Double Digit Month Format	02
MMM	Three Character Month Name	Feb
MMMM	Full Month Name	February
yy	Double Digit Year Format	11
yyyy	Four Digit Year Format	2011
H	Single Digit “24 Hour” Hour Format	6
HH	Double Digit “24 Hour” Hour Format	06
hh	Double Digit “12 Hour” Hour Format	06
m	Single Digit Minute Format	9
mm	Double Digit Minute Format	09
s	Single Digit Second Format	4
ss	Double Digit Second Format	04
tt	ante meridian (AM)/post meridian (PM) part of Time	PM
zzz	Time Zone Offset w.r.t. GMT	+05:30

=Format(Fields!DOCMODIFIED.Value,"dd/MM/yyyy") -> 03/08/2015

=Format(Fields!DOCMODIFIED.Value,"dd/MMM/yyyy") ->03/Aug/2015

=Format(Fields!DOCMODIFIED.Value,"dd/MMMM/yyyy") -> 03/August/2015

=Format(Fields!DOCMODIFIED.Value,"dd-MMM-yyyy") -> 03-Aug-2015

=Format(Fields!DOCMODIFIED.Value,"dd-MMM-yyyy HH:mm") -> 03-Aug-2015 13:28

=Format(Fields!DOCMODIFIED.Value,"dd-MMM-yyyy HH:mm tt") -> 03-Aug-2015 13:28 PM

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  
“Grow more trees to save the Earth”

Dynamic Connection String for SSIS

The SQL Server Native Client provider is SQLNCLI* in an OLE DB Connection String.

·         2005 uses SQLNCLI.1

·         2008 uses SQLNCLI10

·         2012 uses SQLNCLI11

As of this posting, there is not a SQLNCLI12 included with the CTP2 release of SQL Server 2014.

These providers can generally talk to previous versions of SQL Server but version forwardness is less probable (with the exception of SQLNCLI11 communicating with 2014).

Root cause

You have an OLE DB Connection string using a provider that does not exist on that machine. Perhaps you built the package in a 2005 format but are running it on a 2008 instance. At any rate, your connection strings in the format of

Provider=SQLNCLI.1;Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;

For Oracle

Provider=MSDAORA;Data Source=;User ID=;Password=;Max Pool Size=50;Min Pool Size=10

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  
“Grow more trees to save the Earth”

SSRS Page Break After X Number of Records

In SQL Server Reporting Services you can add a page break after a desired amount of records.

Step 1: A simple design to show all rows in a table using table component.

Step 2: Add a group to the detail row

Step 3: Add group expression “=Ceiling((RowNumber(Nothing)) / Nth row)” and Click OK

Step 4: You may get the following error if you run the report after step 3.

Step 5: Open Row group properties  and remove sort expression.

Step 6:  Now, report will show 30 rows in a group. Next step is to define page break for the group. Go to page breaks tab and select page break option

Step 7: Now, hide the group header and detail column from the table component and run the report.

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  
“Grow more trees to save the Earth”

Attach DB without log file(.ldf) in SQL Server

The following article is to create the Database into SQL Server instance without .ldf file. 
There are 2 files required to create database in SQL Server instance

1 .mdf is the primary database file where all table ,view,indexes , SP etc.. stored.

2 .ldf is the transaction log file where all transaction logs are stored since last backup.

we may end up situation where we have only .mdf file to attach. in this case we can create .ldf when we creating the primary database file (.mdf) into the database.

the following query will create the .mdf file and create .ldf file in the database.

CREATE DATABASE <DB Name>

ON (FILENAME = 'Physical path of .mdf file')

FOR ATTACH_REBUILD_LOG  

I hope this article will be very helpful to all. Thanks for reading this article.

“Keep reading and share the knowledge”  

“Grow more trees to save the Earth”