SSRS - The report definition has an invalid target namespace Error

This error normally occurred when you created a report on one SQL version and deploying/opening on another SQL version.

I've created my SSRS report on SQL 2016 and I'm trying to open it on SQL 2014 Report Builder. I'm getting below error.

The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition' which cannot be upgraded.

The solution of this problem is, just open the report in XML format and changed the following lines.

<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition">

to 

<Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns:cl="http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition">

 Just change 2016 version as shown above and remove below lines


Search "ReportParametersLayout" in the code and remove the whole block. This code is only for SQL 2016.

 <ReportParametersLayout>

    <GridLayoutDefinition>

      <NumberOfColumns>2</NumberOfColumns>

      <NumberOfRows>2</NumberOfRows>

      <CellDefinitions>

        <CellDefinition>

          <ColumnIndex>0</ColumnIndex>

          <RowIndex>0</RowIndex>

          <ParameterName>EndDate</ParameterName>

        </CellDefinition>

        <CellDefinition>

          <ColumnIndex>1</ColumnIndex>

          <RowIndex>0</RowIndex>

          <ParameterName>EntityId</ParameterName>

        </CellDefinition>

        <CellDefinition>

          <ColumnIndex>0</ColumnIndex>

          <RowIndex>1</RowIndex>

          <ParameterName>StartDate</ParameterName>

        </CellDefinition>

      </CellDefinitions>

    </GridLayoutDefinition>

  </ReportParametersLayout>

After then, you can see your report will open properly in SQL 2014

SSRS - Remove decimals If result is whole number

Remove decimals If value is whole number.

Example: If value is 28.00 then report should show 28 and If value is 28.25 then report should show 28.25

How we can achieve this.

Add expression in this report and add below lines.


=Replace(Round(Fields!HourPerWeek.Value,2),".00","")

or

=CDec(Replace(Fields!HoursPerWeek.Value,".00",""))

Copy data from one sheet to another conditionally in Excel VBA

In my excel sheet, I've 2 sheets.

1. Name as "Master Sheet"
2. Q1

In this article we will copy only those rows to sheet "Q1" where Quarter column contains text Q1.

So to copy those rows

Follow the below steps.

1. Open your excel file.
2. Press Alt + F11 to open VBA Editor
3. Insert the module as shown in snapshot below

 

4. Create the function in the module.

Sub Quarter()
   
    Dim xRg As Range
    Dim xCell As Range
    Dim i As Long
    Dim Q1Count As Long
   
    i = Worksheets("Master Sheet").UsedRange.Rows.Count
    Q1Count = 1
   
    Set xRg = Worksheets("Master Sheet").Range("D1:D" & i)
    On Error Resume Next
    Application.ScreenUpdating = False
    For Each xCell In xRg
   
        If CStr(xCell.Value) = "Q1" Then
            xCell.EntireRow.Copy Destination:=Worksheets("Q1").Range("A" & Q1Count + 1)
            Q1Count = Q1Count + 1
        End If
     
    Next
    Application.ScreenUpdating = True
   
End Sub

In this function I've implemented foreach, If cell contains text Q1, then that row will be copied to Destination file.

5.  Save the file as macro enabled workbook. (save as .xlsm format)
6. Press F5 to run the function.

If you want your rows to copy automatically when you press Ctrl + S then implement below code in workbook file. Make sure you are not writing this code on Module.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 

  Quarter

End Sub

Once you press save or Ctrl + S, your rows will copy automatically.


If you want your rows to copy automatically when you change anything in excel then implement below code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Quarter
End Sub

If you want your rows to copy on button click then add button on your excel file.

To add the button, go to Developer menu, Press Insert and select button from Form controls as shown in snapshot below and draw rectangle on page where you want button.




   You can see button in below snapshot.

You can change the button text by pressing right click on the button and you can also give the button Ids as well.
Now we need to add click event of button.

Implement below code in workbook file. Make sure you are not writing this code in Module file.

Private Sub Button2_Click()

  Quarter

End Sub

Once you press button, it will copy the excel file rows to sheet Q1.


If you want to delete all the content of sheet Q1 before copy anything then call below method before calling Quarter method.

Sub DeleteRecords()

      Sheets("Q1").Cells.Delete    

End Sub

This method will delete all the content of sheet Q1.


That's all about the copy and paste. If you have any question you can write on the blog.

SSIS- Transform SQL table records to Excel with Dynamic Name

SQL Server Integration Services (SSIS) is used to perform a data migration task.

Let's create a simple package. if you have SSIS installed on your system then you can see Integration service project while creating new project in Visual Studio.

Step 1:

Open Visual Studio => Go to Menu => File => New => Project. It will open the dialogue below. Select Integration Service Project.


















It will create new blank SSIS project.


Step 2:
Go to project Menu and select SSIS Import and Export Wizard as shown in below screenshot.















It will open the wizard below and press next



Step 3:
Choose a data source, Select SQL Server Native Client as shown in below screen shot. Set the source database connection string and press Next.

Step 4:
Choose a destination, Select the Excel File Path where you want to place the file. Select the excel version as well and Press Next.

Step 5:

Select the option to write the query and press Next.

Step 6:

Write you SQL statement below. you can run the stored procedure as well.
Create the stored procedure and write EXEC Procedure Name. Press Next

Step 7:

It will automatically convert the datatype to excel format datatype. Press Next.

Step 8:

Press Finish and Complete the wizard.

Step 9:

Once you close the wizard, you can see it will automatically create Data Flow Task as shown in snapshot below.

When you click Data flow tab, you can see it has 3 things.

A. Source (SQL table)
B. Data Conversion (That will convert datatypes)
C. Destination (Excel File Columns)

Step 10:

Run the project, it will generate the excel on specific location that you mentioned on Step 4.

How to give random name to excel file every time you run the SSIS package?
Click on link below

Excel name with time stamp

That is all about the article. Hope you like this.

How to give random name to excel file every time you run the SSIS package

If you don't know how to create SSIS Package then go through below link first.

Create SSIS Package

Now, I'll explain how to give random name to excel file with time stamp.

Step 1:

Right click on the Destination excel connection (Excel Connection Manger) and click properties as shown in the below snapshot.




Step 2:


Click on the Expressions as shown in snapshot below.

Step 3:


Click on the ExcelFilePath in the property dropdown and set the expression

In expression we are concatenating time stamp  with excel file name. As example below

"F:\\Client_"+
RIGHT("0" + (DT_STR,2,1252)DATEPART("MM" ,GETDATE()), 2) + "-"+
RIGHT("0" + (DT_STR,2,1252)DATEPART("DD" ,GETDATE()), 2)  + "-"+
(DT_STR,4,1252)DATEPART("YYYY" ,GETDATE()) +  "_" + Right("0" + (DT_STR,4,1252) DatePart("hh",getdate()),2) + "" +  Right("0" +  (DT_STR,4,1252) DatePart("n",getdate()),2) + ".xls"


Now when you run the package, every time it will generate excel file with time stamp.
This is all about the article.

Typing Tips & Tricks

Typing Tricks

Below are the few useful typing tricks that help you to speed up your work.

1) Delete the word  

As you all already know by pressing the Backspace will delete single character. However, pressing the CTRL + Backspace will delete the whole word behind the cursor. It is very helpful  particularly Microsoft programs like Word or Outlook.

2) Paste plain text that you copied  

Usually when you copy text from any website or any other source, it will usually copy formatting(means font size and style) that comes with it. To paste this as plain text, press CTRL + ALT + V instead of the standard CTRL + V, and the system will show a 'paste special' dialog box. Select option and system will paste unformatted text.

3) Move Cursor to beginning of the next or previous word  

  

To speed up your work, move the cursor around with keyboard shortcuts. To move it to the beginning of the previous word, use CTRL + Left Arrow. To move it to the beginning of the next word, use CTRL + Right Arrow.



This is all about article. Hope you like it.

Change Rows to Columns - Pivot in SQL Server

PIVOT

In this article, we will discuss PIVOT keyword in Sql Server. Pivot is responsible to  convert Rows to Columns in Sql Server.

Let's take the example.

Step 1: Declare SQL table and add some dummy data

DECLARE @ScoreTable TABLE
(
MatchType VARCHAR(50),
Year INT,
Score  INT
)

INSERT INTO @ScoreTable VALUES('OneDay',2012,1000)
INSERT INTO @ScoreTable VALUES('Test',2012,2200)
INSERT INTO @ScoreTable VALUES('OneDay',2013,800)
INSERT INTO @ScoreTable VALUES('Test',2013,370)
INSERT INTO @ScoreTable VALUES('Test',2014,1100)


Step 2: Run the Select query and see the default data

This is not mandatory step. However, just check you dummy data.

SELECT * FROM @ScoreTable

In above query, you can see we have 3 columns. MatchType, Year and Score.
We will change Year Rows data in Columns.


Step 3: Write query to covert rows to columns


SELECT * FROM @ScoreTable
PIVOT(SUM(Score)
FOR Year IN ([2012], [2013],[2014])) AS PVTTable




In above snapshot, you can see query changed year to columns and score under each column.


This is all about the article. I hope you like it.