Search This Blog

Open Outlook Email Passing Field Emails to Outlook in SSRS Report

Open Outlook Email  Passing Field Emails to Outlook in SSRS Report

Below are the Steps to create a hyperlink so to open an outlook email message appears when clicked on any Employee

Example is like Clicking on Employee will pass EmployeeEmail to outlook  and will  open outlook 

Select your EmployeeName TExtbox
-TextBox Properties
-Action
-Go to URL

="MailTo:" & Fields!EmailAddress.Value

Click ok

 

Now clicking on any Employee will open the outlook with his Email Adress.

Note -IF you want to pass a Hardcoded email Hyperlink to Employee Name
Change your Expression to -

="MailTo:TestUser@gmail.com"

Sum of only Positive Values of a column and sum of negative value of a column in SQL

Sum of only Positive Values of a column and sum of negative value of a column


While creating a Reports one of my colleagues asked me that he has to display sum of only Positive values of a column in a new column and sum of only negative value of a column in a new column Although it can be done easily in SSRS Report also
but his requirement was through sql code.

So below is the Sample code with Solutions

--Creating a test table with Both Position and Negative Values

create table value
(Testvalues int)

insert into value values(1),(-2),(3),(-4),(5),(-6)

Select * from Values

If you will calculate the total of positive values it will be 9 and for negative values it is -12


SO below is the Query
select
  SUM(case when Testvalues>0 then Testvalues else 0 end)SumPositiveValue,
       SUM(case when Testvalues<0 then Testvalues else 0 end)SumNegativeValues
       from value


SumPositiveValue SumNegativeValues
9                           -12

Managerial Round Questions for Developers and Software Engineers

Managerial Round Questions for Developers and Software Engineers 


This Post is about Interview questions asked in Managerial Round or in your final rounds after all of  technical Rounds
AS a BI Developer I have attended various interviews in some i succeed and in some I failed. So based on my experience I am sharing this Post.

 List of questions which were almost same in lots of Managerial Round.

Tell me about yourself your work and your Roles and Responsibilities in current company?

In which Project You are current working who is the customer give some details?

Most Complex thing faced in the Project and how did you managed?

Why are you leaving your current company?

Biggest mistake you did while writing code?

what if you will do if you get more package from some other company?

How long will you work for this company?

Your commitments with the company?

Where do you want to see yourself after 5 years?

If you are working extra hours daily  How will you manager?

What if you will get bored from your work?

Your Role Model and why?

How often you can join if you got offer?

These are some of important questions asked in most of managerial rounds or after technical rounds

 Tips from my side is  be confident ,answer point to point and  don't elaborate your answer too much..

Drill Down Report or Expanded or Collapsed Report in SSRS

Drill Down SSRS Report or Expanded  Collapsed SSRS Report

Below are the steps how to create a Drill Down Report 

In Order to create a Sample Drill Down Report we are using adventure work Database customer table

SELECT     CustomerID, TerritoryID, AccountNumber, CustomerType, rowguid, ModifiedDate
FROM         Sales.Customer


created a dataset with above query
Adding table in the Report
Displaying all Records int the Report

Now we want to expand the Report Based on TerritoryID Column 

So for this go to ROW GROUPS in Report Designer (Below Left hand Side) 
ADD a Group -Territory ID
Group by -TerriotoryID

A new column will be added up in the Report for Group TerritoryID.

Now Delete the Column TerritoryID(Delete the older TerritoryID column Not that one which is added by Adding Group)

Now we want to expand and collapse other column Based on TerritoryID group.
Go to Row Group
There will be a details Group last.
Go to visibility Property -  Report is intially Run-Change in to Hide
check the box display can be toggled by Report ITEm-(TerritoryID1)--This text is same which we added after adding Group by TerritoryiD Group

click ok 
Preview the Report

The Report will have Territory ID as Expanded and collapsed column .
Clicking on any Territory ID will expand other Records.

Note:Here we used Territory as Parent Group you can add other group also as Parent and child Groups.

Dynamic SQL with Explanation and Example

Dynamic SQL with Explanation and Example 


Dynamic SQL statements can be built at run time and placed in a string host variable.
They are then sent to the DBMS for processing. Because the DBMS must generate an access plan
 at run time for dynamic SQL statements, dynamic SQL is generally slower than static SQL.
 When a program containing dynamic SQL statements is compiled, the dynamic SQL statements are not
 stripped from the program, as in static SQL. Instead, they are replaced by a function call
 that passes the statement to the DBMS;
static SQL statements in the same program are treated normally
.
To Know more about Dynamic sql go to
 msdn link-http://msdn.microsoft.com/en-us/library/ms709342(v=vs.85).aspx 

Let us suppose a scenario where have to show data from a table and the table Data should be displayed based selection of table by user so here dynamic sql will come into picutre.

below is a simple stored procedure that will take table name as input

Create proc p
(@tblname nvarchar(100)
)
as
begin
declare @tbttest nvarchar(100)
set @tbttest ='select * from ' +@tblname+'';
--exec(@tbttest);
exec SP_executesql @tbttest 
end

exec  P 'test'

This question is often asked by interviewer to display data of Table at run time user will just pass the table name and you have to show the Data of that table.

Just pass your table name to procedure P it will display all Data of Test table you can pass any table name
You can execute dynamic sql  with Exec(@tbttest) or by executing System Procedure
exec SP_executesql @tbttest .

 sp_executesql is more efficient, faster in execution and also supports parameter substitution. If we are using EXECUTE command to execute the SQL String, then all the parameters should be converted to character and become as a part of the Query execution.

EmployeeName with their ManagerName in SQL

EmployeeName with their ManagerName in SQL  


A common Interview question asked by interviewer to display all EmployeeName with their Manager Name.
Its not only a interview question but also in many situation you have to fetch the ManagerNames with their Employees.

So below is the solution with sample table

Consider below table

create table employeemanager1
(empid int identity(1,1),
employeename nvarchar(100),
ManagerId nvarchar(100)


insert into employeemanager values('E1',3),('E2',1),('E3',1),('E4',2)

select * from employeemanager

empid employeename ManagerId
1 E1 3
2 E2 1
3 E3 1
4 E4 2

If you will see the table, the table is displaying EmpiD with their Name and their ManagersID . A Manager is Also an Employee
So we have to display EmpID,EMployeeName and their ManagerName
Eg E1 has Manager E3 ......

--Self join Showing employeeName with their Manager Names
select e.empid , e.employeename,em.employeename as ManagerName from employeemanager e join 
employeemanager em on em.empid  =e.ManagerId  

empid employeename ManagerName
1 E1 E3
2 E2 E1
3 E3 E1
4 E4 E2

Now sql is showing all Employee with their ManagersName.

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.

How to Move chart Bars and Line closer to Y axis in a chart Report in SSRS

 How to Move chart Bars and Line closer to Y axis in a chart Report in SSRS

While creating one report there was a  requirement that there should be no gap between horizontal axis first legend and y axis that mean the bar should be started from y axis by default there will be around 3 inches gap between y axis and horizontal axis first legend.

Below is the screenshot of Default Gap
In the screenshot u can see the gap between January (first legend of horizontal axis) and
Y axis values i.e(0,0.5,1.....)

The Requirement was like January should be sticked or moved to y axis
below are the step
Go to horizontal axis Properties.
-Axis Options
--Side Margins-Change it to Disable--by Default Auto.
click ok

like in below screenshot
Preview Report -The Horizontal axis first axis will stick or move to Y axis.

Back to Previous Report or Parent Report in SSRS

Back to Previous Report or Parent Report in SSRS

Many times when working with multiple reports on clicking a link on a report you are navigating to other report and clicking on child reporty you want to go back to your parent report .
Then below Javascript you can use.

Back to Previous Report or Parent Report in SSRS
Add a Image or Text box in your Report 
In below back  image i have added an Image GO to TextBox or Image Properties.
Action-Go to URL
="javascript: history.go(-1)"




If you want to go to more back then replace -1 with -2 to go back upto 2 levels, -3 to go back 3 levels and so on.

Another way is like

 In  back  image i have added an Image
 GO to TextBox or Image Properties.
--Earlier we use Go to URL Now using Go to Report
Action-Go to Report-Select your Report from DropDown
Ok 
Clicking on backimage or Textbox willl Navigate to report selected from dropdown.

NOTE:
1-If you are opening page in a Sharepointwebpage and ="javascript: history.go(-1)" will not wok until u have a separate webpage for each report as through one web page you are navigating to other childs report then in order to go back to parent report remove -1 from javascript.
="javascript: history.go()"

2-When using Javascript function to go to parent report or previous report enabled javascript in your browser 
Steps to enable in Internet explorer.
  1. On the Tools menu, click Internet Options, and then click the Security tab.
  2. Click the Internet zone.
  3. If you do not have to customize your Internet security settings, click Default Level. Then do step 4
  4. If you have to customize your Internet security settings, follow these steps:
    a. Click Custom Level.
    b. In the Security Settings – Internet Zone dialog box, click Enable for Active Scripting in the Scriptingsection.
  5. Click the Back button to return to the previous page, and then click the Refresh button to run scripts.
For other browserrs see Microsoft link-http://support.microsoft.com/gp/howtoscript


Drill Through Report or Hyperlink to other SSRS Report Passing Values from one Report to Another in SSRS

Drill Through Report or Hyperlink to other SSRS Report Passing Values from one Report to Another in SSRS

This post is about the scenario when  click on any values of Parent Report you want to Go to child Report.  You can also pass clicked value of parent Report to filter child Report.

let us take example of Adventure work Database (AdventureworkDW2008)

we will take two table DimProduct category and DimProductSubCategory

First we will create two Report one with table DimProduct category and another Report with DimProductSubCategory


Parent Report
Create Dataset with SQL

SQL -Select * from DimProduct category

Screenshot




Child Report

Create Dataset with SQL

select * from DimProductsubCategory 

screenshot



above we created just two simple report now in click of Parent Report-Product Category Key we want to navigate to child Report i.e Simple Drill through Report or Hyperlink to a Report.

for this go to Parent Report (Product Category Report)

Select Product CategoryKey column
Textbox Properties
Action
GO to Report
Specify a Report-Select your child Report
Child Report
ok

Screenshot










Preview the Parent Report 

click on any Product Category field it will open the Child Report



Now we want to Pass value from parent to Child report.

so this is the final Part where clicking on the ProductiCategoryd will filter the child Report i.e will show data of only clicked Product category Id in the child Report .

for example clicking on Product categoryid -1 on Parent Report  will display only product categoryid -1 data in child Report

For this what we will do we will pass the Product categoryid from Parent Report to child Report to filter the child Report


In order to achieve this

Add a Parameter to child Report

just change the child report dataset query to

select * from DimProductsubCategory where ProductCategoryKey =@ProductCategoryKey

click ok 

This will automatically add a Parameter to child report


Now go to Parent Report 
Select Product CategoryKey column
Textbox Properties
Action
use parameter to run the Report
In Name write the name of child report parameter( the name should be same as Child Report Parameter
in value dropdown add Productcategory key

preview the Parent Report 

click on any Product category key in my case  i am clicking  on category 4 in Parent Report  it will open the child report with only data having Product category key 4 in child report..

if you don't want to show the parameter in child Report.
Make it hidden by Right click Product category Parameter parameter properties-visibility-hidden.

Screenshot of child Report with category 4 Data.





Non Clustered Index on Primary Key in SQL

Non Clustered Index on Primary Key in SQL

I believe all developer are aware of this fact that by Default Primary key creates Clustered index on the Column and there is only one clustered index per table.

But sometimes you want to have a NONclustered index in your Primary key column .

Below is the Script
.
--while creating table you can define it

create table test
(IDNum int identity (1,1) PRIMARY KEY NONCLUSTERED ,
 EmployeeName varchar(50)
 ) 

--Or you can alter the table to add Primary Key with NonClustered Index.

alter table Test
add  CONSTRAINT [PK_EmployeeName] PRIMARY KEY NONCLUSTERED 
(
[IdNum] ASC
) ON [PRIMARY]






ALL Datetime Formats in SQL

ALL Datetime Formats in SQL 

Many times you have to format your datetime to get desired format of Datetime 
for eg mm\dd\yy or dd\mm\yy or yy\mm\dd or dd\mm\yy\hh\mm .............

Below are the list of all Datetime formats ..

ALL Datetime Formats in SQL 

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)
                                      
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy           

SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd      

SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy

SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy

SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

SELECT convert(varchar, getdate(), 106) -- dd mon yyyy

SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy

SELECT convert(varchar, getdate(), 108) -- hh:mm:ss

SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)

                                        -- NOV 7 2013 17:22:44:016 AM   

SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy

SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd

SELECT convert(varchar, getdate(), 112) -- yyyymmdd

SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm

                                        -- 07 NOV 2013 17:22:44:016    

SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)

SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)

SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm

SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm

                                        -- 2013-11-07T17:22:44:016  

String is Palindrome or Not in SQL

String is Palindrome or Not in SQL

In order to test your logic many interviewer have asked this question write a query that will return where the input string is Palindrome or Not.
Most of developers are aware of palindrome words and Sentences just to recall it here is a brief defination of  palindrome:

palindrome is a word, phrase, number, or other sequence of symbols or elements, whose meaning may be interpreted the same way in either forward or reverse direction...From Wiki

Eg-Words-Civic,Mom,DaD,Madam,Level

Sentences-1-Sir, I demand, I am a maid named Iris
                 2- Never odd or even

So below is the sql query that will tell wheather a string or a word is palindrome or not

DECLARE @String NVARCHAR(100) = 'Never odd or even'
SELECT CASE WHEN REPLACE(@String, ' ', '') = REVERSE(REPLACE(@String, ' ', ''))
THEN 'Palindrome'
ELSE 'String is not Palindrome' END [Output]
GO

Output
Palindrome

DECLARE @String NVARCHAR(100) = 'Never odd '
SELECT CASE WHEN REPLACE(@String, ' ', '') = REVERSE(REPLACE(@String, ' ', ''))
THEN 'Palindrome'
ELSE 'String is not Palindrome' END [Output]
GO

Output
String is not Palindrome

Explanation-We have used two function in the query one is Replace which is replacing space with no space between the words second one is Reverse which is reversing the string after removing space in the string if string matches then Palindrome else not Palindrome
below is simple example of Reverse and Remove Function.

SELECT REPLACE('abc','b','c')--acc

SELECT REVERSE ('abc')--cba

Grouping by Multiple Columns in SQL

Grouping by Multiple Columns in SQL

I will explain this topic with an example.

Yesterday one of my colleague went for an Interview.
The Interviewer asked him that you have two tables Family and Members 
Family table has familyId,Headof the Family and FamilyAddress fields
whereas
In Child Table it has columns MemberID,FK_FamilyId,MemberName
so the Interviewers asked him  to calculate total no. of members for each family which includes HeadofFamily and familyaddress.

Below is the Table Structure with Data.

create table Family
(familyId nvarchar(50),
 HoF nvarchar(50),--HeadofFamily
 familyadd nvarchar(200)
 )
 create table Members
 (MemberId nvarchar(50),
  FK_FamilyId nvarchar(50),--Take values from Familyid from familytable
  MemberName nvarchar(50)
  )

--Family Info
insert into Family values(1,'rakesh','btm2'),('2','raghu','jpngr'),('3','vnk','mahalkshmi')

--FamilyMember Info.  
insert into members values('1','2','sumit'),('2','2','amit'),('3','2','mit'),('4','1','vikas'),('5','1','vijay'),('6','3','anil')

Now if you see in the family table Rakesh,Raghu and Vnk are the Head of Family.

The requirement was like TotalNo.ofMembers,HOF,FamilyADD

So below is the query using using Group by Various column.

  select COUNT(m.memberid)'No.ofMembers',f.HoF ,f.familyadd  from Members m join Family f
  on f.familyId =m.FK_FamilyId  group by f.familyId,f.HoF ,f.familyadd

No.ofMembers HoF familyadd
2              rakesh            btm2
3               raghu             jpngr
1               vnk                   mahalkshmi

Explanation-The query is giving the count of Members for each family using group by familyID In order to get  Headoffamily and Familyaddress the query has been grouped by Hof and FamilyAdd too.

Note if you will remove HOF and FamilyADD from group by clause it will throw an ERROR.

Msg 8120, Level 16, State 1, Line 2
Column 'Family.HoF' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The Reason for the error is every column written in Select query must be included in Group by clause when using select with aggregate function and group by clause, except the columnn which  is used in aggregate function ( min,max,count,avg...) .