How to create report using WebService as DataSource.

Hi,

We created a simple web serivce having a Web Method which accepts a string parameter and returns "Hello World" concatenated with parameter value. We tested this web service by browsing it through IE and it was working fine by accepting a parameter and displaying the "Hello World " concated with the value given through the parameter when clicked on "Invoke" button.

We now created a Reporting Services 2005 project using BI having a report for which the data source will be the web service which is mentioned above. We defined a parameter for the report which will pass the value to the webservice and the report will display the returned value from the web service.

When we have done the report and executed by giving a value to the parameter and clicking on view report button it was displaying the "Hello World" but the parameter which we passed to the web service is not getting concatenated. When we tried to trace the application we could see that the value passed to the web service web method was comming as null. So kindly help me where I went wrong in developing such a kind of report which is not passing a parmeter to webservice.

Kindly provide us the solution in order to make this issue to be resolved.

Thanks in Advance.I can concur with VDeevi, I was able to recreate the exact same issue. In my case I'm passing parameters to a web service that is returning a dataset. When I set a default value in the webmethod it returns a dataset successfully. My problem is also that the parameters are not getting passed to the web service.

What I think is strange is that I've done the BOL sample with the SSRS web service Listchildren method and it works successfully. Does that mean that the problem is not in SSRS but in my implementation of the web service?

This works:

<WebMethod()> _
Public Function GetTestDataset() As System.Xml.XmlElement
Dim TestXDataset As New TestXDataset()
TestXDataset.Fill(1)
Dim xdd As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(TestXDataset)
Dim docElem As System.Xml.XmlElement = xdd.DocumentElement
Return docElem
End Function

This doesn't work:

<WebMethod()> _
Public Function GetTestDataset(ByVal ID As Integer) As System.Xml.XmlElement
Dim TestXDataset As New TestXDataset()
TestXDataset.Fill(ID)
Dim xdd As System.Xml.XmlDataDocument = New System.Xml.XmlDataDocument(TestXDataset)
Dim docElem As System.Xml.XmlElement = xdd.DocumentElement
Return docElem
End Function

|||

It's probably not your implementation, but in the parsing specifics of the SoapAction and Namespace.

For example, if your SoapAction is http://tempuri.org/HelloMyNameIs, we split it into http://tempuri.org and HelloMyNameIs throwing away the separating / character.

But if you look at the way webservice is defined, the namespace is actually http://tempuri.org/ - with a trailing /.

So in this case, when method name/namespace can not be correctly derived from SoapAction and when SoapAction can not be correctly derived from method name (in the above case we would get http://tempuri.org//HelloMyNameIs) you should use both <Method …/> and <SoapAction …/>, e.g.

<Query>
<Method Namespace="http://tempuri.org/" Name="HelloMyNameIs"/>
<SoapAction>http://tempuri.org/HelloMyNameIs</SoapAction>
</Query>

We are working on updates to the documentation.

|||Thanks Brain, it really helped us to solve this issue.

Thanks once again,|||Worked for me as well. Thanks a lot!

how to create performance statistics (was "A Challenging Question")

How does one figure out, I mean compare the results after some changes have been done to the SQL Server, I mean the method in which the comparision is done, lets say I have a query I ran it it gave me the results in 40 seconds , I ran it again this time it took 30 seconds, then I again ran it it took 35 seconds, I then created an index , this time the query ran in 30 seconds ... how does one compare such things , I mean i need to give stats as too what kind of performace has takern place ... please helpo , I need to knw as a DBa how would you convince your maanger that becasue of some changes the performance has improved, cause when you ask the users they say its ok , we dont see the differnece & stuff , please help.Add this code before ur query.

set statistics io on
go
set statistics time on
go


run ur query before making any changes,note down Execution Time, IO (specially logical read).
Make changes to ur query or add index,
remove data from cache and force sp to recomplie if u are using sp, by running below code

DBCC DROPCLEANBUFFERS
go
DBCC FREEPROCCACHE
go


add the first code I mentioned,run the sql statement again , note down execution time,IO.

compare the result.show to ur manager if it is improved,otherwise take holiday!

How to create locialized report?

I know the language displayed on reporting service is based on language preference on IE, so I have no porblem on that.

But, is there any method to control the report itself? Can I use a resource control thing? or anything else to control it? so that when IE is displaying Chinese big5, the reoprt header and column header can display Chinese big5, and it goes the same for English and so on

Many thx

Yes - You can change the STYLE properties for any element and set the culture field to whatever you want.

How to create an update or delete method in a strongly typed dataset?

Like the subject says, I'm using strongly typed datasets. I'm using to designer to create the datasets and the methods. I can select and insert, but I can't update or delete.

I right click on the adapter bar and select Add Query.

I sleect 'Use SQL Statements'

I select 'Update' (or 'Delete')

I get a sql statement pane containing the word 'Update' ('Delete') and asking 'What data should the table load?'

I can click on next, but anything else gives me errors. I'd list them, but I'm clearly doing something wrong and it's probably obvious.

Diane

Have you made sure that you have set a primary key for your table.|||

The table didn't have a primary key. I added one, but I still don't know how I'm supposed to use update and delete in a recordset.

Diane

|||

Hi Diane,

You can remove and re-add the TableAdapter. Set the SELECT command to select from that table which has primary key.

When you walk to the end of the wizard, it will show you that Update and Delete methods are generated. In this case, you will be able to Update and Delete through the TableAdapter.

|||

Update and Delete have been generated, I just don't know how to use them.

Diane

|||

Hi Diane,

When you have generated the Update and Delete methods, you can call them directly from an instance of TableADapter.

The Update method has several overloads. Some take a DataRow or a DataSet. They can update rows in batch. There is another overload that takes each field value seperately.

The Delete method takes arguments for all the field values, and it will check then delete the row.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

How to correctly propogate data back to the database

I have the following code but do not know the best way to return the updated
DataTable back to the database. I believe I can use the Update method of the
Data Adapter, BUT if true, I also believe I have to 'long-hand' write code
for each individual column data that's being added.....this seems a bit
daft considering that the data is already in the disconnected data table.
Have I lost the plot?? Based on the code below, what is the correct
approach?

Note: sqlcnn is defined at module level.

Public Sub AddRequest(ByVal Eng As String, ByVal Bran As String, ByVal Req
As String) Implements IHelpSC.AddRequest

Dim dtNew As New DataTable("dtNew")
Dim drNew As DataRow
sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)
sqlda.Fill(dtNew)

'Add and populate the new datarow with
'data passed into this subroutine

drNew = dtNew.NewRow
drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString
drNew("Engineer") = Eng
drNew("Branch") = Bran
drNew("Request") = Req
dtNew.Rows.Add(drNew)

End Sub

Hope one of you wizards can help.

Rgds....and Merry Christmas.

PhilPhil (Phil@.nospam.com) writes:
> I have the following code but do not know the best way to return the
> updated DataTable back to the database. I believe I can use the Update
> method of the Data Adapter, BUT if true, I also believe I have to
> 'long-hand' write code for each individual column data that's being
> added.....this seems a bit daft considering that the data is already in
> the disconnected data table.

Yes and no.

First, if memory serves, you don't have to write any extra code, if
you use the default .Update method on the data adapter, but it will
include all columns. But you are better of asking about that in group
like microsoft.public.dotnet.framework.adonet that is devoted to ADO .Net.
Or, instead of asking, just conduct an experiment.

Then comes the next question, whether you actually want it. For a
application of any size, it is usually best to perform all access to
SQL Server through stored procedure. In this way, users does not have
to have direct access to the tables, but only access to the stored
procedures. This makes a big difference for the security of the database.
In many shops, the DBA will not permit anything but stored procedures
anyway.

And if you use stored procedures, it follows by necessity that if you
add another column to a query, that you will have to add it to the
SelectCommand, UpdateCommand and InsertCommand of the DataAdapater as
well. And, yes, that means some extra overhead when you add a new
column, but it is not really a big deal.

Finally some notes about your code:

> sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)

This may be just an example, but permit me to point out that "SELECT *"
should never occur in production code. It may look convenient, but it
isn't. It gives you an extra overhead of retrieving columns, you don't
nead. And it makes it very difficult to find out if a column is actually
used or not, in case you are looking into to drop a column.

> drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString

I don't really know what happens in the end, but you should probably
pass dates as dates. If you format dates and passes them as strings to
SQL Server, they may not be understood by SQL Server, if there are
some unexpected dateformat and langauge settings.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

It sounds like you know what you are talking about! :-).....and you raise
some excellent points. I am extremely new to db programming and just finding
my feet. The comments you make about using 'SELECT *' and stored procedures
I have read in my 'programming databases using .NET ' book. THANKS for the
confirmation. I will eventually change to using stored procedures but I am
trying to pick up the basics regarding 'connected classes' and 'disconnected
classes', Data Adapters, Data Tables, Datasets etc etc etc.

I will take a look at the suggested newsgroups, thanks for your response.

All the best,

Phil

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95CB77CF3AB42Yazorman@.127.0.0.1...
> Phil (Phil@.nospam.com) writes:
> > I have the following code but do not know the best way to return the
> > updated DataTable back to the database. I believe I can use the Update
> > method of the Data Adapter, BUT if true, I also believe I have to
> > 'long-hand' write code for each individual column data that's being
> > added.....this seems a bit daft considering that the data is already in
> > the disconnected data table.
> Yes and no.
> First, if memory serves, you don't have to write any extra code, if
> you use the default .Update method on the data adapter, but it will
> include all columns. But you are better of asking about that in group
> like microsoft.public.dotnet.framework.adonet that is devoted to ADO .Net.
> Or, instead of asking, just conduct an experiment.
> Then comes the next question, whether you actually want it. For a
> application of any size, it is usually best to perform all access to
> SQL Server through stored procedure. In this way, users does not have
> to have direct access to the tables, but only access to the stored
> procedures. This makes a big difference for the security of the database.
> In many shops, the DBA will not permit anything but stored procedures
> anyway.
> And if you use stored procedures, it follows by necessity that if you
> add another column to a query, that you will have to add it to the
> SelectCommand, UpdateCommand and InsertCommand of the DataAdapater as
> well. And, yes, that means some extra overhead when you add a new
> column, but it is not really a big deal.
> Finally some notes about your code:
> > sqlda = New SqlDataAdapter("SELECT * FROM ActiveCalls", sqlcnn)
> This may be just an example, but permit me to point out that "SELECT *"
> should never occur in production code. It may look convenient, but it
> isn't. It gives you an extra overhead of retrieving columns, you don't
> nead. And it makes it very difficult to find out if a column is actually
> used or not, in case you are looking into to drop a column.
> > drNew("CallTime") = Format(Date.Now, "dd MMM yyyy").ToString
> I don't really know what happens in the end, but you should probably
> pass dates as dates. If you format dates and passes them as strings to
> SQL Server, they may not be understood by SQL Server, if there are
> some unexpected dateformat and langauge settings.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp