Cannot execute sub-report in list or table

I have a report that needs to invoke a sub-report for each row in a dataset.
I can execute the sub-report manually without problems. I can place the
sub-report on the main report and set the parameter to the first ID in my
recordset, and the report executes. If I place the sub-report in a list or
table, I get the following error:
An error has occurred while processing a sub-report. Details: Object
reference not set to an instance of an object
For both the table and list, I just added the container control to the main
report and just set the data source. I then add the subreport to the
container and hook up the parameters. I don't modify any other attributes
on the container. To experiment, I added a list to my main report and then
dragged the single-instance sub-report control that was working into the
container but I recieved the same error (I then drag the sub-report to
outside of the container and it renders just fine). I tried hard-coding the
parameters, but I continue to receive the error. I recieve this error in
both VS 2005 and SRS 2005 SP1.
Since the sub-report will execute outside the container, I believe I am
doing something incorrectly with having the sub-report in a container.
1) Does SRS support invoking a sub-report in a container, once per row?
2) If so, what is the recommend container? List, Table, or Matrix?
3) I found the log files to be of little help. I'm including the stack
trace for this error below. Any suggestions on how to troubleshoot this
issue?
Any help would be appreciated
- Chris
==============[ BEGIN STACK TRACE]=============================== w3wp!library!6!09/22/2006-15:50:27:: i INFO: Call to CreateReport( 'Trip',
'/BBSReporting', def)
w3wp!library!6!09/22/2006-15:50:27:: i INFO: Call to SetReportDefinition(
'/BBSReporting/Trip', def )
w3wp!library!7!09/22/2006-15:51:35:: i INFO: Call to GetPermissions:/
w3wp!library!7!09/22/2006-15:51:35:: i INFO: Call to GetSystemPermissions
w3wp!library!e!09/22/2006-15:51:40:: i INFO: Call to
GetPermissions:/BBSReporting
w3wp!library!e!09/22/2006-15:51:40:: i INFO: Call to GetSystemPermissions
w3wp!library!1!09/22/2006-15:51:43:: i INFO: Call to
GetPermissions:/BBSReporting/Trip
w3wp!library!1!09/22/2006-15:51:43:: i INFO: Call to GetSystemPermissions
w3wp!library!7!09/22/2006-15:51:50:: i INFO: Call to
GetPermissions:/BBSReporting/Trip
w3wp!library!1!09/22/2006-15:51:50:: i INFO: Call to GetSystemPermissions
w3wp!library!7!09/22/2006-15:51:52:: i INFO: Call to RenderFirst(
'/BBSReporting/Trip' )
w3wp!processing!7!9/22/2006-15:51:56:: e ERROR: An error has occurred while
processing a sub-report. Details: Object reference not set to an instance of
an object. Stack trace:
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessingContext.GetScopeValues(GroupingList
containingScopes, IScope containingScope)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateSubReportInstance(SubReport
subReport, ProcessingContext processingContext, Int32 index, IScope
containingScope, String& label)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateInstance(ReportItem
reportItem, Boolean setupEnvironment, Int32 index)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateInstances(ReportItemColInstance
collectionInstance, ReportItemCollection reportItemsDef)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.Merge.CreateInstances(ParameterInfoCollection
parameters, Boolean noRows)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.Merge.Process(ParameterInfoCollection
parameters, Boolean mergeTran)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeRICollection.CreateSubReportInstance(SubReport
subReport, ProcessingContext processingContext, Int32 index, IScope
containingScope, String& label)
w3wp!webserver!7!09/22/2006-15:51:57:: i INFO: Processed report.
Report='/BBSReporting/Trip', Stream=''Hello Chris,
Based on my research, the sub report could be in the data region such as
table, matrix and list.
The error message showes that the seb report could not render correctly.
Since you could render the sub report correctly out of the data region, it
may be related with your parameter.
Would you please let me know the parameter you use in the sub report?
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hello Wei,
The sub-report takes a single integer parameter named CompanyID. I have a
recordset that returns a CompanyID. I bound the recordset to the list
control. I have the Parameter Name set to CompanyID and the Paramter Value
set to =Fields!CompanyID.Value. I selected both using the Sub-Report
Properties dialog box, the Parameters tab, using the dropdown arrows.
To continue to troubleshoot the issue, I've done the following:
=========================* Added a textbox to the list that displays the CompanyID value
(=Fields!CompanyID.Value). It displays the expected value.
* I attempted to hard-code the parameter value using the following
syntax:
="100"
=100
100
=CInt(100)
=CInt("100")
* I'm using the following SQL to simplify the main report: SELECT 100 as
CompanyID
* I created a new Main Report that had no controls other than the
sub-report. I used the query above, binding the sub-report parameter to
=First(Fields!CompanyID.Value, "DataSet1"). The sub-report renders. If I
add a list control, bind it to the dataset, drag the sub-report into it, it
will not render. If I drag it out of the list control, it will render. I
didn't change a thing on the sub-report control.
Is there anyway to find out what *exactly* is NULL?
I appreciate your assistance with this.
- Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:dtkYvMG4GHA.4280@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Based on my research, the sub report could be in the data region such as
> table, matrix and list.
> The error message showes that the seb report could not render correctly.
> Since you could render the sub report correctly out of the data region, it
> may be related with your parameter.
> Would you please let me know the parameter you use in the sub report?
> Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Hello Chris,
Based on my test, I put the sub-report into the List control successfully.
I would like to send the sample to you. To get the sample, could you send
me a email to me? Please remove the ONLINE in my email address. I will keep
the information secure.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I sent my email address yesterday morning.
This afternoon I was able to recreate the problem with simple reports. If
necessary, I can send this to you.
Create three (3) reports, each with a single textbox with their name. I
named them "Report A", "Report B", and "Report C".
I add "C" to "B" as a sub-report. B renders fine - I see B and C.
I add "B" to "A" as a sub-report. A renders fine - I see A, B, and C.
I then added a dataset to A.
Declare @.Report table ( ID int )
INSERT INTO @.Report values (1);
INSERT INTO @.Report values (2);
SELECT * FROM @.Report
I added a List control and bound it to the dataset.
I then added a second instance of report B to the list.
At this point I get: Warning 1 [rsErrorExecutingSubreport] An error occurred
while executing the subreport 'subreport1': Object reference not set to an
instance of an object. [Path]\ReportA.rdl 0 0
I'm not accessing the database.
I'm not passing parameters.
If I add Report C to the list, it renders correctly, but B still does not.
This leads me to believe that there may be an issue with multiple levels of
nested reports within a list. This should be very easy to recreate. I can
zip my samples and send them to you if need be.
Visual Studio 2005
SQL Server Reporting Services 2005 SP1
Thanks,
Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:tFEApXf4GHA.4344@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Based on my test, I put the sub-report into the List control successfully.
> I would like to send the sample to you. To get the sample, could you send
> me a email to me? Please remove the ONLINE in my email address. I will
> keep
> the information secure.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Chris,
Thanks for the example of your issue.
I have reproduced it on my side.
I checked our internal database and found a workaround:
In "ReportB", you could add a List control and bound to a dataset which
have only one record. Then put the subreport for "ReportC" in the list
control.
Then in the "ReportA", you could add the subreport and put it in to the
list control without any error.
Please try this workaround on your side and let me know the result. Thank
you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei,
I was able to resolve this as you suggested, but I ended up having to modify
other reports as once I added the report in question to the "dummy" list
control, any subsequent sub-reports resulted in the same error.
Any ideas when this will be fixed? A hot fix, SP2, etc?
Thanks for your help with this.
- Chris
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:jceAud54GHA.2336@.TK2MSFTNGXA01.phx.gbl...
> Hello Chris,
> Thanks for the example of your issue.
> I have reproduced it on my side.
> I checked our internal database and found a workaround:
> In "ReportB", you could add a List control and bound to a dataset which
> have only one record. Then put the subreport for "ReportC" in the list
> control.
> Then in the "ReportA", you could add the subreport and put it in to the
> list control without any error.
> Please try this workaround on your side and let me know the result. Thank
> you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Chris,
Thanks for the update.
The workaround need to be done on all the sub-reports.
After contact with the product team, this issue will be fixed in SQL 2005
SP2.
If you have any questions, or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Chris,
If you have any question or concerns, please feel free to let me know.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

cannot create connection

I created a report using Report Designer. The Dataset is working fine. In
fact, the Data tab shows me the expected query result. But...
when going to the Preview tab, I get "Cannot create a connection to data
source <name> . Login failed for user 'sa'.
Further info:
1) if I go to Tools / Connect to database and enter the info in "Datalink
properties" (server name / specific user name passw / database name) and hit
Test connection, I get "Connection succeeded"
2) During creation of data source for a SQL Server machine on the network
when connecting with winNT security I get an error while trying to access the
database list.
What am I doing wrong ' THnksHi,
Go to your "Shared Data Sources" click on the datasource you use for your
reports, open the properties by double clicking on the "credientials" tab
enter your password again and save it.
It should work.
Amarnath
"julio" wrote:
> I created a report using Report Designer. The Dataset is working fine. In
> fact, the Data tab shows me the expected query result. But...
> when going to the Preview tab, I get "Cannot create a connection to data
> source <name> . Login failed for user 'sa'.
> Further info:
> 1) if I go to Tools / Connect to database and enter the info in "Datalink
> properties" (server name / specific user name passw / database name) and hit
> Test connection, I get "Connection succeeded"
> 2) During creation of data source for a SQL Server machine on the network
> when connecting with winNT security I get an error while trying to access the
> database list.
> What am I doing wrong ' THnks
>|||Amarnath,
thanks a lot. It works like a charm.
Julio
"Amarnath" wrote:
> Hi,
> Go to your "Shared Data Sources" click on the datasource you use for your
> reports, open the properties by double clicking on the "credientials" tab
> enter your password again and save it.
> It should work.
> Amarnath
>
> "julio" wrote:
> > I created a report using Report Designer. The Dataset is working fine. In
> > fact, the Data tab shows me the expected query result. But...
> >
> > when going to the Preview tab, I get "Cannot create a connection to data
> > source <name> . Login failed for user 'sa'.
> >
> > Further info:
> >
> > 1) if I go to Tools / Connect to database and enter the info in "Datalink
> > properties" (server name / specific user name passw / database name) and hit
> > Test connection, I get "Connection succeeded"
> >
> > 2) During creation of data source for a SQL Server machine on the network
> > when connecting with winNT security I get an error while trying to access the
> > database list.
> >
> > What am I doing wrong ' THnks
> >
> >

Cannot convert Varchar to Numeric issue

Hi again all,

I have a small issue. Here's an example dataset :

F1 F2 F3
1 0.58 Hi
2 0.70 Hello
3 Fail Bye
4 <Null> Hi

When I write this statement :

SELECT SUM(CONVERT(DECIMAL(16,8),F2)) MySum
FROM T1
WHERE IsNumeric(IsNull(F2,'X'))=1

I get "Cannot convert a Varchar value to Numeric" error. From what I
understand, it somehow tries to convert to a decimal(16,8) BEFORE filtering
the nulls and the non-numeric out. (Keep in mind that the actual table has
over 1.5Million records).

Any idea on how to get around that ?

Thanks,

MichelMichel (Michel@.askme.com) writes:
> F1 F2 F3
> 1 0.58 Hi
> 2 0.70 Hello
> 3 Fail Bye
> 4 <Null> Hi
> When I write this statement :
> SELECT SUM(CONVERT(DECIMAL(16,8),F2)) MySum
> FROM T1
> WHERE IsNumeric(IsNull(F2,'X'))=1
> I get "Cannot convert a Varchar value to Numeric" error. From what I
> understand, it somehow tries to convert to a decimal(16,8) BEFORE
> filtering the nulls and the non-numeric out. (Keep in mind that the
> actual table has over 1.5Million records).

This might help:

SELECT SUM(CASE WHEN isnumeric(F2)
THEN convert(decimal(16, 8)), F2
ELSE 0
END) MySUM
FROM T1
WHERE isnumeric(F2) = 1

Beware that isnumeric may give you false positives. Not all strings
that causes isnumeric to return 1 are convertible to decimal.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Cannot connection to Oracle DB

I create my datasource using the OLE DB data provider for oracle an when i
test the connection it is successful. When I create the dataset it cannot
connect to the database and says "System.Data.Oracle Client requires Oracle
Client Software version 8.1.7 or greater". I am using PL/SQL Developer that
says it is 9i compatible. Im so lost here and need help. Thanks.We had the Oracle client set up quite a while ago so forgot the steps to set
it up. We did run into some issues. If my memory serves me right, the default
9i installation allows the oci.dll to admin group only, and the web app
(ASPNET by default) or reporting service account (the one you chose during
setup) may not have the necessary permission to see this file. It is one
possible reason.
"Steve" wrote:
> I create my datasource using the OLE DB data provider for oracle an when i
> test the connection it is successful. When I create the dataset it cannot
> connect to the database and says "System.Data.Oracle Client requires Oracle
> Client Software version 8.1.7 or greater". I am using PL/SQL Developer that
> says it is 9i compatible. Im so lost here and need help. Thanks.
>|||Specifically, you may want to check this KB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;870668
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:9349134D-7982-4C1E-995B-3A761058945E@.microsoft.com...
> We had the Oracle client set up quite a while ago so forgot the steps to
> set
> it up. We did run into some issues. If my memory serves me right, the
> default
> 9i installation allows the oci.dll to admin group only, and the web app
> (ASPNET by default) or reporting service account (the one you chose during
> setup) may not have the necessary permission to see this file. It is one
> possible reason.
> "Steve" wrote:
>> I create my datasource using the OLE DB data provider for oracle an when
>> i
>> test the connection it is successful. When I create the dataset it
>> cannot
>> connect to the database and says "System.Data.Oracle Client requires
>> Oracle
>> Client Software version 8.1.7 or greater". I am using PL/SQL Developer
>> that
>> says it is 9i compatible. Im so lost here and need help. Thanks.|||Niether of those ideas worked. I do not have and oci.dll file on my system
and I am developing on an XP system not a 2003 server system.
"Robert Bruckner [MSFT]" wrote:
> Specifically, you may want to check this KB article:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;870668
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:9349134D-7982-4C1E-995B-3A761058945E@.microsoft.com...
> > We had the Oracle client set up quite a while ago so forgot the steps to
> > set
> > it up. We did run into some issues. If my memory serves me right, the
> > default
> > 9i installation allows the oci.dll to admin group only, and the web app
> > (ASPNET by default) or reporting service account (the one you chose during
> > setup) may not have the necessary permission to see this file. It is one
> > possible reason.
> >
> > "Steve" wrote:
> >
> >> I create my datasource using the OLE DB data provider for oracle an when
> >> i
> >> test the connection it is successful. When I create the dataset it
> >> cannot
> >> connect to the database and says "System.Data.Oracle Client requires
> >> Oracle
> >> Client Software version 8.1.7 or greater". I am using PL/SQL Developer
> >> that
> >> says it is 9i compatible. Im so lost here and need help. Thanks.
> >>
>
>|||If there is no oci.dll on your machine, then the Oracle client software is
not installed. It won't work without it. You can download the Oracle client
software from Oracle Technology Network (www.oracle.com/otn).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"steve" <steve@.discussions.microsoft.com> wrote in message
news:9BDF4BD5-DF7E-4134-8873-94FBFB5E35AD@.microsoft.com...
> Niether of those ideas worked. I do not have and oci.dll file on my
> system
> and I am developing on an XP system not a 2003 server system.
>
> "Robert Bruckner [MSFT]" wrote:
>> Specifically, you may want to check this KB article:
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;870668
>>
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
>> news:9349134D-7982-4C1E-995B-3A761058945E@.microsoft.com...
>> > We had the Oracle client set up quite a while ago so forgot the steps
>> > to
>> > set
>> > it up. We did run into some issues. If my memory serves me right, the
>> > default
>> > 9i installation allows the oci.dll to admin group only, and the web app
>> > (ASPNET by default) or reporting service account (the one you chose
>> > during
>> > setup) may not have the necessary permission to see this file. It is
>> > one
>> > possible reason.
>> >
>> > "Steve" wrote:
>> >
>> >> I create my datasource using the OLE DB data provider for oracle an
>> >> when
>> >> i
>> >> test the connection it is successful. When I create the dataset it
>> >> cannot
>> >> connect to the database and says "System.Data.Oracle Client requires
>> >> Oracle
>> >> Client Software version 8.1.7 or greater". I am using PL/SQL
>> >> Developer
>> >> that
>> >> says it is 9i compatible. Im so lost here and need help. Thanks.
>> >>
>>