March 14, 2016

SSRS: The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '162'. The expected version is '147'.

My laptop has 2 SQL Server instances namely SQLServer2008R2 and SQLServer2012. Today I messed up 2008 R2 reporting services when I was trying to configure 2008 R2 Reporting Services using SQLServer2012 Reporting Services Configuration Manager because for some reason 2008R2 Reporting Services Configuration Manager was denying to connect with the 2008R2 reporting server.

When I was trying to open report server through browser it was throwing an error "The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is '162'. The expected version is '147'.". It made me really frustrated. On ReportServer DB, I found a table named [ReportServer].dbo.[ServerUpgradeHistory]. It contains information about the Server Upgrade. I updated 162 to 147 and it worked well.



UPDATE tbl
SET tbl.ServerVersion = '162'
FROM [ReportServer].dbo.[ServerUpgradeHistory] tbl
WHERE UpgradeID = 3

February 16, 2016

Return Date Part from DATETIME - SQL Server

Most of the time we need date part only from datetime format in SQL Server. 

1) Old method
 SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))



2) On SQL Server 2008 and higher
SELECT CONVERT(DATE, GETDATE()) 



Above two methods helps to keep original date format and returns date part only from datetime.

3) If you want in specific date format (US date format)
SELECT CONVERT(CHAR,GETDATE(),101)


February 1, 2016

SSRS Default Date Parameters

Default date value in SSRS will help to provide the date/time range (Start and End Date) as needed. This will help to reduce the report execution time, it also helps end users because they don't need to provide date range every time they run the report.

For Example:

1)Beginning of current month
=DateSerial(Year(Date.Now), Month(Date.Now), 1)

2)End of current month
=DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, DateSerial(Year(Date.Now), Month(Date.Now), 1)))

3) Beginning of  last month

=DateAdd(DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

4) End of last month
=DateAdd(DateInterval.DAY, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

5) End of last month 11 PM
=DateAdd(DateInterval.Hour, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1))

Steps:
1) Double click on the date parameters.

2) Click on "Default Values". Select "Specify values" and click on Add Button and then function (fx)


3) Then, provide the formula and click OK.




October 14, 2014

SSRS report header and footer get cut off when exported to word format

Problem: I had an experience of header and footer cut off while printing the exported SSRS report to word format.

According to Riccardo Muti from Microsoft  "The margins in the word document do match those in the report at 2 cm or 0.79 in. However, Word and SSRS treat page headers and footers differently. Word actually positions them inside the page margins, whereas SSRS positions them inside the area that the margins surround. As a result, in Word, the page margins do not control the distance between the top edge of the page and that of the page header (or similarly for the page footer). Instead, Word has separate "Header from Top" and "Footer from Bottom" properties to control those distances. Since RDL does not have equivalent properties, the Word renderer sets these properties to zero."

Reference:
http://connect.microsoft.com/SQLServer/feedbackdetail/view/614558/word-export-sets-margin-top-margin-bottom-to-0mm

I have the solution so far is before printing the report from Word, we have to increase the size of header and footer. This will help to print the report from word format without header and footer cut off.

September 4, 2014

SSRS Alternate Row Color

This post will help to set alternate row background color in SSRS tablix report using an expression.

1) Select the row from tablix then go to properties (Press F4) and then go to expression of background color property.


2) Use this expression:
                                    = IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")

Then, click OK.


Here, RowNumber(Nothing) will work in each row to give row number in sequential order.

(RowNumber(Nothing) Mod 2 means RowNumber(Nothing) will be divided by 0 and it should give remainder as 0 or 1. Then, if condition is used to give the color for its corresponding result.

3) Then, run the report to see the output. You should get something like this.

Hope, this will help you.

August 25, 2014

How to import data into SQL Server using TSQL

There are features such as Import/Export and SQL Server Integration services to get the data into SQL Server. We can get the data directly into SQL Server using SQL command such as
BCP, BULK INSERT, OPENROWSET, OPENDATASOURCE, OPENQUERY and LINKED SERVERS. 

August 20, 2014

SQL - Case Statement

Case statement is used as conditional operator in Structure Query Language which compares the expression and returns the possible results. Case statement can also be used to evaluate a set of Boolean expression. Most often Case statement is useful in stored procedures, views and formula for a particular column while designing the table.

Syntax:

CASE expression
         WHEN expression1 THEN expression1
         WHEN expression2 THEN expression2
          .....
ELSE expressionN
END

--------------------------------------------------------------------------------------------------------------------------

For example:

DECLARE @Val INT = 1

SELECT
CASE @Val
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
ELSE 'Not Known'
END

Result: First
--------------------------------------------------------------------------------------------------------------------------

DECLARE @Val INT  = 5

SELECT
CASE
WHEN @Val < 0 THEN 'Negative'
WHEN @Val < 3 THEN 'Less than 3'
WHEN @Val > 3 THEN 'Greater than 3'
ELSE 'Value not assigned'
END

Result: Greater than 3

--------------------------------------------------------------------------------------------------------------------------

Formula for a particular column while designing the table.

Below, you can see on the formula
(case [RoleID] when (1) then '100' when (2) then '75' when (3) then '50' else '0' end)


Result:



When Role is assigned to the Sales man then it will automatically set the value on the SalesTarget column based on the RoleID assigned.