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

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