Adding Default Date to Reports Parameters in SSRS Reports

Adding Default Date to Reports Parameters in SSRS Reports

Many times while creating Reports you have to set a default value for your Datetime Parameter like for eg
 Startdate will be one year or one month or one day back and FinishDate like Current Data these Parameters will filter your Report Data between thsese Dates

 Suppose you want to show customer details of 1 year by default In these type of scenarios we have to set the Parameter Default Values.
Below is the Procedure how to add default Date to Report ParameterIn our Case we will add Start Date as 1 year back DateFinishDate as Current DateOpen Design

 View of Your ReportSelect Parameter

 -Add new ParamterName it to StartDate DataType

-DateTimeNow go to Default value

 -Specify Value-Addclick on Fx button and Right Expression

=Dateadd("M",-12,Now())
or
 =Today().AddYears(-12)

click ok

Next for FinishDate Parameter

Select Parameter

 -Add new ParamterName it to StartDate DataType

-DateTimeNow go to Default value

 -Specify Value

-Addclick on Fx button and Right Expression

=Now()
or
=Today()

click ok
Preview your Report Two Parameters will be thereStartDate-1 year back Date from Current DateFinishDate-CurrentDateNow you can filter your Report Based on these Parameters.


Note:In our Example we have taken Start Date as 1 year back Date from Current Date

 if you want to show one month back from Current date then alter your expression

 =Dateadd("M",-1,Now())

if you want to show one Day back from Current date then alter your expression

=Dateadd("d",-1,Now())

Deploying SSRS Reports in SharePoint 2010 asking for UserName and Password Issue fixed

Deploying SSRS Reports in SharePoint 2010 asking for UserName and Password Issue fixed

While Deploying SSRS reports in Sharepoint it is asking for UserName and Password the Reason of this Issue is your not providing Correct Url  of TargetServerURL and for othersfolder  the Sharepoint document Librarary

For Eg Suppose you have your SharePoint Site is:

Http://Testing/TestingDomain/AllDocuments/Reports

Reports is that folder where you are Deploying Your Reports.

Then go to Your Project Properties 

Right click your Project -Properties

Below Screen will appear.

Now in Target Server URLType your sharepoint Main Site or Subsite.Not the Folder where you are deploying the Reports.

Target Server URL:Http://Testing/TestingDomain

for Other Folder Like TargetReportFolder,TargetDatasourceFolder and all other add the Document library path where you are deploying your Datasource and Reports

Like in our Example we are deploying inside Report Folder.

so add below url in other boxes 

Http://Testing/TestingDomain/AllDocuments/Reports

Deploy the Report it will not ask for UserName and Password.

Note:If you select your DocumentLibrary folder of Sharepoint and copy into browser it will include other folders with page extension like below  Http://Testing/TestingDomain/AllDocuments/Reports/form.aspx 

 tmake sure the TargetServer URl and Other Target folder doesn't contain any asp page extension the path should contain upto your document libarary name and do not add backslash after the Document libarary name.

If you are still getting error then it must be some error with Report Server URL alter it the error is most related with URL provided by you.

Year as Input Parameter Returning First date ,Last Date and Number of Weeks in a Year in SQL

Year as Input Parameter Returning First date ,Last Date and Number of Weeks in a Year in SQL

Today One of Developer asked me that Passing year as Input Parameter can Return me the first date,Lastdate and No. of Weeks of that year So I told him yes it is possible and it is quite simple 

His requirement was through stored Procedure.

So I answered him in same way 

Below is the Script

--Below Procedure will take take year as Input 

create procedure CalculateDate

@Date nvarchar(50)

as

select cast(@Date as datetime)as firstdateofyear, dateadd(dd,-1,dateadd(yy,1,cast(@Date as datetime)))as LastDateofYear

,DATEPART(wk,dateadd(dd,-1,dateadd(yy,1,cast(@Date as datetime))))NoofWeeks

--Execute Procedure to view Result 

CalculateDate '2013'

Output

firstdateofyear                          LastDateofYear                NoofWeeks

2013-01-01 00:00:00.000 2013-12-31 00:00:00.000             53

--Query to Return First Date of Year

select CONVERT(Datetime ,'2012')as firstdateofYear

--Query to Return last Date of Year

select dateadd(dd,-1,dateadd(yy,1,cast('2012' as datetime)))as LastDateofYear

--Query to Return No. of Weeks

select DATEPART(wk,dateadd(dd,-1,dateadd(yy,1,cast('2012' as datetime))))NoofWeeks

Space Used by Tables in a Database in SQL

Space Used by Tables in a Database in SQL

In order to know which table is using maximum space or How much space each table is using in a Database 

so for this SQL Procedures are there

In order to check space used by a single table

--Space Used by Single Table in Database

USE Database_Name

EXEC sp_spaceused 'Employee'; 

Output

name       rows           reserved                data                index_size               unused

Employee 7           16 KB               8 KB                   8 KB                  0 KB

--Space Used by All Table in Database

USE Database_Name;

EXEC sp_msforeachtable 'EXEC sp_spaceused [?]'

Output

All tables of Database with there size 

name       rows           reserved                data                index_size               unused

Employee 7           16 KB               8 KB                   8 KB                  0 KB

name       rows           reserved                data                index_size               unused

Employee1 7           16 KB               8 KB                   8 KB                  0 KB

name       rows           reserved                data                index_size               unused

Employee2 7           16 KB               8 KB                   8 KB                  0 KB

How to Rename a column,Table or Database in SQL

How to Rename a columnName,Table or a Database in SQL

To Rename a columnName or TableName creating table is to use SP_Rename System Procedure

To Rename a DBName SP_Renamedb System Procedure

Below we will create a table then we will apply SP_Rename to change ColumnName Table name

create table Employee_rename

(Empid int ,EmpName nvarchar(50),EmpSalary float)

insert into Employee_rename values(1,'Amit','5000'),(2,'Sumit','6000'),(3,'Raj','8000'),(4,'vijay','9000'),(5,'suresh','10000')

select * from Employee_rename

--Rename a ColumnName 1parameter tablename.oldcolumnname 2 parameter NewcolumnName

sp_rename 'Employee_rename.EmpName','EmployeeName'

select * from Employee_rename

--Rename a table name 1parameter old tablename 2 parameter Newtable Name

sp_rename 'Employee_rename','Emp_rename'

select * from Emp_rename

--Rename a Database name 1parameter old dbname 2 parameter NewDB Name

sp_renamedb 'Testing','Testingnew'

Alternative way to Rename DBName-Rightclick DBName from Object Explorer -Rename

Alternative way to Rename Table-Rightclick TableName from Object Explorer -Rename

Alternative way to Rename Column -Rightclick TableName-Design-Change ColumnName-Save the Table Name

Note: For Table and column Name we have used SP_Rename Procedure, but to Rename DB

we have Used Sp_Renamedb Stored Procedure

After testing change your db ,TableName and columnName back to PreviousName

Almost All Strings function of sql 2008

Almost all Strings Manipulation function of sql 2008 like Substring, Left, Right , Stuff, Replace, Replicate...........

This post is to show all string functions of SQLwith examples  and How to use them in TSQL.

We will take a string 'Bangalore' and Apply all function in that string.

--Stuff Replacing value from 'ang' to 'abc' position 2-4 to 'abc' output babclore

select 'bangalore',STUFF('bangalore',2,4,'abc')

--Return value from Position 2-4  output ang

select 'bangalore',substring('bangalore',2,4)

--Replace values b to z output Zangalore

select 'bangalore',REPLACE('bangalore','B','Z')

--Replicate 'bang' word 5 time Output 'bangbangbangbangbang'

select 'bangalore', Replicate('bang',5)

--Reverse the value of bangalore output erolagnab

select 'bangalore', reverse('bangalore')

--Take three values from left Output 'Ban'

select 'bangalore',LEFT('bangalore',3)

--Take three values from right Output 'ore'

select 'bangalore',right('bangalore',3)

--convert values into upper case Output 'BANGALORE'

select 'bangalore',UPPER('bangalore')

--convert values into lower case Output 'bangalore'

select 'bangalore',lower('bangalore')

--Search the pattern and return starting position of  pattern  value if pattern not found returns zero

--output 2

select 'bangalore',PATINDEX('%ang%','bangalore')

--Search the value and return  position of  value if value not found returns zero

--output 2

select 'bangalore',charindex('n','bangalore')

--Count no of Character and  return no. of characters  it include space also Output -13

select 'bangalore' ,LEN(' bangalore is')

--returns no. of byte in a string

select 'bangalore' ,DATALENGTH('bangalore is')

--Trim white space from right side of string

select 'bangalore',RTRIM(' bangalore ')

--Trim left space from right side of string

select ' bangalore',lTRIM(' bangalore')

Note: In above function we have taken the example of a string 'bangalore' in order to use it with tables replace 'bangalore' with your column names'

Update column values with other column values using Select query in SQL

Update column values with other column values using Select query  in SQL

You have a scenario where you want to update your table column value with other table column values for eg  all values of a column to be updated with all values of a column of other table.

for this we will create two tables

The first table will have old salaries of Employees and the second table will have updated salaries of employees

so what we have to do update oldsalary of table1 with newsalary of table 2

Below is the script with Example

--table with old salaries

create table Employee_Update_oldsalary

(Empid int ,EmpName nvarchar(50),EmpSalary float)

insert into Employee_Update_oldsalary values(1,'Amit','5000'),(2,'Sumit','6000'),(3,'Raj','8000'),(4,'vijay','9000'),(5,'suresh','10000')

select * from Employee_Update_oldsalary

create one more table with new salary of the employee

--table first with new salaries

create table Employee_Update_newsalary

(Empid int ,EmpName nvarchar(50),EmpSalary float)

insert into Employee_Update_newsalary values(1,'Amit','15000'),(2,'Sumit','16000'),(3,'Raj','18000'),(4,'vijay','19000'),(5,'suresh','110000')

select * from Employee_Update_newsalary 

Now we want to Update old salary table  with new salary 

--Test this before runnig update query in table what you are updating

 select m.EmpSalary as oldsalary,mu.EmpSalary as newsalary

 from Employee_Update_oldsalary M  join  Employee_Update_newsalary MU

 on m.Empid=mu.Empid and m.Empid=mu.Empid

 --Update query that will update old salary with new salaries

 update Employee_Update_oldsalary  set EmpSalary=mu.EmpSalary 

  from Employee_Update_oldsalary m   join  Employee_Update_newsalary MU

 on m.Empid =mu.Empid and m.Empid =mu.Empid

old values will be updated with new values

Note: In scenario like this test query before running in production server and Secondaly write query inside transactions blocks ie commit rollback so that if any value will updating  values will fail it will rollback all the updated values.