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

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:

A 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