Modifying Query in Powerpivot Report in Excel

Modifying Query in Powerpivot Report in Excel

In order to modify existing query in power pivot report in Excel 2010 and Excel 2013.

You have to follow below Steps

Excel Menu Bar

Go to Powerpivot tab

PowerPivot window

--This will open your existing query table

--Select the table in powerpivot window

Go to Design Tab

click table Properties

Edit your query ( modify the query)

click save.

Screenshot 

Now go to Powerpivot excel Report.

Data

Refresh all 

It will Refresh the report with new Fields or Data.

Conditional Formating in Pivot Table Report in Excel 2010

Conditional Formating in Pivot Table Report in Excel 2010

While working with pivot table Report many times you have to apply conditional formatting to table,column or cell

for eg like if table or column has negative value make that color backround red.
if greater than 1000 then black
if equal to 500 then red and so on...

So in this post i will show how to apply conditional formatting in pivot table report in excel 2010.

Note: we will use the same pivot table which we have created in previous post

Creating Excel Pivot Table using SQL Database

http://sqlandssrssolutions.blogspot.in/2013/12/creating-excel-pivot-table-using-sql.html

so we will apply conditional formating in Salary Column like

if salary is equal to 10,000 then Darkyellow color text with light yellow background
if salary 9000 then redcolor text with light red background
if salary less then 9000 then greenbackground with bold text

For this select empsalary column

Go to home tab

conditional formatting

Higlight cells Rules

-Here you will find lots of options

-Greater then
--less then
--between
select the condition Equal to 
write  10,000 in first cell and in second cell some default custom is there apply 
Light red fill with Dark red background

 then apply other conditions.

Screenshot after applying condition format and options of condition format.

apply condition as mentioned above....

In condition format you can apply other conditions option like 

 Top Bottom Rules--for applying condition in top 10 or bottom 20 like this.
 Data bars- To convert your column to Bars.
 color scales - To apply coloring in cell and columns.
Icon set- In order to use indicators.

PowerPivot in Excel 2010

PowerPivot in Excel 2010

Below i will show to create a Pivot Table Report Using PowerPivot in excel.This is just a simple Report.

The Reason of using Power Pivot is it extends the capabilities of the PivotTable data summarisation and cross-tabulation feature with new features such as
expanded data capacity, advanced calculations, ability to import data from multiple sources, and the ability to publish the workbooks as interactive web applications.

Below are the steps of creating Power pivot Report in Excel

Open excel 
PowerPivot Tab
PowerPivot Window
--This will open a new window
Here we will create a report using SQL Database

-Select from Database
-ServerName
DatabaseName
-Next(screenshot)

-Here you wll get two options

-Select a list of Tables
-Write Query that will specify data to import

-So i will select from table
-next
-select table --you can select multiple table and can link them
--IT will create a Report for you with selected Table
Now click on pivot table
and insert a pivot table
In the Right hand side you will see pivot table fields

--YOu can add the column which you want to display in Report also you can add Slicer Directly like in the screenshot.

This is just a sample report so that anyone can get started with Powerpivot you can create Dashobards and many Complex reports using PowerPivot.

Displaying Pivot Table Column outside Pivot Table

Displaying Pivot Table field outside Pivot Table

In my Yesterday post i showed  how to display sum of Pivot Table column oustide Pivot table

In this Post i will show you  how to display pivot table field (Not Sum) outside Pivot table.

For This we have an excel formula GetPivotData.

We will use the Previous blog to create pivot table

Creating Excel Pivot Table using SQL Database

http://sqlandssrssolutions.blogspot.in/2013/12/creating-excel-pivot-table-using-sql.html

Slicers to Filter data of Pivot Table in Excel

http://sqlandssrssolutions.blogspot.in/2013/12/slicers-to-filter-data-of-pivot-table.html

Using the same above Pivot table and slicers to demonstrate the Solution to Display Pivot Table field outside Pivot Table.

Now suppose we want to display Raj salary  based on EmpName Raj salary (8000)

in Top row third fifth column i have to display Raj Salary so here is the formula 

=GETPIVOTDATA("Sum of EmpSalary",$A$2,"EmpName",A4)

Sum of Salary- Is the the pivot table column whose value to be displayed

$A$2-Cell of Pivot Table

EmpName-Is the column based on value to be displayed

A4-Is the Cell whose value to be displayed(Raj).

You can add Multiple fields data in GetPivottable by adding fields and item Name,

In order to display Grand Total outside Pivot Table Read my Previous blog.

Displaying Pivot Table Column Sum outside Pivot Table 

http://sqlandssrssolutions.blogspot.in/2013/12/displaying-pivot-data-sum-outside-pivot.html

Displaying Pivot Table Column Sum outside Pivot Table

Displaying Pivot Table Column Sum outside Pivot Table 

I was working on a Excel Report where i have to display the Grand Total of pivot table field in the Header of Report also  i was having slicer (Report filter ) in the Report.
Testing  Pivot Table and Slicers examples are in my Previous post.

Creating Excel Pivot Table using SQL Database

http://sqlandssrssolutions.blogspot.in/2013/12/creating-excel-pivot-table-using-sql.html

Slicers to Filter data of Pivot Table in Excel

http://sqlandssrssolutions.blogspot.in/2013/12/slicers-to-filter-data-of-pivot-table.html

Using the same above Pivot table and slicers to demonstrate the Solution to Display Pivot Table Sum outside Pivot Table.

Mistake What i did earlier in the Header cell i called the GrandTotal cell thinking that it was simple just to display the total.

like in below Report  c11  is the cell where i was having GrandTotal 53000 and i have to display the total
Salary in the first riw i.e Header.(c1)

so what i did in formula bar of C1 i directly wrote =C11(c11 is the cell displaying Grand Total)

It was displaying total correct until i didn't applied  filtered to the Report when i filtered the Report using Slicer it was showing Zero as Total Salary,which was 13,000.below screenshot.

Then i used GetPivotData function to display pivot table field but it didn't worked with GrandTotal for me.

So Here is the trick which i did for Solution .

write the formula =Sum(c3:c11)/2

--c2 is the first cell of the column from where to start summing 
--c10 is the last cell which has GrandTotal of all Salary.

Now add any filter to the Report it will show the Exact sum.

Note:Must  Include Grand Total cell in the Sum formula as final Range.

Slicers to Filter data of Pivot Table in Excel

Slicers to Filter data of Pivot Table in Excel

Slicer are used to filter the Pivot table in Excel slicers are the filtering component that contain a set of buttons
that enable you to quickly filter the data in a PivotTable report, without the need to open drop-down lists to find the items that you want to filter.
You can also add Slicers with  Olap cubes functions.(SSAS)

Slicers are same as Report Parameter in SSRS .

Below is the example how to add a slicer in the pivot table to filter the data.

if you want to know how to create a pivot table in excel then read my previous blog on
Creating Excel Pivot table

http://sqlandssrssolutions.blogspot.in/2013/12/creating-excel-pivot-table-using-sql.html 

we will add slicers to same pivot table which we created on our previous blogs.
.

we have 3 columns in our pivot table we will add slicers (empname and empid ) to filter the pivot table based on empname and empid

so below are step

First create a pivot table using the above link
click anywhere in your pivot table
Insert tab
click on Slicer
It will display all the column of your pivot table with checkboxes.
Select the checkbox for which you have to  add slicer in our case we are adding Empname and Empid

It will add two slicer in your Sheet.

Adjust the Position of Slicers.

click on any values of slicer to filter the report the Report will be filtered based on your values selection in slicer.

press Ctrl key with mouse click to add multiple values in slicer..

In order to clear all the filter from slicer click cross sign highlited in red in below screenshot.

Excel Report Hyperlink to Url

Excel Report Hyperlink to Url 

In order to add a hyperlink in a excel column create a excel sheet first like below sample data.with first two columns only.

Now in third column we will add hyperlink to site URL>

first column have site Name Second Column has Site URL now on Third Column we want the Name of Site and clicking on any Name in third column will open the Site URL. 

This is simple by hyperlink formula in Excel

Select your third column 3 rd row which is empty first 

click on fx 

Write formula
=Hyperlink(B2,A2) 

B2 is the value field which is url and A2 is the Name which we want to display. Writing formula will Automatically generate the site name with hyperlink to site URL.

Just Drag the formula field to all 5 rows of 3 rd column by clicking on plus sign..

Some play around with Hyperlink function.

The second argument of hyperlink function is optional if you will not give any value to second argument
it will take the first argument name which is url name eg

=Hyperlink(B2) 

Then site URL =http://sqslandssrssolutions.blogspot.in

and you can specify any name in Second argument of Hyperlink function.
like in last row i have use the formula

=Hyperlink(B2,"G") 

That's why it is displaying G not the site Name in row 6 of 3 rd column.

Now click on any site name in 3rd column will navigate you to corresponding Address.

Creating Excel Pivot Table using SQL Database

Creating Excel Pivot Table using SQL Database-Table

A pivot table is a data summarization tool found in data visualization programs such as spreadsheets or business intelligence software. Among other functions, a pivot-table can automatically sort, count, total or give the average of the data stored in one table or spreadsheet. It displays the results in a second table (called a "pivot table") showing the summarized data. Pivot tables are also useful for quickly creating unweighted cross tabulations.

Now in order to Create Pivot Table in Excel using SQL Database in SQL 

First create a table in SQL with Below Script

CREATE TABLE [dbo].[Employee1](
[Empid] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee1] ON
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'Amit', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'Sumit', 6000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'Raj', 8000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'vijay', 9000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'suresh', 10000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (6, NULL, 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, NULL, 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (8, NULL, 5000)
SET IDENTITY_INSERT [dbo].[Employee1] OFF

Now open Excel 

---First we will create a Dataconnection to SQL Table for this below are the steps

Go to Data Tab 
Connection
ADD
Browse for More
New Source
SQL Server
-Connection Name
-Select your Database Name
-Select Table Name-Employee 1--above table which we have created
-click ok

Now in Order to create Pivot table

Go to Insert Tab
-Pivot Table
-It will ask for connection file
select the Name of connection file which we created above
click ok 

In excel you will see the the column Name in Right hand Side.

Drop EmpName and EmpID to Row Labels and Salary field to Values. It will Display Total Salary Based on  EmpID and EmpName.Its same as Group by in SQL displaying Sub total also.

IF you don't want subtotal of each EmployeeName Select your Pivot Table

 Go to Pivot Table tools-Design

-SubTotals

-Do Not show Sub Totals

You will have your Pivot table displaying Employee Name ,ID and Sum of Salary.

Searched Case Expression in SQL

Searched Case Expression in SQL

The CASE expression has two formats:

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

The searched CASE expression evaluates a set of Boolean expressions to determine the result.Like it Searches for condition and based on that it display Data

Example explaining Searched case expression

 In below Table we have Empsalary field it will check for Salary if Salary is 5000or 60000 it will double the salary in the output and if empname is null then it is returning salary as zero

--Table with employee Detail and Salary

CREATE TABLE [dbo].[Employee1](
[Empid] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [nvarchar](50) NULL,
[EmpSalary] [float] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Employee1] ON
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (1, N'Amit', 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (2, N'Sumit', 6000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (3, N'Raj', 8000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (4, N'vijay', 9000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (5, N'suresh', 10000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (6, NULL, 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (7, NULL, 5000)
INSERT [dbo].[Employee1] ([Empid], [EmpName], [EmpSalary]) VALUES (8, NULL, 5000)
SET IDENTITY_INSERT [dbo].[Employee1] OFF

select * from Employee1

--Query Displaying Empdetail with different conditions

select case when empsalary =5000 and EmpName IS not null then '10000'
when EmpSalary =6000 then '12000'
when empsalary=5000 and EmpName is null then '8000'
else empsalary end Salary
,EmpId,Empname from employee1

If you want to know about simple case expression with examples read my post:
http://sqlandssrssolutions.blogspot.in/2013/07/introduction-to-case-statement.html

Fetching Excel Sheet Data in SQL using SQL Query

Fetching Excel Sheet Data in SQL using SQL Query 

In order to Select, fetch or Read  the Excel Sheet Data using SQL Query or Creating Table in SQL  using Excel query first configure the advanced option through below scripts

step 1
sp_configure 'show advanced options', 1;
RECONFIGURE;

step 2
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

Step 3


for MSExcel 2007 and 2010 --Database is the path of your excel sheet ,sheet1 is the name of sheet1

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database=E:\book2.xlsx', 'SELECT * FROM [Sheet1$]');


Step 4 if you want to create a new table in sql based on excel data
for MSExcel 2007 and 2010 --Database is the path of your excel sheet ,sheet1 is the name of sheet1

SELECT * into test FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0; Database=E:\book2.xlsx', 'SELECT * FROM [Sheet1$]');


Case if you are using Excel 2003

SELECT * FROM 
OPENROWSET('Microsoft.ACE.OLEDB.4.0', 'Excel 4.0;Database=E:\book2.xls;HDR=YES', 'SELECT * FROM [Sheet1$]');


 In case if you will get error like 

Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Run the below script to fix the Error

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
GO

Note: You can use import export wizard ,SSIS also to import Data from excel sheet to sql

Incorrect Paging in SSRS Report

Incorrect Paging in SSRS Report

While Creating a SSRS Report one of my friend Faced problem like he has some records in his report which were coming in two pages of SSRS Report.

But the Total no. of pages in the report was showing 3 and opening 3rd page was completely blank with no records which was of no use so he don't want to have 3 rd page in Report.

Below screenshot with Empty Records last page.

Solution

I checked the Report the Reason was like the size of Report body was greater then Report size so i changed the Report body to 2 inches

and Report size  to 10 inches 

Preview the report 

Now Report was showing only 2 pages which was Required.

now there was no extra page issue in the Report.

Note:Always keep the Size of Report body less then Report to avoid these types of issue.In order to know how to fix Report body and report size read my post on Report size.

http://sqlandssrssolutions.blogspot.in/2013/12/how-to-fixheight-and-width-of-ssrs.html 

SQL Facts and History

SQL Facts and History

As this is my 100 post on my blog so i thought to write some  interesting Points about SQL.

1-SQL stands for Structured Query Language. SQL is a standard language that was designed to query and manage data in relational database managementsystems (RDBMS).

2-It is a database programming language(TSQL) that acts as a means of communication between two other programs such as a Webapplication and a database.

3-The relational model is language-independent. That is, you can implement the relational model with languages other than SQL.

4-SQL was initially developed at IBM by Donald D. Chamberlin, Donald C. Messerly, and Raymond F. Boyce in the early 1970s called SEQUEL (short for Structured English QUEry Language) for their RDBMS product called System R. 

5-The name of the language was later changed from SEQUEL to SQL because of a trademark dispute.

6-Major component of SQL DDL,DML and DCL.

7-Examples of some  websites that use SQL in some capacity are Ebay, Microsoft and Amazon.

8-SQL is not a case sensitive language.

9-SQL is a Product of Microsoft.

10- Microsoft SQL Server, has several add-on services that manage different aspects of enterprise business intelligence and information processing such as data integration, reporting and analysis(SSRS SQL Server Reporting Service ,SSAS SQL Server Analysis Service,SSIS SQL Server Integration Service).

IF Else Condition in SQL

IF Else Condition in SQL 

The IF Else condition in sql allows us to control the flow of our sql code or query.
For example the below if else condition shows that if current date is greater then 15 of the month then it will show
Fee pending please pay the fees
else It will show 'you have paid your fees'


if day(getdate())>15
print 'Your fees date is over please pay the Pending fees'
else
print 'you have paid your fees'

Output--as today date is 16 december greater then 15
Your fees date is over please pay the Pending fees

IF you want more condition to be added you can you else if statetement or case statement.

Syntax else if

IF condition1..
result1
else if  condition2...
result2
else condition2...
result

Syntax case 
Case when condition1 then result1
Case when condition2 then result 2
else result3 end

How to Fix Height and width of SSRS Report

How to Fix Height and width of SSRS Report

A conman mistake is like in order to fix height and width of report some users use to change height and width of body of Report and faces the problem that while preview the report the report height and width is not getting fixed. 

So,In order to fix or set SSRS Report Height and width 

Right click outside the report.

select Report properties.

set Height and width of Report.

Click ok. 

Preview the Report the Report size will be fixed.

Note: In case where your table size is more then Report size then first you have to fix the Table height and width and then Report Size.

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

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.

4. 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