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

BrightRavenBusiness AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.