Feeds:
Posts
Comments

Archive for October, 2011

I have found the below  somewhere in the internet but I don’t remember where:
–>> Line Feed
— Newlines in text boxes
If you’re using an expression to build up a paragraph or two of text in a text box and need a new line, use this:
VBCRLF (Visual Basic Carriage Return Line Feed for those who’re interested!)

E.g. =”This is line 1″ + VBCRLF + “This isn’t” will produce:
This is line 1
This isn’t

Gotcha: If VBCRLF is the first or last thing in your text box, Reporting Services will ignore it (I have no idea why!). Make sure you put in one extra VBCRLF at the beginning or end if you’re using them there.
—- >>>

– – Checkboxes
I would have thought it would be fairly common to want to display a check box on a report which is checked based on a value in the dataset but it would appear that it’s not so common as there is no control provided to handle this. If your client, like mine, really wants a checkbox displayed on a report, you’ll need to use wingdings. Yes, you heard right, add a small text box to your report, set the font to wingdings and use something like the following expression:
=iif(Fields!BookingMade.Value = “1”,chr(0254),”o”)

Checked Box > Wingding 2 =chr(82)
Unchecked Box > Wingding 2 =chr(163)

Read Full Post »

-- This snippet lists all given the field vlaues so that they straight away be copied and put into a string.

 

-- i.e.  set @variable = 'a,b,c,d,e' -- the text inside the single quote is generated

 

-- Change the below sql in the singel quote to replace it with the required field and table name.

 

-- Delete or change the top 100 to the number of values you want it returned
-- Also remember to be inside the server and the database the table is in.
-- Go to the "MESSAGES" tab and copy the piece of text into the clipboard for later use.

declare @passed varchar(max)
set @passed = 'SELECT top 100 [place the field name here] from [place the table name here]'

Declare @fetch table
(
 id int identity,
 store varchar(max)
)

if left(ltrim(@passed), 6) = 'select'
begin
 insert into @fetch
 exec (@passed)
end

select * from @fetch

 

declare @id int, -- Pointer
 @idMax int, -- Scroller
 @pipe varchar(max) -- Final string to return

select @id = 0, @idMax = max(id), @pipe = ''
from @fetch
while @id < @idMax -- whenever @id would be = @idMax; the loop would break
begin
 set @id = @id + 1 -- Start Scrolling
 select @pipe = case when @id < @idMax then @pipe + store + ', '
 else @pipe + store end -- We dont want a comma at the end of the string
 from @fetch where id = @id -- tells which record to fetch
end

print @pipe

-- Please check the screen shot below:
-- I have used the below query:
-- SELECT name FROM dbo.sysobjects WHERE type = 'u'

I have also made an excel file that does a similar job but it is limited to the number of rows. Just copy and past the the fields values in the left most column and press ctrl + d

Click the link below to download it:

Link to download

Read Full Post »

-- For use with usp_getMonth (which can be found in the Second Option in the post - http://wp.me/pAchH-4l)

-- The below can be used when in SSRS you only want to show the months which are available for the users to select. This requires the the a parameter which would return the year selected

=iif(Parameters!Year.Value = year(today()), month(today()), 12)

Read Full Post »

— For use with usp_getMonth (which can be found in the Second Option in the post – http://wp.me/pAchH-4l)

— The below can be used when in SSRS you only want to show the months which are available for the users to select. This requires the the a parameter which would return the year selected

=iif(Parameters!Year.Value = year(today()), month(today()), 12)

Read Full Post »

-- Returns the Week Day Names and its correponding Week Number (taking Sunday as 1)
--==================================================
--alter proc dbo.usp_getWeekDayNames
--as
SET DATEFIRST 1 -- Monday is set to be the first day of the week

DECLARE @c INT

SET @c = 1

DECLARE @WeekDays TABLE (
	WeekDayNumber INT
	,WeekDayName VARCHAR(12)
	)

WHILE (@c & lt;= 7)
BEGIN
	INSERT @WeekDays
	SELECT datepart(weekday, dateadd(day, @c, getdate())) WeekDayNumber
		,datename(weekday, dateadd(day, @c, getdate())) WeekDayName

	SET @c = @c + 1
END

SELECT *
FROM @WeekDays
ORDER BY 1

Read Full Post »

--Description:    Returns the Start Time and Stop Time columns from udf_EvenlySlice24hours in minutes (this udf can also be found in my posts)
--alter proc dbo.usp_getTimeSlicedInMin (@Interval int)
--as
---- ==== Test Data =====
DECLARE @Interval INT

SET @Interval = 60

---- ========================
SELECT left(StartTime, len(StartTime) - 3) StartTime
	,left(StopTime, len(StopTime) - 3) StopTime
	,LEFT(StartTime, 2) * 60 + RIGHT(LEFT(StartTime, 5), 2) StartTimeInMin
	,LEFT(StopTime, 2) * 60 + RIGHT(LEFT(StopTime, 5), 2) + 1 StopTimeInMin -- one has to be added is added (see notes below)
FROM udf_EvenlySlice24hours(@Interval)
	/*  ====== Notes

1 is added to StopTimeInMin so that in the calling SP  the parameter  passed by the user need can include
the last minute till which he wants to end time this is done for user friedliness, so for example  if the user wants to end the timer at 5pm then
he shoud put in 1020 = (17 * 60) later on in the code 3 mc should deducted like below
@StopTime = DATEADD(ms,-3, dateadd(minute, [last minute till which he wants to end time], @StopTime)) -- Subtract -3 ms to get the last time

*/

Read Full Post »

--WARNING! ERRORS ENCOUNTERED DURING SQL PARSING! -- There are two SPs here
--First option:    Returns all month name and number
--==================================================
--create proc dbo.usp_getMonthNameAndNumber
--as
DECLARE @allMonths TABLE (
	MonthNumber INT identity
	,MonthName VARCHAR(10)
	)
DECLARE @count INT

SET @count = 0

WHILE (@count & lt;12)
BEGIN
	INSERT @allMonths
	SELECT dateName(month, dateadd(month, @count, '1 Jan 2010'))

	SET @count = @count + 1
END

SELECT *
FROM @allMonths
ORDER BY 1

--Second option:    Returns Month name and number upto the passed month number
--    (rows are restricted to the passed month)
--==================================================
--create proc dbo.usp_getMonth (@RestrictToMonthNumber int = null)
--as
---------====== Test Values ======
DECLARE @RestrictToMonthNumber INT

SET @RestrictToMonthNumber = 6

-------=============================
-- Note: If null is sent then all months are returned
DECLARE @allMonths TABLE (
	MonthNumber INT identity
	,MonthName VARCHAR(10)
	)

IF @RestrictToMonthNumber IS NULL
	SET @RestrictToMonthNumber = 12

DECLARE @count INT

SET @count = 0

WHILE (@count & lt;@RestrictToMonthNumber)
BEGIN
	INSERT @allMonths
	SELECT dateName(month, dateadd(month, @count, '1 Jan 2010'))

	SET @count = @count + 1
END

SELECT *
FROM @allMonths
ORDER BY 1 

Related post ( used IN SSRS ) :
http: / / wp.me / pAchH – 4 A

Read Full Post »

Older Posts »