Datasets in report header/footer - A Summary of the issue

I know there are already a few threads on this topic but I wanted to create another one - and directly ask - how could this limitation possibly exists - and even more important - why is it still a problem in 2005?

I want to summarize a few of the solutions that have been listed here and elseware online. We've tried them all and they all have some kind of problem:

1) Create hidden field(s) and then reference that field from the header: Doesn't work on any pages where the field isn't printed. Header value will be blank in this case.

2) Create hidden column(s) in the data table and reference that from the header: Works, but again, if the table is showing on every page, the value will be blank. i.e. - if you have two or more tables stacked, you would have to add the columns to every one. A very sloppy and not practical solution.

3) Do it with custom code: I've seen this approach recomended but have never actually seen someone provide a working example. From what I understand, custom code can't access the datasets (I'm using 2000 - maybe 2005 is different here?)

4) Use a queried parameter to hold the value- then reference that in the header: This seems to be the best contender so far. It seems to work sometimes, but on several machines here, the report designer goes into some kind of infinite loop and crashes once the queried parameter is added.

If anyone has any other approaches that I'm not aware of, or has another idea, please post it here. Microsoft - this is a huge problem for many people. Is there any chance of a fix for this any time soon?

Thank you.

~H

did you find a solution ?

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

sql

Datasets in report header/footer - A Summary of the issue

I know there are already a few threads on this topic but I wanted to create another one - and directly ask - how could this limitation possibly exists - and even more important - why is it still a problem in 2005?

I want to summarize a few of the solutions that have been listed here and elseware online. We've tried them all and they all have some kind of problem:

1) Create hidden field(s) and then reference that field from the header: Doesn't work on any pages where the field isn't printed. Header value will be blank in this case.

2) Create hidden column(s) in the data table and reference that from the header: Works, but again, if the table is showing on every page, the value will be blank. i.e. - if you have two or more tables stacked, you would have to add the columns to every one. A very sloppy and not practical solution.

3) Do it with custom code: I've seen this approach recomended but have never actually seen someone provide a working example. From what I understand, custom code can't access the datasets (I'm using 2000 - maybe 2005 is different here?)

4) Use a queried parameter to hold the value- then reference that in the header: This seems to be the best contender so far. It seems to work sometimes, but on several machines here, the report designer goes into some kind of infinite loop and crashes once the queried parameter is added.

If anyone has any other approaches that I'm not aware of, or has another idea, please post it here. Microsoft - this is a huge problem for many people. Is there any chance of a fix for this any time soon?

Thank you.

~H

did you find a solution ?

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

|||I am very interested in a solution, too.

Although I have found a 5th option, which works quite well for my scenario - nevertheless I don't consider it being very elegant (at least with "allow null values" enabled).

5) Use "Available values: From query"

All

report parameters are hidden and passed via the URL. The parameter

value is an ID, but in the page header my customer would like to see

the corresponding description.

I have have defined the available values to come from a query and specified the value and label fields. Now I can access the parameter value in the page header like e.g.

Parameters!ProcessId.Label

There's one drawback: if you're using parameters having "allow null value" enabled this version gives you an error when openen the report having passed NULL for the parameter (I have to translate the message from german to english):

The process ID parameter value is missing.

This problem can be solved, but I consider it being a quirk. Just use the following SQL for your data set:

select ProcessId, Desc from Processes
union
select ProcessId=NULL, Desc=NULL from Processes

I'm also interested in sharing other (more elegant) approaches.

Thanx for listening and kind regards,
Gerald

Datamining Viewer Client Problem

Hello!

I am running a SQL Server 2005. I created the mining models from the tutorials. I can connect with the DataminingViewerClient directly on the SQL Server(i.e. to localhost), but when i try to connect to the Server from my other computer it is not working i always get connection faild and i should check that the server is running. I can connect to the Server with other programms but the viewer is not working. Any ideas what that could be?

Can you connect to other servers from the machine where the DataminingViewerClient application works? If so, it may be that there are additional client components that need to be installed. Let us know and we can continue to diagnose.

Thanks

-Jamie

|||Ok i now tried to connect from Server A to Server B, not working either. It works on both when i run the viewer directly on the server and connect to localhost but not from Server A to B or vice versa or from any other computer. Could it be that some permissions are missing, because in the dataminingviewer client i dont have to specify a user/password for the server where the analysis-service runs. I dont think that components are missing because i can compile the source code without errors, but i'll be happy about any hint that might help.

Another thing i tried: When i create a datasource on Server A to connect to Server B(or from any other computer) it only works when I use the sa user(Windows Authentication doesnt work), otherwise i cant browse/select the databases.|||In general AS only uses Windows Authentication, unless you connect over http (requires configuration - see books online). Connecting over http allows you to specify a username and password. For the DM Viewer Client, you cna modify the code to use a http connection, or prompt for username and password. The source code is provided.|||

Hello again!

I found the error...the server was not in the domain, after we added it it worked.

Datafile From Non-Windows Server

Can SQL Server 2000 import a flat data file directly from a mainframe
or other server that is not a Windows server or does the data file
need to be moved to a Windows server first?
Jerry
On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:

>Can SQL Server 2000 import a flat data file directly from a mainframe
>or other server that is not a Windows server or does the data file
>need to be moved to a Windows server first?
The first issue is whether the file can be "seen" and accessed. I
have seen AS/400 setups where that system had part of the disk visible
over the network so that a Windows box could access the files like any
other network drive. If the file is visible in that way something
should be possible. However I generally used a transfer utility
rather than a shared directory.
The next issue is whether the file is in a useable format. All the
files I ever loaded from a mainframe started on the mainframe end as
readable text, with no binary, float, packed, or zoned decimal fields,
likewise no "overpunch" where the sign is carried in half a byte. Then
it was a matter of using a transfer tool tool that converted IBM's
mainframe character set, EBCDIC, to the ASCII used by the rest of the
world.
A final aternative would be a driver that allowed ODBC or equivelent
access to mainframe data.
Roy Harvey
Beacon Falls, CT
|||In addition, the best approach here is to define your process:
1) dump the flat-file from the AS400 to a Windows machine or an FTP site
2) Use DTS to do ETL - there is a File Transfer Protocol Task if you intend
to read from an FTP site or a Text File Source if you intend to dump the
file on a Windows machine
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:s1gpb3prd16altj63k5qatvicte8vd2im7@.4ax.com...
> On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>
> The first issue is whether the file can be "seen" and accessed. I
> have seen AS/400 setups where that system had part of the disk visible
> over the network so that a Windows box could access the files like any
> other network drive. If the file is visible in that way something
> should be possible. However I generally used a transfer utility
> rather than a shared directory.
> The next issue is whether the file is in a useable format. All the
> files I ever loaded from a mainframe started on the mainframe end as
> readable text, with no binary, float, packed, or zoned decimal fields,
> likewise no "overpunch" where the sign is carried in half a byte. Then
> it was a matter of using a transfer tool tool that converted IBM's
> mainframe character set, EBCDIC, to the ASCII used by the rest of the
> world.
> A final aternative would be a driver that allowed ODBC or equivelent
> access to mainframe data.
> Roy Harvey
> Beacon Falls, CT

Datafile From Non-Windows Server

Can SQL Server 2000 import a flat data file directly from a mainframe
or other server that is not a Windows server or does the data file
need to be moved to a Windows server first?
--
JerryOn Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>Can SQL Server 2000 import a flat data file directly from a mainframe
>or other server that is not a Windows server or does the data file
>need to be moved to a Windows server first?
The first issue is whether the file can be "seen" and accessed. I
have seen AS/400 setups where that system had part of the disk visible
over the network so that a Windows box could access the files like any
other network drive. If the file is visible in that way something
should be possible. However I generally used a transfer utility
rather than a shared directory.
The next issue is whether the file is in a useable format. All the
files I ever loaded from a mainframe started on the mainframe end as
readable text, with no binary, float, packed, or zoned decimal fields,
likewise no "overpunch" where the sign is carried in half a byte. Then
it was a matter of using a transfer tool tool that converted IBM's
mainframe character set, EBCDIC, to the ASCII used by the rest of the
world.
A final aternative would be a driver that allowed ODBC or equivelent
access to mainframe data.
Roy Harvey
Beacon Falls, CT|||In addition, the best approach here is to define your process:
1) dump the flat-file from the AS400 to a Windows machine or an FTP site
2) Use DTS to do ETL - there is a File Transfer Protocol Task if you intend
to read from an FTP site or a Text File Source if you intend to dump the
file on a Windows machine
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:s1gpb3prd16altj63k5qatvicte8vd2im7@.4ax.com...
> On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>>Can SQL Server 2000 import a flat data file directly from a mainframe
>>or other server that is not a Windows server or does the data file
>>need to be moved to a Windows server first?
> The first issue is whether the file can be "seen" and accessed. I
> have seen AS/400 setups where that system had part of the disk visible
> over the network so that a Windows box could access the files like any
> other network drive. If the file is visible in that way something
> should be possible. However I generally used a transfer utility
> rather than a shared directory.
> The next issue is whether the file is in a useable format. All the
> files I ever loaded from a mainframe started on the mainframe end as
> readable text, with no binary, float, packed, or zoned decimal fields,
> likewise no "overpunch" where the sign is carried in half a byte. Then
> it was a matter of using a transfer tool tool that converted IBM's
> mainframe character set, EBCDIC, to the ASCII used by the rest of the
> world.
> A final aternative would be a driver that allowed ODBC or equivelent
> access to mainframe data.
> Roy Harvey
> Beacon Falls, CT

Datafile From Non-Windows Server

Can SQL Server 2000 import a flat data file directly from a mainframe
or other server that is not a Windows server or does the data file
need to be moved to a Windows server first?
JerryOn Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:

>Can SQL Server 2000 import a flat data file directly from a mainframe
>or other server that is not a Windows server or does the data file
>need to be moved to a Windows server first?
The first issue is whether the file can be "seen" and accessed. I
have seen AS/400 setups where that system had part of the disk visible
over the network so that a Windows box could access the files like any
other network drive. If the file is visible in that way something
should be possible. However I generally used a transfer utility
rather than a shared directory.
The next issue is whether the file is in a useable format. All the
files I ever loaded from a mainframe started on the mainframe end as
readable text, with no binary, float, packed, or zoned decimal fields,
likewise no "overpunch" where the sign is carried in half a byte. Then
it was a matter of using a transfer tool tool that converted IBM's
mainframe character set, EBCDIC, to the ASCII used by the rest of the
world.
A final aternative would be a driver that allowed ODBC or equivelent
access to mainframe data.
Roy Harvey
Beacon Falls, CT|||In addition, the best approach here is to define your process:
1) dump the flat-file from the AS400 to a Windows machine or an FTP site
2) Use DTS to do ETL - there is a File Transfer Protocol Task if you intend
to read from an FTP site or a Text File Source if you intend to dump the
file on a Windows machine
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:s1gpb3prd16altj63k5qatvicte8vd2im7@.
4ax.com...
> On Fri, 10 Aug 2007 18:18:01 -0000, Jerry <jerryalan@.gmail.com> wrote:
>
> The first issue is whether the file can be "seen" and accessed. I
> have seen AS/400 setups where that system had part of the disk visible
> over the network so that a Windows box could access the files like any
> other network drive. If the file is visible in that way something
> should be possible. However I generally used a transfer utility
> rather than a shared directory.
> The next issue is whether the file is in a useable format. All the
> files I ever loaded from a mainframe started on the mainframe end as
> readable text, with no binary, float, packed, or zoned decimal fields,
> likewise no "overpunch" where the sign is carried in half a byte. Then
> it was a matter of using a transfer tool tool that converted IBM's
> mainframe character set, EBCDIC, to the ASCII used by the rest of the
> world.
> A final aternative would be a driver that allowed ODBC or equivelent
> access to mainframe data.
> Roy Harvey
> Beacon Falls, CT

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