• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

Look up data from table into Reporting Services footer field

How can I populate a field in a report footer with a select lookup. (SQL /RS 2005)

I wish to do a simple SELECT statement and return the value to a textbox in my report footer.
I am looking up a single item e.g. "SELECT AgentsRef FROM SystemParams WHERE ClientID = 1"
The result of this would be "Agent1".
I want the report footer to be ="Agents Ref: " & strAgenstRef

  • 2
1 Solution
Megan BrooksSQL Server ConsultantCommented:
Assuming you want the same footer text on each page, in RS 2008 you could just create a dataset containing your SELECT query for the footer text and then drag the field from the dataset to the report footer to create a textbox with the appropriate expression.
In RS 2005 you would still create the additional dataset to return the footer text, but I think you might need to add a textbox to the report footer and then enter the appropriate expression into it. For example:

=First(Fields!footertext.Value, "DataSet2")
where "footertext" is the name of the field returned by the dataset and "DataSet2" is the name of the dataset you created to return the footer text.
I don't have a  copy of RS 2005 around with which to test this but I believe I have tried it in the past. I did test it with RS 2008.
BrightRavenBusiness AnalystAuthor Commented:
SQL 2005 states you can not place a field in a footer.
You would think being SQL you could put an inline Select statement in any textbox!!!

RS2008 I know has advanced with variables etc. But I need to do this in SQL 2005.

MS should be shot for this!
Megan BrooksSQL Server ConsultantCommented:
I think I remember now running into that with RS 2005, and I may have worked around it by using some other kind of footer. It's important to specify a dataset in the expression; you can't default to the table dataset outside the table, even in RS 2008.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now