SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Writing custom code for reporting services


Writing custom code for reporting services

Author
Message
billy.knight
billy.knight
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 35
I am interested in writing code for my new reporting services projects which I have just gotten involved with at work. Can anyone tell what language and syntax is required? Also are there any function reference libraries available for me to look at to see what I can use?

Thanks

Billy Knight
stevefromOZ
stevefromOZ
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15844 Visits: 3757
Billy,

If you can expand a little on *why* you want to write code for this project, it might help us point you towards where to look as there are several areas in which you could write code.

Steve.
billy.knight
billy.knight
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 35
Hi Steve,

Thanks for responding. I have been creating reports in reporting services for a while now, and we have just upgraded to SSRS 2008. I am trying to beef up on a few areas in reporting servces where I have not needed to go before. I am now in charge of developing new reports for our clients to be used alongside the existing software we develop and provide for them. I am just interested in the particular language and syntax to use for writing code in reporting services. I take it you can put as many functions in there as you like? Can I use VBA??? Is it Microsoft Visual Basic 2008 like I use in SSIS script tasks?
stevefromOZ
stevefromOZ
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: Moderators
Points: 15844 Visits: 3757
Ok, sounds like you're interested just in adding custom code to your reports. I believe it's VB 2008, not VBA. MSDN has a section using code here.

Steve.
gsc_dba
gsc_dba
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2863 Visits: 2049
Sample to get you started:



Private bOddRow As Boolean
'*************************************************************************
' -- Display green-bar type color banding in detail rows
' -- Call from BackGroundColor property of all detail row textboxes
' -- Set Toggle True for first item, False for others.
'*************************************************************************
Function AlternateColor(ByVal OddColor As String, _
ByVal EvenColor As String, ByVal Toggle As Boolean) As String
If Toggle Then bOddRow = Not bOddRow
If bOddRow Then
Return OddColor
Else
Return EvenColor
End If
End Function


Then to use this on a table, add this to the expression on BackgroundColor:
=Code.AlternateColor("White", "Whitesmoke", TRUE)



gsc_dba
gayathridevi.msit
gayathridevi.msit
Mr or Mrs. 500
Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)Mr or Mrs. 500 (528 reputation)

Group: General Forum Members
Points: 528 Visits: 65
Hi,

Custom code u can use VB Functions... c# is not allowed.. but u can create DLL (Dynamic Link library) using C# or VB.net language..

in REPORT -> PROPERTIES -> CODE (u can write your code here)...

in REFERENCE TAB - > add dlls / references...

Regards,
Gayathri :-)
sushilb
sushilb
Old Hand
Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)Old Hand (312 reputation)

Group: General Forum Members
Points: 312 Visits: 39
Hi All
I wrote and Cutome code as below , that works well in development environment,
During Preview it gives me perfect Output,
but after the deployement it gives me error.
What must be fix to this. Please attached files where i added customcode and reference to System.data.dll



Function IsBudgetRevised(ByVal Proj_Id As Integer, ByVal Comp_Id As Integer, ByVal Sbu_Id As Integer, ByVal Contract_Type As Integer, ByVal FinYear as Integer) As String
Dim oConn As New System.Data.SqlClient.SqlConnection
oConn.ConnectionString = "Data Source=C-41396\C41396;" & _
"Initial Catalog=Finance_Dashboard; Integrated Security=true"
oConn.Open()
Dim oCmd As New System.Data.SqlClient.SqlCommand
oCmd.Connection = oConn
oCmd.CommandText = "Select Convert(Varchar(50),UpdatedBudget)+'-'+Convert(Varchar(50),LatestForecast) from tbl_CalculateUpdatedBudLatestFor" & _
" Where Proj_Id=@Proj_Id" & _
" and Comp_Id=@Comp_Id" & _
" and Sbu_Id=@Sbu_Id " & _
" and Contract_Type=@Contract_Type " & _
" and FinYear= @FinYear "
oCmd.Parameters.AddWithValue("@Proj_Id", Proj_Id)
oCmd.Parameters.AddWithValue("@Comp_Id", Comp_Id)
oCmd.Parameters.AddWithValue("@Sbu_Id", Sbu_Id)
oCmd.Parameters.AddWithValue("@Contract_Type", Contract_Type)
oCmd.Parameters.AddWithValue("@FinYear",FinYear)
Dim nRetVal As String = oCmd.ExecuteScalar()
oConn.Close()
If(nRetVal<>Nothing)
Return nRetVal
else
Return "0-0"
End If
End Function
Attachments
SSRS-Error.bmp (148 views, 2.00 MB)
AndyOwl
AndyOwl
SSC Veteran
SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)SSC Veteran (212 reputation)

Group: General Forum Members
Points: 212 Visits: 182
Hi

There's a tutorial on embedding code here (with a couple of full examples). I'm hoping to get round to writing one on custom assemblies next, to complete the picture!

Andy is a director of Wise Owl, a UK company providing training courses (and occasional consultancy) in SQL, Reporting Services, Integration Services and Analysis Services, as well as in many other Microsoft software applications. You can see more about Wise Owl's SQL Server training courses here.
benharlan333
benharlan333
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 93
gsc_dba (6/29/2010)


Private bOddRow As Boolean
'*************************************************************************
' -- Display green-bar type color banding in detail rows
' -- Call from BackGroundColor property of all detail row textboxes
' -- Set Toggle True for first item, False for others.
'*************************************************************************
Function AlternateColor(ByVal OddColor As String, _
ByVal EvenColor As String, ByVal Toggle As Boolean) As String
If Toggle Then bOddRow = Not bOddRow
If bOddRow Then
Return OddColor
Else
Return EvenColor
End If
End Function


Then to use this on a table, add this to the expression on BackgroundColor:
=Code.AlternateColor("White", "Whitesmoke", TRUE)



Regarding this example, I was able to get the following to work for me:

BackgroundColor code for the rows:

=iif(RowNumber(Nothing) Mod 2,"White","Yellow")



If you want to use Report Code, though, I did this:

Function fToggleColor(ByVal bToggle As Boolean) As String
If bToggle Then
Return "White"
Else
Return "Yellow"
End If
End Function



with this in BackgroundColor:

=Code.fToggleColor(iif(RowNumber(Nothing) Mod 2,True,False))



I would be careful with RowNumber because I'm not fully aware of its behavior. I believe RowNumber would take the group name if there were groups involved. Not sure.
benharlan333
benharlan333
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 93
billy.knight (6/24/2010)
I am interested in writing code for my new reporting services projects which I have just gotten involved with at work. Can anyone tell what language and syntax is required? Also are there any function reference libraries available for me to look at to see what I can use?

Thanks

Billy Knight


Visual Basic is the language, and here are some links that might be related to the reference libraries:

Custom Code and Assembly References
http://technet.microsoft.com/en-us/library/ms159238%28v=sql.105%29.aspx

Visual Basic Run-Time Library Members:
http://msdn.microsoft.com/en-US/library/c157t28f%28v=vs.90%29.aspx

I copied the 2008 or 2008 R2 links, so be sure to change it to whatever version of SQL Server you're using at the top of each page.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search