?
Solved

how do i connect to database through a web service in crystal reports 11?

Posted on 2009-12-18
30
Medium Priority
?
450 Views
Last Modified: 2012-05-08
I would like to put mysql connection info into a web service and then have crystal read in the connection string for a portable data connection. Hiow can I do this without having to use a dataset and push the dataset in to crystal.
0
Comment
Question by:bschave2
  • 17
  • 11
  • 2
30 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 26083288
Since I am not a big fan of the "pull" method, I am curious why you would not want to "push" to the report?
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 26083515
I might be misunderstanding the question, so I apologize if that is so.

I think web services only became available in Crystal Reports 2008? But as TheLearnedOne says, that might be for a "push" method. In CR2008 you can connect to a web service just like an ODBC or other data source.
0
 

Author Comment

by:bschave2
ID: 26083789
what is considered a push method?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:bschave2
ID: 26083799
right, I tried to connect the web service to crystal only to get a schema error. I have sub reports in my reports quite often and really don't know how else to get this data portably without issues.
0
 

Author Comment

by:bschave2
ID: 26083810
i also tried the applylogon method at load time only to find out that you cannot override a db connection with an odbc connection. I don't know what other options I have accept a stupid dsn which isn't portable at all in the dev, qas, prod environment.
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 26083812
It might be possible that it just isn't supported in XI. They seemed pretty excited about web services as a data source being a part of CR2008.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 26083829
Oh, I forgot to mention that I don't believe that you will be able to connect to a web service from a Crystal Report (AFAIK).  How did you try to connect to the web service?

"Push" means that the application is responsible for getting the data, and sending it to the report.  "Pull" means that the report is responsible for connecting to the data source, and getting the data itself.  Crystal Reports can be pretty brain dead when it comes to the "pull" method, plus there isn't any way to debug the process.  If your application is getting the data, you would be able to debug that.
0
 

Author Comment

by:bschave2
ID: 26083830
well why add the connection type functionality with the web service if it's not going to work?
0
 

Author Comment

by:bschave2
ID: 26083840
i used the xml connection method, selected the web service option and then went through the wizard only to get a shema could not be found error.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 26083844
"As far as I know" AFAIK means that it may exist, but I haven't had any reason to find out (until now)...
0
 

Author Comment

by:bschave2
ID: 26083860
that's what I thought....the promblem with the push method is that I have to change the connection in the report everytime i move to another environment. My company has a dev, qas, and prod environment and the push method just isn't portable enough for that.
0
 

Author Comment

by:bschave2
ID: 26083880
i mean I have two options from what I can tell. option 1, write the data to a xml file, then pull in the data through the database file connection method or option 2 which I dispise, is change the dsn connection through each environment.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 26083890
With the "push" method, the connection would be stored in the app.config (or web.config).  With the "pull" method, you would need to change the connection in the report everytime.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 26083910
The web method can be created to return a DataSet/DataTable, which you can call SetDataSource with.
0
 

Author Comment

by:bschave2
ID: 26083911
i have my connection in the web.config already, but how do I get the report to read that in directly. I tried the run time method to overide the rdo connection method, but didn't seem to work.
0
 

Author Comment

by:bschave2
ID: 26083927
right, that's what I did. I created a web service to pull the data into a dataset, but crystal doesn't really support this method even though they have the option as a connection method under XML data source
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 26083993
1) What type of application are you working with?

2) Are you creating an instance of the report in code, and passing that to the ReportViewer?
0
 

Author Comment

by:bschave2
ID: 26084023
1. vb.net, crystal 10 in vb.net ide and crystal 11 supplemental to ide
2. yes, code attached
Dim myTableLogonInfo As New CrystalDecisions.Shared.TableLogOnInfo()
        Dim myConnectionInfo As New CrystalDecisions.Shared.ConnectionInfo()

        myConnectionInfo.IntegratedSecurity = True
        myConnectionInfo.AllowCustomConnection = True

        myConnectionInfo.ServerName = mySQLserver
        myConnectionInfo.DatabaseName = "etconlinebenefits"
        myConnectionInfo.UserID = "etcbenefits_pxy"
        myConnectionInfo.Password = passWord


        myreport.SetDatabaseLogon("etcbenefits_pxy", passWord, mySQLserver, "etconlinebenefits")
        Dim myTables As CrystalDecisions.CrystalReports.Engine.Tables

        myTables = myreport.Database.Tables

        For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables


            myTableLogonInfo.ConnectionInfo.IntegratedSecurity = True
            myTableLogonInfo.ConnectionInfo.AllowCustomConnection = True
            myTableLogonInfo = myTable.LogOnInfo
            myTableLogonInfo.ConnectionInfo = myConnectionInfo
            myTables.Item(0).TestConnectivity()
            myTables.Item(0).Location = myTable.Name

            'myTable.ApplyLogOnInfo(myTableLogonInfo)
            Dim tbl As String = myTableLogonInfo.TableName.ToString

        Next

        myreport.VerifyDatabase()
        CrystalReportViewer1.ReportSource = myreport
        CrystalReportViewer1.RefreshReport()

Open in new window

0
 

Author Comment

by:bschave2
ID: 26084044
i have tried numerouse variations of this, but what ends up happening with the code above is that it doesn't override the database connection that is in the report itself, but it doesn't error out wither. I read that because i am using the ODBC method, that this way will never work....RDO method i was using was connection string method.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 26084613
Here is an example:

1) 2008 Windows Forms application
2) Weather web service reference:  http://ws.cdyne.com/WeatherWS/Weather.asmx
3) Weather service class, which returns a list of WeatherForecast objects (shown below).
4) Report bound to WeatherForecast class (image shown).

Sample usage:
    Private Sub ShowWeather(ByVal zipCode As String)
        Dim result As List(Of WeatherForecast) = WeatherService.GetWeatherForZipCode(zipCode)
        Dim report As New WeatherReport()
        report.SetDataSource(result)

        Me.CrystalReportViewer1.ReportSource = report
        Me.CrystalReportViewer1.RefreshReport()
    End Sub

Imports CrystalReportDemo.WeatherData

Public Class WeatherService

    Private Shared m_weatherService As WeatherSoapClient = Nothing

    Shared Sub New()
        m_weatherService = New WeatherSoapClient("WeatherSoap")
    End Sub

    Public Shared Function GetWeatherForZipCode(ByVal zipCode) As List(Of WeatherForecast)
        Dim result As ForecastReturn = m_weatherService.GetCityForecastByZIP(zipCode)

        Dim list As New List(Of WeatherForecast)

        For Each entry As Forecast In result.ForecastResult

            Dim forecast As New WeatherForecast()

            forecast.City = result.City
            forecast.State = result.State
            forecast.WeatherStationCity = result.WeatherStationCity

            forecast.Forecast = String.Format("{0}:  {1}, precip: {2}%, low: {3} F, high: {4} F" & vbCrLf, _
                                entry.Date.ToShortDateString(), entry.Desciption, entry.ProbabilityOfPrecipiation.Daytime, _
                                entry.Temperatures.MorningLow, entry.Temperatures.DaytimeHigh)

            list.Add(forecast)
        Next entry


        Return list
    End Function

End Class

Public Class WeatherForecast
    Public City As String
    Public State As String
    Public WeatherStationCity As String
    Public Forecast As String

    Public Overrides Function ToString() As String
        Return Me.Forecast
    End Function

End Class

Open in new window

12-18-2009-5-45-09-PM.png
0
 

Author Comment

by:bschave2
ID: 26099098
what about if you have sub reports. would I need to create two classes with data for the main and subreport or one. Also can I pass in a dataset directly from the class instead of using a list or can I use a multidimensional array? I am also  passing a parameter "user id" for every record in the main report to  the subreport. Do I need to pass this paramter throught the class and if so, how do I do this?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 26100523
The objects that you bind the report to are "templates" that define the elements that you will pass into the report.  You can pass in anything that implements the properties that you use for the definition, including a DataSet/DataTable with the matching columns.  

In that case, I usually have a schema document (.xsd file), that defines the relationships between the two tables.  You can connect to the .xsd file through a new ADO.NET connection, where you can browse to the .xsd file.  Then, your web method can return a DataSet that you can pass to the report with SetDataSource call.
0
 

Author Comment

by:bschave2
ID: 26100602
can I not just use the push method at run time like so.... Instead of using a class to communicate with a webservice?
Dim myreport As ReportDocument = New ReportDocument()

conn.Open()

        strSql = "select user_id, name from emp_benefit_info where year_stamp=Year(CURDATE())"
        cmd = New MySqlCommand(strSql, conn)
        da = New MySqlDataAdapter(cmd)
        ds = New DataSet()
        da.Fill(ds, "users")

        strSql2 = "select user_id, dep_fname from dependents where year_stamp=Year(CURDATE())"
        cmd = New MySqlCommand(strSql2, conn)
        da = New MySqlDataAdapter(cmd)
        ds = New DataSet()
        da.Fill(ds, "dependents")

        myreport.Load(Server.MapPath("completed_report.rpt"))
        myreport.SetDataSource(ds.Tables("users"))
        myreport.Subreports.Item("dependents").SetDataSource(ds.Tables("dependents"))
        'report.Subreports.Item("dependents").SetParameterValue()
        CrystalReportViewer1.ReportSource = myreport
        CrystalReportViewer1.DataBind()
        'CrystalReportViewer1.RefreshReport()

        conn.Close()
        conn.Dispose()

Open in new window

0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 26104937
The "best" way to answer that question for yourself is to try.  I believe that it would work that way, but I haven't tried it.
0
 

Author Comment

by:bschave2
ID: 26105106
it's working, but the subreport parameter is not being passed. do you happen to know why?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 26105599
I don't see where you are passing in any parameter to the report, or I don't understand what you are asking...
0
 

Author Comment

by:bschave2
ID: 26108033
i have a parameter "user id" that should be read in the sub report from the main report. Do you know how I am suppsed to pass a value for every record in the main report to the sub report?
0
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 total points
ID: 26108147
0
 

Author Comment

by:bschave2
ID: 26131302
okay, so I used a dataset in crystal with a xml schema file that basically pushes the data into crystal. The listing style report didn't need a sub report, rather, needed a group with a query with all items in it and then it worked fine.

0
 

Author Closing Comment

by:bschave2
ID: 31667890
thanks for all of your help!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question