How to use the custom code in the Report ?

Hi all,

I am trying to use the custom code in the report but I don't think I am understanding how this is being used.

I have a function to get starting months for a report parameter.
The function is below:-

--
Shared Function GetStartingMonths() as String
dim strDefault as string
dim CurrentMonth as String
Dim SqlString as String

'strDefault = Month(Now) & "/1/" & Year(Now)
CurrentMonth = "5/1/2002"
Do While CDate(CurrentMonth) <= Now
SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear"
CurrentMonth = dateadd("m",1,CDate(CurrentMonth))

if Cdate(CurrentMonth) = Now then
Exit Do
else
sqlString = SqlString & " Union "
end if

Loop

return SqlString

End Function

what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.

In the dataset, I had put the following expression
=Code.GetStartingMonths()

However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.

Am I doing something wrong here or is there a better way to doing this ?

Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?

thanks !

Bernard Ong

Hi,

Is it possible to specify custom code function as the source for the DataSet.Instead of this you can create a Store procedure which is giving the Starting months and use the SP as the source for DataSet.Use that dataSet to populate the parameter.

Hope this helps.

|||

Hi Mahima,

Thanks for the quick reply. At first I didn't know whether you could do this, but a reference to "HitchHiker's guide to reporting services 2000" said that you can do this. I followed exactly like how it was specified in the book.

I am actually abandoning this method at the moment, since I am creating a stored procedure that would retrieve the months and years. However I am having trouble retrieving the end dates instead.

Bernard

|||

After much tinkering, I got what I needed.
This is to get a certain date range (starting date of every month) stored procedure between the current and the specified date

declare @.Default datetime
set @.Default = '5/1/2002'
declare @.datestr varchar(20)

set @.datestr = Cast(DateName(m,@.Default) as varchar(15)) + ', ' + CAST(YEAR(@.Default) AS varchar(4))

IF OBJECT_ID('tempdb..#tmpdatetable') IS NOT NULL
Begin
--print 'exist'
drop table #tmpdatetable
end
Select @.Default as value, @.datestr as MonthYear into #tmpdatetable
set @.Default = DateADD(M,1,@.Default)

while @.Default < GetDate()
begin
set @.datestr = Cast(DateName(m,@.Default) as varchar(10)) + ', ' + CAST(YEAR(@.Default) AS varchar(4))
Insert into #tmpdatetable (value, MonthYear) Values(@.default, @.datestr)
set @.Default = DateADD(M,1,@.Default)
end

Select * from #tmpdatetable order by value desc
--

And the next stored procedure is to get all the ending dates between the two dates

declare @.Default datetime
set @.Default = '5/31/2002'
declare @.datestr varchar(20)

set @.datestr = Cast(DateName(m,@.Default) as varchar(15)) + ', ' + CAST(YEAR(@.Default) AS varchar(4))

IF OBJECT_ID('tempdb..#tmpdatetable') IS NOT NULL
Begin
--print 'exist'
drop table #tmpdatetable
end
Select @.Default as value, @.datestr as MonthYear into #tmpdatetable
set @.Default = datediff(d,1,dateadd(m,1,dateadd(d,1,@.default)))

while @.Default < GetDate()
begin
set @.datestr = Cast(DateName(m,@.Default) as varchar(10)) + ', ' + CAST(YEAR(@.Default) AS varchar(4))
Insert into #tmpdatetable (value, MonthYear) Values(@.default, @.datestr)
set @.Default = datediff(d,1,dateadd(m,1,dateadd(d,1,@.default)))
end

Select * from #tmpdatetable order by value desc

--drop table #tmpdatetable

Hope this would help someone.

|||

Hi,

I think what you are specifying about custom code is i.e using c# we can create custom assembly(class Library) in that custom assembly we can write a function which is returning value.And inorder to use that custom code you have to include that custom assembly in the report Server folder.And refer that custom addembly in your report by adding Report->Report Properties->click on References tab Browse to the report server folder where you placed the custom assembly specify class name and instance name.

And in the Dataset you can refer like this:

=Code.myReportsLibrary.GetDataBase(..).

|||

That is quite correct Mahima,

However, nothing says about having to use the code in the same report. Your post is right if I need to use the code somewhere else. However, it seems that if I want to use that code in the report itself, it should be alright by coding the function in to the Code Section of the report. Kinda like saying "Don't have to use assemblies if you are only using it within the same report".

your expression is definitely correct if I need to use another assembly, and hence why i am so confused from the book that if i put it straight into the code section of the report, I can use the code itself by typing.

=Code.GetDataBase()

I am wondering whether this is even possible now that you mentioned it, or whether I still have to use assemblies, regardless.

Thanks for the post though !

Bernard Ong