Exact value after decimal without Rounding Off in SQL

 Exact values after decimal without Rounding Off in SQL

When Writing the sql code i was performing calculation in sql query and the values were coming in decimal 

upto 4 and 5 places of Decimal .So the requirement was to show only 2 values after decimal point without rounding off.

Eg  2.8988 it should not be round off to 2.90 it should show only 2.89 that was the requirement.

Converting the values to any datatype was not giving the exact result. All were rounding off the values to 2 decimal place  like below 

--converting to decimal

select convert(decimal(10,2),2.8988)--2.90

---converting to numeric

select convert(numeric(10,2),2.8988)--2.90

--Round Function

select round(2.8988,2) --2.90

If you will see above three queries all were rounding off the values which was not meeting the requirement

So Here is the Solution

select LEFT('2.8988',CHARINDEX('.',2.8988,0))+ SUBSTRING('2.8988',CHARINDEX('.',2.8988,0)+1,2) 

Output 2.89

This is what was required 

below is the test table to use above query in table

create table Numbertest

(Id int identity  (1,1),Number float )

insert into numbertest values(2.8883),(2.87877),(555.43434),(2121.32),(0.3232)

select LEFT(Number,CHARINDEX('.',Number,0))+ SUBSTRING(cast(Number as varchar),CHARINDEX('.',Number,0)+1,2) as Number

from numbertest

output--Exact two Values after decimal without Rounding off

Number

2.88

2.87

555.43

2121.32

0.32

The Reason i cast the number to varchar is substring function works with string ie nvarchar field

if i directly write the column name Number which is of float data type it will throw an error

.

--without casting to varchar throwing error

select LEFT(Number,CHARINDEX('.',Number,0))+ SUBSTRING(number,CHARINDEX('.',Number,0)+1,2)

from numbertest

Msg 8116, Level 16, State 1, Line 2

Argument data type float is invalid for argument 1 of substring function.

Y axis or X axis Chart Gridlines to be bolded in Chart Report SSRS

Y axis or X axis Chart Gridlines to be bolded in Chart Report SSRS

While Creating a Report it was requirment like to bold out  the zero Y axis line so that it will be look distinct then other lines below screenshot will give clear picture on this.

If you will click on the above screenshot all chart Gridliness are Grey in Color and the requirement was like to make Y axis 0 (middle line)to be  bolded out so that it looks different from other lines.

So below are the step-

-Select your vertical axis ie y axis -Properties on Right side 

or After selecting Vertical axis Press F4 key -Properties

-Chart Axis Properties-In the Apperance Section-Stripline 

-click on Stripline

-Add a new Stripline

-StripLine Properties

-Change Border color to black

Increase size of border to 1.5 so that it look different from other lines

-ok

Preview the Report 

The chart will have the Y axis 0 line to bolded out in black color.

Report Last Modified Date and Modified by User in SSRS

 Report Last Modified Date and Modified by User in SSRS

In order to get last modified date of Report and modified by User you have to run below query in your Reporting server Database.

--Below query will Return all the report who has been modified by  users

select username,userid,modifiedbyid,modifieddate,name as ReportName,path as ReportPath from catalog c join users u on 

u.userid= c.modifiedbyid

--Add where condition if you want to see modifieddate and modified by user for a specific report

--where name ='TestReport' 

In order to test manually for a Report to see last modified date and Modified by User

--o you can pick modifiedbyid  from catalog table of your ReportServerDB using below query

select name,modifieddate,modifiedbyid from catalog where name ='TestReport'

then you can give that modifiedbyid to below query

select username,userid from users where userid='35ED7F1F-968D-4D36-8254-809EC2163C08'

--Above id is modifiedbyid to get the name of user who modified the Report.

Adding Total in Last Row as Grand Total in SSRS Report

Adding Total in Last Row as Grand Total in SSRS Report

Many times in your Report you have a requirement like to show grand total in the last row.Its very simple

in SSRS.

Let us suppose you are displaying Salary of Employee  and in the last Row you want to show the total of all salaries 

Below are the steps.

Suppose you have a report like this

EmployeeName   EmployeeSalary

Amit                       10,000

Vikash                     20,000

Ajay                        30,000

Sumit                       40,000

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

TotalSalaries                     1,00,000 --This you want to add in your Report

Go to Report designeer

Add a New row in SSRS Table

Right click Table Properties

--ADD New Row

--Outside group 

In New Row first column-Total Salaries

In New Row Second Column =Sum(Field!EmployeeSalary.value)

Click Ok

Preview The Report 

The Report will have a new Row with displaying Sum of Salaries.

                                                                         

                                                  

Inserting multiple Rows in a Single Insert Query in SQL 2008

Inserting multiple Rows in a Single Insert Query in SQL 2008 

Many times you have requirement to insert many Rows at once in a table, so its a best to use Multi-row values clause in Insert Statements which was added in sql 2008.

Below is the Sample table using Multi row values clause in Insert Statement.

create table Employee1

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

--This will not work in sql 2005

insert into Employee1 values('Amit','5000')

,('Sumit','6000')

,('Raj','8000')

,('vijay','9000')

,('suresh','10000')

--In SQL 2005

insert into Employee1 (EmpName ,EmpSalary )

Select 'Sumit','6000'

union all

Select 'Raj','8000'

union all

select 'vijay','9000'

union all

select 'suresh','10000'

--Simple Insert Statement with multiple Insert query

insert into Employee1 values('Amit','5000')

insert into Employee1 values('Sumit','6000')

insert into Employee1 values('Raj','8000')

Behaviour and Output of Number functions and Datatype in SQL.

Many times when you have decimal values in your calculation of sql you didn't get exact result or some values get round off  so it depend upon the function or Datatype in which you are converting  your calculated values.

Below are some output and behaviour of a decimal number used converted with Different Datatypes and functions.

--Testing Number

select 00.8788

Datatype int

select cast(00.8788 as int)---0

Datatype float

select 00.8788 as float--0.8788

converting to decimal

select convert(decimal(10,2),00.8788)--0.88

converting to numeric

select convert(numeric(10,2),00.8788)--0.88

Round function with cast

select ROUND(cast ('00.8788' as float),2)--0.88

Round Function

select ROUND('00.8788',2)--0.88

Cieling Function

SELECT Ceiling(00.8788)--1

Floor Function

select FLOOR(00.8788)--0

The Variable @abc has already been Declare Could not Update a list of Fields of Query in SSRS Report

A comman Mistake while working with SSRS Report that SSRS is Case Sensitive while SQL is not
The mistake what i was doing that i created an sql query for the Report where i declared lots of variable
what i did like
--take an Example
I was having two parameters in the Report StartDate and EndDate
 My parameter was @StartDate in the Report
In my query i was using like this @startDate --small s

Select empname,address from employee where
empdob between @startDate and @EndDate

In sql the  query was working file but when i run the same query using SSRS query designer i saw report was asking for one more Parameter i start date

I clicked ok after passing values to Paramter

The Reason behind this error the SSRS considered @startDate as New Parameter of Report I
changed @startDate to @StartDate the Report works without any issue.

Note-@startDate has Started from small s and @StartDate with Capital S so SSRS consider these as two paramter these is a comman mistake so always keep Paramters in same Case both in SQL query and SSRS Report.