Dataset′s

Im trying to make a dataset from a stored procedure. My problem is that RS wont use all of the rows returned.

My SP returns 9 rows. Some of them are dublicates and some ar on of a kind. And it seeems that RS just displays one of each kind. No mater how many there are i the resultset.

Ive read that RS cant handle more than one resultset, but i think that the result returned is just one resultset.

If a run the SP in the Data tab it works just fine, it displays all rows. But when im using it in previewmode its not.

The SP is built around a cursor. It makes a temporary table with info. Then just JOINs it with a real table. Would not that create a SINGLE resultset witch RS should read?

Thanks in advanceIf it is working in preview, you are doing something in the layout of your report. Maybe you are grouping by some repeating value which will cause for the number of rows to be reduced. Try to start with a blank slate and drop the fields in the detail row of a table.|||

Thanks, it workt. I started of with a blank report. You where right, it was somehing in the layout section that flawed.

Dataset Filtering Not Working

I am trying to filter data at the dataset level after it is returned.

However, the filters tab on in the Dataset window does not appear to work correctly.

I have tried the following expressions:

Expression Operator Value

=(Fields!IdLog.Value = Parameters!Id_Log.Value) = =True

=Fields!IdLog.Value = =Parameters!Id_Log.Value

When I rerun my dataset query, the entire dataset is returned, instead of the filtered dataset. This same behavior occurs if I try to run the report itself.

I do not understand why the filtering does not occur.

Any help would be greatly appreciated.

To get maximum performance with filtering its the best to build the parameter into the SQL-Query:

="select your_colums from your_table where ID='" & Parameters!Id_Log.Value & "'"

I never tried something different, because its waste of resources to retrieve 1000records from a database and filter out 999 of them..

|||

BenniG,

Thank you for your comment. However, I am try to filter the results that are returned from a Web Service, and therefore, I cannot specify the SQL Statement used to query the database.

|||

I tried the filter-tab:

=Fields!ID.Value = =Parameters!ID.Value

works if both fields have the same datatype, if one is a string and one an integer I get an error.. Maybe you have to change the datatype for the Parameter?
I think the filter has no effect on the !-Icon in datasets, but in my case it worked in preview mode..

Dataset Confusion in Reporting Services 2005

I'm creating a report that gets its data from a dataset returned from a
webservice call. I've supplied the URL for the webservice and in my Dataset
dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
Query String as follows:
<Query>
<SoapAction>Federation/GetAll</SoapAction>
<ElementPath />
</Query>
Where: 'Federation' is my namespace and 'GetAll' is the method which gets
consumed from the webservice.
When I click the Run button on the report Design screen, the webservice is
called correctly but what I get back is one row for each field in my dataset
instead of one row for each record in the dataset. IOW, my dataset has 20
fields in it and what I get returned is a table with 20 records in it, one
for each field, instead of the actual data.
Can someone tell me what I'm doing wrong?
Amos."Amos Soma" wrote:
> I'm creating a report that gets its data from a dataset returned from a
> webservice call. I've supplied the URL for the webservice and in my Dataset
> dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> Query String as follows:
> <Query>
> <SoapAction>Federation/GetAll</SoapAction>
> <ElementPath />
> </Query>
> Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> consumed from the webservice.
> When I click the Run button on the report Design screen, the webservice is
> called correctly but what I get back is one row for each field in my dataset
> instead of one row for each record in the dataset. IOW, my dataset has 20
> fields in it and what I get returned is a table with 20 records in it, one
> for each field, instead of the actual data.
> Can someone tell me what I'm doing wrong?
> Amos.
I am having the same problem. Any answers yet? I am messing around with the
Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
XML & Web Services the going is slow.|||Same problem!....please help!
Tito Perez wrote:
> "Amos Soma" wrote:
> > I'm creating a report that gets its data from a dataset returned from a
> > webservice call. I've supplied the URL for the webservice and in my Dataset
> > dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> > Query String as follows:
> >
> > <Query>
> > <SoapAction>Federation/GetAll</SoapAction>
> > <ElementPath />
> > </Query>
> >
> > Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> > consumed from the webservice.
> >
> > When I click the Run button on the report Design screen, the webservice is
> > called correctly but what I get back is one row for each field in my dataset
> > instead of one row for each record in the dataset. IOW, my dataset has 20
> > fields in it and what I get returned is a table with 20 records in it, one
> > for each field, instead of the actual data.
> >
> > Can someone tell me what I'm doing wrong?
> >
> > Amos.
> I am having the same problem. Any answers yet? I am messing around with the
> Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
> XML & Web Services the going is slow.|||Can someone please help with this?|||Try this... I was having the same problem but found another example. Here is
what I did and it worked for me - Notice the changes to the "ElementPath".
<Query>
<SoapAction>Federation/GetAll</SoapAction>
<ElementPath
IgnoreNamespaces="True">GetAllResponse/GetAllResult/diffgram/NewDataSet/Table</ElementPath>
</Query>
Note: If you only want certain columns returned, add your column name(s)
seperated by commas in brackets "{} " after "Table" (eg. <ElementPath
IgnoreNamespaces="True">GetAllResponse/GetAllResult/diffgram/NewDataSet/Table{column1_name,column2_name}</ElementPath>)
Hope this helps!
"Kays" wrote:
> Same problem!....please help!
> Tito Perez wrote:
> > "Amos Soma" wrote:
> > > I'm creating a report that gets its data from a dataset returned from a
> > > webservice call. I've supplied the URL for the webservice and in my Dataset
> > > dialog (in Visual Studio), I've indicated the Comnand Type as 'Text' and my
> > > Query String as follows:
> > >
> > > <Query>
> > > <SoapAction>Federation/GetAll</SoapAction>
> > > <ElementPath />
> > > </Query>
> > >
> > > Where: 'Federation' is my namespace and 'GetAll' is the method which gets
> > > consumed from the webservice.
> > >
> > > When I click the Run button on the report Design screen, the webservice is
> > > called correctly but what I get back is one row for each field in my dataset
> > > instead of one row for each record in the dataset. IOW, my dataset has 20
> > > fields in it and what I get returned is a table with 20 records in it, one
> > > for each field, instead of the actual data.
> > >
> > > Can someone tell me what I'm doing wrong?
> > >
> > > Amos.
> >
> > I am having the same problem. Any answers yet? I am messing around with the
> > Element Path attribute, but since the BOL is pretty thin, and I'm a newbie to
> > XML & Web Services the going is slow.
>

Datagrid/Stored Procedure Issue

I have a developer that has databound datagrids as follows:
Datagrid.datasource= DataSet (yes, the compiler allows this)
The dataset is returned from stored procedures. Each of the sps return
a single recordset, but periodically we are seeing some
nondeterministic behavior where the code for the datasource above is
causing a runtime error. More than one recordset is being returned for
some reason.
Of course, the easiest way to fix this is to have the developer change
code to
Datagrid.datasource = DataSet.Table("Some Table or Index"),
but is there are anything that can be done from a SQL Server
standpoint, that will only return one recordset ?
TIAMJKulangara wrote:
> I have a developer that has databound datagrids as follows:
> Datagrid.datasource= DataSet (yes, the compiler allows this)
> The dataset is returned from stored procedures. Each of the sps return
> a single recordset, but periodically we are seeing some
> nondeterministic behavior where the code for the datasource above is
> causing a runtime error. More than one recordset is being returned for
> some reason.
> Of course, the easiest way to fix this is to have the developer change
> code to
> Datagrid.datasource = DataSet.Table("Some Table or Index"),
> but is there are anything that can be done from a SQL Server
> standpoint, that will only return one recordset ?
> TIA
The usual cause of extra resultsets is the lack of a "SET NOCOUNT ON"
statement in the stored procedure. This statement tells the query engine to
suppress the informational "x rows affected" messages that you see when
running queries in QA. These messages get sent as extra resultsets.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks Bob. I checked each of the sps called and they all have "SET
NOCOUNT ON". The strange thing here is that we don't always get an
error. Most of the time (90%) applications rnus without a problem.|||"MJKulangara" <RutgersDBA@.gmail.com> wrote in message
news:1140627492.706939.31610@.z14g2000cwz.googlegroups.com...
>I have a developer that has databound datagrids as follows:
> Datagrid.datasource= DataSet (yes, the compiler allows this)
> The dataset is returned from stored procedures. Each of the sps return
> a single recordset, but periodically we are seeing some
> nondeterministic behavior where the code for the datasource above is
> causing a runtime error. More than one recordset is being returned for
> some reason.
> Of course, the easiest way to fix this is to have the developer change
> code to
> Datagrid.datasource = DataSet.Table("Some Table or Index"),
> but is there are anything that can be done from a SQL Server
> standpoint, that will only return one recordset ?
> TIA
Run the stored procedure in Query Analyser (with parameters that cause the
error in your page).
Check if any messages are generated.
I had a similar problem with a stored procedure and found that it was
because of null values in aggregate functions.
Adding SET ANSI_WARNINGS OFF to the procedure solved the problem.
Note: make sure ANSI_WARNINGS is ON in QA options before doing the test.