Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cedubose
Starting Member
22 Posts |
 Posted - 2006-09-12 : 14:31:14
|
| I am trying to put some embedded Visual Basic code into my report, using the "Code" section in the report properties.Here's the function I'm trying to use:Public Shared Function ReportTotal(ByVal IsUnitCost As Boolean,ByVal TotalDirectCosts As Double, ByVal SalaryBenefitsTotal As Double, ByVal IndirectRate As Double, ByVal SS_Screened As Integer, ByVal UnitRate As Double) As Double If IsUnitCost Then Return SS_Screened * UnitRate Else Return TotalDirectCosts + SalaryBenefitsTotal * IndirectRate End IfEnd FunctionSupposedly, after entering this you can access it in the expression for a field by typing the following: =Code.ReportTotal(... parameters ...)However, when I try this, "ReportTalk" does not show up in the intellisense after I type "Code.". If I type it in anyway, a red squiggly line shows up under it, and if I attempt to preview the report, I get the following error: "The definition of report [report name] is invalid. Exception of type 'Microsoft.ReportingServices.ReportProcessing.ReportProcessingException' was thrown."This is not a very helpful error message! I tried with and without "Public" and "Shared" and got the same thing.What am I doing wrong?Cynthia |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-12 : 14:43:24
|
Here is an example of what we have running production:embedded code:Public Function DisplayMessage(ByVal startDate As Date, ByVal endDate As Date) As String DisplayMessage = "" If startDate > endDate Then DisplayMessage = "End date must be greater than or equal to start date." & vbNewLine End If End Functionexample call:Code.DisplayMessage(Parameters!StartDate.Value, Parameters!EndDate.Value) Oh and forget Intellisense when using RS 2000, it doesn't exist. It makes coding very difficult. I haven't used RS 2005 enough to know if they added Intellisense in. Tara Kizer |
 |
|
|
cedubose
Starting Member
22 Posts |
Posted - 2006-09-12 : 18:58:39
|
| Thanks for the info, Tara.They do have intellisense in RS 2005, but for Code, the only things that come up are Equals, GetType and other generic things of that nature. It seems like "Code" is translated as something of type Object -- who knows. There is scant documentation. I'm assuming that if it could "see" my function, my function would come up in the intellisense.I notice that instead of "Return" you have "DisplayMessage=" in your function. I guess it's a question of whether this thing is using VB6 or VB.NET. I was assuming VB.NET. But anyway, I tried "ReportTotal=" and it still does not work! (I don't know why I put "ReportTalk" in my post -- it should be "ReportTotal"!)I find this little code box to be really really clunky. You can't precompile it to check for errors; there's no notion of scope or context. I mean -- can you have global variables? Can you have a namespace?At this point I have given up and gone back to the even clunker "IIf" -- which is really a mess, because I need to do several things with the value returned by this function, such as subtract things from it and multiply it by something else, and using a function would have been much cleaner.However, if anyone can come up with a solution, I'm still interested!Cynthia |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-12 : 19:03:59
|
| Yes it is VB.NET. I'd suggest picking up a book that deals with Reporting Services along with embedded code plus adding .NET assemblies to the reports. That's how we figured out how to do it for our reports. We use both embedded code like the example that I showed plus we also link .NET assemblies to our report for the more sophisticated code.Tara Kizer |
|
|
|
cedubose
Starting Member
22 Posts |
Posted - 2006-09-13 : 15:07:33
|
| I just wanted to report that someone at MSDN forums had the answer to this problem: the code is not compiled until the report is deployed. Thus, it will not work in preview, but it does work when the report is deployed.(It would be nice if there was some documentation of this!)Cynthia |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-09-13 : 15:08:48
|
| Interesting...I have not noticed this. I believe we can see both types of code in RS 2000. Are you using 2005?Tara Kizer |
|
|
|
|
|
|
|
|
Embedded code: what's the trick?