Datasource Webservice

I test Webservices with the SSRS 2005 and do not get ahead.

Connectionstring: http://www.webservicex.net/WeatherForecast.asmx
Query String: <Query><SoapAction>http://www.webservicex.net/GetWeatherByPlaceName</SoapAction></Query>

I get an error. What is wrong?
Additionally I would like to use parameters. For example Houston, Dallas, New York.

Can someone send an example with this Web service to me?

Willfried

I just tried this using IE and got:
"The underlying connection was closed: The server committed an HTTP protocol violation."

You should contact the author of the web service to request a fix.

-Lukasz

datasource connection error

have developed a report on a test windows server machine in SSRS 2005 which
I can access from i./e. http://localhost/reports
DataSourcce is accessed using a sql login
My colleague on his machine can not access. His error is:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'MarketAnalytics'.
(rsErrorOpeningConnection)
For more information about this error navigate to the report server on the
local server machine, or enable remote errorsCheck the SRS error logs they should have a better error for you. They can be
found in: C:\Program Files\Microsoft SQL Server\MSSQL.#\Reporting
Services\LogFiles.
--
SQL Server Developer Support Engineer
"arkiboys2" wrote:
> have developed a report on a test windows server machine in SSRS 2005 which
> I can access from i./e. http://localhost/reports
> DataSourcce is accessed using a sql login
> My colleague on his machine can not access. His error is:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'MarketAnalytics'.
> (rsErrorOpeningConnection)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
>

Dataset Question

I have a dataset which is calling a stored proc in my SSRS 2005 report. The stored proc just returns one value which is the result of a SUM in my stored proc's sql query.

When I reference that field from my dataset, SSRS puts SUM() around the fieldname. This is causing an issue because I have a complicated expression in my table footer and need to just reference that field by it's name without SUM.

Why is it putting SUM around my one field? If I take out the SUM around that field, SSRS can't recognize it as a valid dataset field anymore

Instead of using it from dataset option use Fields(Datasource) option in the expression|||I'm not quite sure I understand why you are putting Datasource as the field in parenthesis unless there's more behind that...which is not obious to me in your syntax example. Can you give me an example in full syntax?sql

Databound image in report header

After finding out that SSRS doesn't directly support databound items in the header or footer, I found the workaround from Microsoft yesterday at http://msdn2.microsoft.com/en-us/library/ms159677.aspx. The problem is that their solution only works for the first page of the report, so it's basically useless! Because this is an image, it also means the workaround of using a parameter doesn't work, either.

Does anybody have any advice? Is there a solution to this problem?

One approach to accomplish this is to create a custom function that queries the database for the image, caches it and then returns it for use in the Value property of the database image in the report header.

Private m_headerImage As Object

Public Function GetHeaderImage() As object
' be sure to add appropriate error handling

If m_headerImage = Nothing Then
Dim adapter as new System.Data.SqlClient.SqlDataAdapter("Select ThumbnailPhoto From Production.Productphoto", "Data Source=localhost; Initial Catalog=AdventureWorks; Integrated Security=SSPI;" )
Dim ds as new System.Data.DataSet()
adapter.Fill(ds)
m_headerImage = ds.Tables(0).Rows(0)(0)
End If

Return m_headerImage

End Function

Set the image Value property to

=Code.GetHeaderImage()

Also, you will need to add a references to the System.Sql.Data and System.Xml assemblies.

More information on cutom code:
http://msdn2.microsoft.com/en-us/library/ms155798.aspx

Ian|||

Mark, which version of SSRS are you using?

Note: the parameter approach for databound images in page header/footer works correctly with RS 2005 SP1.

-- Robert

|||Robert,

I am using RS 2005 SP1; I just assumed that the parameter approach wouldn't work for binary image data due to the size factor. I'll have to try that out.

-Mark
|||Robert,

How exactly do I get this to work using the parameter approach? I've tried everything I can think of. It works for text, but not images.

Thanks,
Mark
|||

Mark, I attached a small sample report (based on the Northwind database) that shows how to do it.

Important parts:
* the report parameter default value is based on a calculated field (ImageBase64) from the dataset
* the calculated field is converting the image data field into a base-64 encoded string, e.g. =Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))
* the image report item takes the value of the Image report parameter and converts it back from a base-64 string to a byte array:
=Convert.FromBase64String(Parameters!Image.Value)

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>02f7d41c-e5a6-4015-9bd4-6aa837db9bb6</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Image">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DataSet1</DataSetName>
<ValueField>ImageBase64</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Image</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>1.5in</Height>
</Body>
<PageHeader>
<ReportItems>
<Image Name="image2">
<Sizing>AutoSize</Sizing>
<MIMEType>image/bmp</MIMEType>
<Width>2.875in</Width>
<Source>Database</Source>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
</Style>
<Value>=Convert.FromBase64String(Parameters!Image.Value)</Value>
</Image>
</ReportItems>
<Height>2.625in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageHeader>
<rd:ReportID>14f7b895-1a77-410d-ae9c-58cfb7418d60</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from employees</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="LastName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>LastName</DataField>
</Field>
<Field Name="FirstName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>FirstName</DataField>
</Field>
<Field Name="Title">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Title</DataField>
</Field>
<Field Name="TitleOfCourtesy">
<rd:TypeName>System.String</rd:TypeName>
<DataField>TitleOfCourtesy</DataField>
</Field>
<Field Name="BirthDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>BirthDate</DataField>
</Field>
<Field Name="HireDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>HireDate</DataField>
</Field>
<Field Name="Address">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Address</DataField>
</Field>
<Field Name="City">
<rd:TypeName>System.String</rd:TypeName>
<DataField>City</DataField>
</Field>
<Field Name="Region">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Country</DataField>
</Field>
<Field Name="HomePhone">
<rd:TypeName>System.String</rd:TypeName>
<DataField>HomePhone</DataField>
</Field>
<Field Name="Extension">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Extension</DataField>
</Field>
<Field Name="Photo">
<rd:TypeName>System.Byte[]</rd:TypeName>
<DataField>Photo</DataField>
</Field>
<Field Name="Notes">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Notes</DataField>
</Field>
<Field Name="ReportsTo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ReportsTo</DataField>
</Field>
<Field Name="PhotoPath">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PhotoPath</DataField>
</Field>
<Field Name="ImageBase64">
<Value>=Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))</Value>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

Robert,

I am in the same fix regarding the databound image situation. I've used the hidden text box field inside the body of the report that converts the image (a field in the dataset) to a string and then the actual image in the page header references the hidden text box. This works great in the preview of BI Studio. It also works when the report is exported to Excel. However, when exported to PDF, the image is not shown. Is this even possible?

Regarding the solution above, I tried your suggestion. However, I'm back to the situation where a "fields cannot be used in report parameter expressions". Is there something I'm missing in your explanation above? This particular report will eventually become an "rdlc" report on a remote site so I'm limited as to what I can do in the BI environment.

Any help or direction would be appreciated! I've spent way too much time on what should be a simple task, in my opinion, to implement.

Also, do you still have the sample report?

Thanks,

John

Databound image in report header

After finding out that SSRS doesn't directly support databound items in the header or footer, I found the workaround from Microsoft yesterday at http://msdn2.microsoft.com/en-us/library/ms159677.aspx. The problem is that their solution only works for the first page of the report, so it's basically useless! Because this is an image, it also means the workaround of using a parameter doesn't work, either.

Does anybody have any advice? Is there a solution to this problem?

One approach to accomplish this is to create a custom function that queries the database for the image, caches it and then returns it for use in the Value property of the database image in the report header.

Private m_headerImage As Object

Public Function GetHeaderImage() As object
' be sure to add appropriate error handling

If m_headerImage = Nothing Then
Dim adapter as new System.Data.SqlClient.SqlDataAdapter("Select ThumbnailPhoto From Production.Productphoto", "Data Source=localhost; Initial Catalog=AdventureWorks; Integrated Security=SSPI;" )
Dim ds as new System.Data.DataSet()
adapter.Fill(ds)
m_headerImage = ds.Tables(0).Rows(0)(0)
End If

Return m_headerImage

End Function

Set the image Value property to

=Code.GetHeaderImage()

Also, you will need to add a references to the System.Sql.Data and System.Xml assemblies.

More information on cutom code:
http://msdn2.microsoft.com/en-us/library/ms155798.aspx

Ian|||

Mark, which version of SSRS are you using?

Note: the parameter approach for databound images in page header/footer works correctly with RS 2005 SP1.

-- Robert

|||Robert,

I am using RS 2005 SP1; I just assumed that the parameter approach wouldn't work for binary image data due to the size factor. I'll have to try that out.

-Mark
|||Robert,

How exactly do I get this to work using the parameter approach? I've tried everything I can think of. It works for text, but not images.

Thanks,
Mark
|||

Mark, I attached a small sample report (based on the Northwind database) that shows how to do it.

Important parts:
* the report parameter default value is based on a calculated field (ImageBase64) from the dataset
* the calculated field is converting the image data field into a base-64 encoded string, e.g. =Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))
* the image report item takes the value of the Image report parameter and converts it back from a base-64 string to a byte array:
=Convert.FromBase64String(Parameters!Image.Value)

-- Robert

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>02f7d41c-e5a6-4015-9bd4-6aa837db9bb6</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Image">
<DataType>String</DataType>
<DefaultValue>
<DataSetReference>
<DataSetName>DataSet1</DataSetName>
<ValueField>ImageBase64</ValueField>
</DataSetReference>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Image</Prompt>
<Hidden>true</Hidden>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>1.5in</Height>
</Body>
<PageHeader>
<ReportItems>
<Image Name="image2">
<Sizing>AutoSize</Sizing>
<MIMEType>image/bmp</MIMEType>
<Width>2.875in</Width>
<Source>Database</Source>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
</Style>
<Value>=Convert.FromBase64String(Parameters!Image.Value)</Value>
</Image>
</ReportItems>
<Height>2.625in</Height>
<PrintOnLastPage>true</PrintOnLastPage>
<PrintOnFirstPage>true</PrintOnFirstPage>
</PageHeader>
<rd:ReportID>14f7b895-1a77-410d-ae9c-58cfb7418d60</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from employees</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="EmployeeID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>EmployeeID</DataField>
</Field>
<Field Name="LastName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>LastName</DataField>
</Field>
<Field Name="FirstName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>FirstName</DataField>
</Field>
<Field Name="Title">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Title</DataField>
</Field>
<Field Name="TitleOfCourtesy">
<rd:TypeName>System.String</rd:TypeName>
<DataField>TitleOfCourtesy</DataField>
</Field>
<Field Name="BirthDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>BirthDate</DataField>
</Field>
<Field Name="HireDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>HireDate</DataField>
</Field>
<Field Name="Address">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Address</DataField>
</Field>
<Field Name="City">
<rd:TypeName>System.String</rd:TypeName>
<DataField>City</DataField>
</Field>
<Field Name="Region">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Region</DataField>
</Field>
<Field Name="PostalCode">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PostalCode</DataField>
</Field>
<Field Name="Country">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Country</DataField>
</Field>
<Field Name="HomePhone">
<rd:TypeName>System.String</rd:TypeName>
<DataField>HomePhone</DataField>
</Field>
<Field Name="Extension">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Extension</DataField>
</Field>
<Field Name="Photo">
<rd:TypeName>System.Byte[]</rd:TypeName>
<DataField>Photo</DataField>
</Field>
<Field Name="Notes">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Notes</DataField>
</Field>
<Field Name="ReportsTo">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ReportsTo</DataField>
</Field>
<Field Name="PhotoPath">
<rd:TypeName>System.String</rd:TypeName>
<DataField>PhotoPath</DataField>
</Field>
<Field Name="ImageBase64">
<Value>=Convert.ToBase64String(System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Photo.Value),79)))</Value>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>4.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

Robert,

I am in the same fix regarding the databound image situation. I've used the hidden text box field inside the body of the report that converts the image (a field in the dataset) to a string and then the actual image in the page header references the hidden text box. This works great in the preview of BI Studio. It also works when the report is exported to Excel. However, when exported to PDF, the image is not shown. Is this even possible?

Regarding the solution above, I tried your suggestion. However, I'm back to the situation where a "fields cannot be used in report parameter expressions". Is there something I'm missing in your explanation above? This particular report will eventually become an "rdlc" report on a remote site so I'm limited as to what I can do in the BI environment.

Any help or direction would be appreciated! I've spent way too much time on what should be a simple task, in my opinion, to implement.

Also, do you still have the sample report?

Thanks,

John