Search

Advertisements

TSQL - SET FMTONLY ON/OFF - To display only Column headers (Empty resultset)

Here is an example :

--Sample data
DECLARE @Temp TABLE (Id INT,Name VARCHAR(50),DOJ VARCHAR(25))
INSERT @Temp SELECT 1,'Sathya','1-1-2011'
INSERT @Temp SELECT 2,'Deepak','2-1-2010'
SELECT * FROM @Temp


--To display only column headers

SET FMTONLY ON
SELECT * FROM @Temp

--To display column headers with data rows(normal behaviour)
SET FMTONLY OFF
SELECT * FROM @Temp


 


For more information,Refer - 

http://technet.microsoft.com/en-us/library/ms173839.aspx 

http://blogs.msdn.com/b/youngjoo/archive/2006/04/17/577920.aspx 













SSRS - In Bar Charts , how to adjust the size of the bars

In this post , I will shown an example on how to adjust the size of the bars  in SSRS bar charts .

Below query is going to be my dataset


DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth


Create Column chart with Sales as summation value and Months as Category Groups 

  Try like below (as shown in below image) 

 Click on any bars on the chart - > Press F4 - > Properties - > CustomAttributes - > PointWidth - > Change according to your requirement



Report Preview : 


 

SSRS - In Charts , how to sort labels on X - axis (Horizontal axis)

In this post , I will shown an example on how to sort labels on the X-axis (Horizontal axis)  in SSRS charts .

Below query is going to be my dataset


DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth



Create Column chart with Sales as summation value and Months as Category Groups 





 Try like below (as shown in below images)

 Click on Chart area - > Category Groups - > Category Group Properties ... - >  Sorting - > Sort by 






 Report Preview :














 

SSRS - In Charts , how to display all labels on the X-axis (Horizontal axis)

In this post , I will shown an example on how to display all labels on the X-axis (Horizontal axis)  in SSRS charts .

Below query is going to be my dataset

DECLARE @SalesbyMonth TABLE (Months VARCHAR(20),Sales INT,MonthNo INT)
INSERT @SalesbyMonth SELECT 'January',1000,1
INSERT @SalesbyMonth SELECT 'February',100,2
INSERT @SalesbyMonth SELECT 'March',100,3
INSERT @SalesbyMonth SELECT 'April',200,4
INSERT @SalesbyMonth SELECT 'May',500,5
INSERT @SalesbyMonth SELECT 'June',800,6
INSERT @SalesbyMonth SELECT 'July',500,7
INSERT @SalesbyMonth SELECT 'August',100,8
INSERT @SalesbyMonth SELECT 'September',900,9
INSERT @SalesbyMonth SELECT 'October',600,110
INSERT @SalesbyMonth SELECT 'November',100,11
INSERT @SalesbyMonth SELECT 'December',100,12
SELECT * FROM @SalesbyMonth


Create Column chart with Sales as summation value and Months as Category Groups 



Try like below (as shown in below images)

Right-click on X-Axis - > Horizontal Axis Properties ... - > Axis Options - > Interval  - > 1 





Report preview :











Advertisements