Embedded code: what's the trick?

I am trying to put some embedded Visual Basic code into my report (RS 2005), 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 If
End Function

Supposedly, 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?

Unfortunately, Custom code is not compiled until the report is published, so any custom methods will not show up in intellisense or be resolved (which results in the red underline) by the report designer.

If you are using RS 2005, then it should be evident in the exception message if the custom code generated the error. So, inorder to help you debug the issue I have a few questions:
What version of Reporting Services are you using?|||

Oh OK, you are right -- when I deploy the report, my code does work! Thank you very much.

You'd think there would be ONE mention of this little fact in SOME documentation, somewhere! It never would have occurred to me that the error was occurring only on preview.

In the SQL Server books online, the following is the entire entry for How to Add Code to a Report:

1. On the Report menu, click Report Properties.

Note: If the Report menu is not available, click within the report design area.

2. On the Code tab, in Custom code, type the code.

I kid you not. Anyway, thank you very much!

Embedded code: what's the trick?

I am trying to put some embedded Visual Basic code into my report (RS 2005), 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 If
End Function

Supposedly, 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?

Unfortunately, Custom code is not compiled until the report is published, so any custom methods will not show up in intellisense or be resolved (which results in the red underline) by the report designer.

If you are using RS 2005, then it should be evident in the exception message if the custom code generated the error. So, inorder to help you debug the issue I have a few questions:
What version of Reporting Services are you using?|||

Oh OK, you are right -- when I deploy the report, my code does work! Thank you very much.

You'd think there would be ONE mention of this little fact in SOME documentation, somewhere! It never would have occurred to me that the error was occurring only on preview.

In the SQL Server books online, the following is the entire entry for How to Add Code to a Report:

1. On the Report menu, click Report Properties.

Note: If the Report menu is not available, click within the report design area.

2. On the Code tab, in Custom code, type the code.

I kid you not. Anyway, thank you very much!

Embedded Code

I have read several posts but still cannot get to the answer and I
think it is something very basic and simple.
I have one report with embedded code that is running a sqlconnection,
sqlcommand, and sqldatareader to get two different fields from a query
to build a string to return back to the text field in the report. It
works just fine on my developer computer in the Preview mode but when I
deploy to the Report Server, the text field that is using the
referenced code displays a #Error.
I have seen that people pull this data out to a separate Assembly and
can reference it out in other reports, but I am just looking at making
this one report work with embedded code and it seems that I am
overlooking something.
Any help would be appreciated.
JoeI haven't done this but the issue is one of security. It works on your
development environment because you have rights to everything. I don't think
there is any way to do this as code behind. I think you have to create an
assembly so you can give rights to it.
What are you doing, it is possible that you can have another query for an
additional dataset or use a sub report and do this without code.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <joe.falk@.apptecusa.com> wrote in message
news:1125341439.906488.60130@.o13g2000cwo.googlegroups.com...
>I have read several posts but still cannot get to the answer and I
> think it is something very basic and simple.
> I have one report with embedded code that is running a sqlconnection,
> sqlcommand, and sqldatareader to get two different fields from a query
> to build a string to return back to the text field in the report. It
> works just fine on my developer computer in the Preview mode but when I
> deploy to the Report Server, the text field that is using the
> referenced code displays a #Error.
> I have seen that people pull this data out to a separate Assembly and
> can reference it out in other reports, but I am just looking at making
> this one report work with embedded code and it seems that I am
> overlooking something.
> Any help would be appreciated.
> Joe
>|||What I am trying to do is pull an integer and a true/false value from a
query that has a parameter on it. The report shares the parameter and
let's the report reader select from a list of names that the report
will return data. One of the data pieces I would like to display in a
text field with 4 columns and show a "*" if the T/F is true. I am using
a table object and have a text field with the code in the Report
Properties to pull that data as one block of data. I tried using a List
object but it would only display the data in one long strip instead of
columns. If I can't put code in the report, what good is that function?
Joe|||You can put code in behind the report. What you can't do is violate security
(which accessing the database directly does). If you want to go directly
against the database from code then you have to create an assembly and give
it the correct security assignments.
I think you can do what you want here by dropping a subreport into the field
instead of calling code.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <joe.falk@.apptecusa.com> wrote in message
news:1125353292.661926.156290@.g47g2000cwa.googlegroups.com...
> What I am trying to do is pull an integer and a true/false value from a
> query that has a parameter on it. The report shares the parameter and
> let's the report reader select from a list of names that the report
> will return data. One of the data pieces I would like to display in a
> text field with 4 columns and show a "*" if the T/F is true. I am using
> a table object and have a text field with the code in the Report
> Properties to pull that data as one block of data. I tried using a List
> object but it would only display the data in one long strip instead of
> columns. If I can't put code in the report, what good is that function?
> Joe
>|||It looks like creating a separate assembly is my best bet then. I wish
that it could be as easy as a subreport, but I cannot seem to get a
string of data rows to fill in anything more than a single column of
data and not a set of 4 or 5 columns by 4 or 5 rows. I have a finite
set of data that I am pulling "Select top 20" and no matter which
object I select, I cannot get it to display in multiple columns.
Thanks for your clarification on the reason why this particular code
will not work in embedded code.
Joe
Bruce L-C [MVP] wrote:
> You can put code in behind the report. What you can't do is violate security
> (which accessing the database directly does). If you want to go directly
> against the database from code then you have to create an assembly and give
> it the correct security assignments.
> I think you can do what you want here by dropping a subreport into the field
> instead of calling code.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Joe" <joe.falk@.apptecusa.com> wrote in message
> news:1125353292.661926.156290@.g47g2000cwa.googlegroups.com...
> > What I am trying to do is pull an integer and a true/false value from a
> > query that has a parameter on it. The report shares the parameter and
> > let's the report reader select from a list of names that the report
> > will return data. One of the data pieces I would like to display in a
> > text field with 4 columns and show a "*" if the T/F is true. I am using
> > a table object and have a text field with the code in the Report
> > Properties to pull that data as one block of data. I tried using a List
> > object but it would only display the data in one long strip instead of
> > columns. If I can't put code in the report, what good is that function?
> >
> > Joe
> >

Efficiently Inserting 1 Million records

I have an app that needs to insert 1 million records into a table. The tabl
e
is very basic thus far, with no triggers or indexes on it. The procedure
takes in excess of an hour, which I am willing to accept if I have to, but I
would like to know what tools are available to streamline this process.
I am using VB.Net code to do the work with basically 1 million loops and an
insert for each one. Are there great gains in terms having Stored Procs do
the work vs Native insert statements or any fancy database tuning techniques
.
I know that indexes add Select efficiency but what can be suggested for the
insert?
Thanks in advance for any assitance.
--
RyanRyan,
1. BULK INSERT
2. BCP (IN)
3. DTS
--See SQL Books Online for more information on each
HTH
Jerry
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Use a bulk insert, BCP
http://sqlservercode.blogspot.com/
"Ryan" wrote:

> I have an app that needs to insert 1 million records into a table. The ta
ble
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and a
n
> insert for each one. Are there great gains in terms having Stored Procs d
o
> the work vs Native insert statements or any fancy database tuning techniqu
es.
> I know that indexes add Select efficiency but what can be suggested for th
e
> insert?
> Thanks in advance for any assitance.
> --
> Ryan|||Inserting the rows one at time from a client application would be the
absolute slowest method of getting the work done.
You can create a DTS package to import the data. This might be the best
option if data transformations are involved and/or the process needs to be
scheduled as a job. Also, there is the bulk copy T-SQL command or DOS
executable command.
Importing and Exporting Data with DTS and BCP
http://www.microsoft.com/technet/pr...s/c07ppcsq.mspx
Using the DTS Import/Export Wizard
http://www.microsoft.com/mspress/bo...p/4885c.asp#126
Using the Bulk Copy Program (Bcp) and the BULK INSERT Transact-SQL Statement
http://www.microsoft.com/mspress/bo...p/4885e.asp#150
SQL Server 2000 Incremental Bulk Load Case Study
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Ryan" <weeims@.nospam.nospam> wrote in message
news:CE654CF4-AF12-453E-A552-D80336685BEB@.microsoft.com...
>I have an app that needs to insert 1 million records into a table. The
>table
> is very basic thus far, with no triggers or indexes on it. The procedure
> takes in excess of an hour, which I am willing to accept if I have to, but
> I
> would like to know what tools are available to streamline this process.
> I am using VB.Net code to do the work with basically 1 million loops and
> an
> insert for each one. Are there great gains in terms having Stored Procs
> do
> the work vs Native insert statements or any fancy database tuning
> techniques.
> I know that indexes add Select efficiency but what can be suggested for
> the
> insert?
> Thanks in advance for any assitance.
> --
> Ryan