SSRS- preserving % in dataset field name?

Question

SSRS- preserving % in dataset field name?

REF123

Mr or Mrs. 500

Points: 546
More actions
March 29, 2010 at 11:49 am

#386597

Hi,
I'm working with an SSRS 2005 report that queries a view. The view has several fields with a % in the name. The query below is in the report dataset:
Select [5%], [10%]
From view_A
When I run the above query in the Data tab in the report, the % shows properly. It ends up getting translated to ID5_ and ID10_ in the dataset field, though.
Does anyone have any suggestions for preserving the % in the dataset field name? Is there maybe a way to escape the %? The only option that I can see is to replace the % with something like "per" at some point in the SQL and then replace "per" in the report with "%".
Any suggestions would be helpful.
Thanks!
Rachel

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

Couple of things happening here... first, I'd recommend you not include any reserved words or characters like the % sign or anything else in column names, tables, or really anything else you'll ever need to query. As you've now found it just makes your life more difficult later on.

Secondly, I can't find a reference for it right now, but I don't believe you can start a column name in a dataset with a number. That's why it's automatically renaming them to ID_5 and ID_10. (btw, you can right click on the name int he dataset from the layout tab to rename it, but you can't start it with a numeral.)

You can rename them, but at the end of the day it's not really the dataset field name that's important, it's what you show to the users. You don't have to accept the names that get auto-populated for you as column headers. You can change them to whatever you want.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

REF123 Mr or Mrs. 500 Points: 546 More actions · Answer 2

Thanks, Luke. I didn't realize that numbers in the beginning of the column names were contributing to the problem.

Unfortunately, I have requirements to keep/show the column names as is. I had origianally thought that my report would need to be more dynamic but I'm not sure that this is the case now. I think that renaming in the layout may be sufficient.

Thanks again!

Rachel

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

Perhaps I don't understand, can you please clarify what you mean when you say, " I have requirements to keep/show the column names as is"

Do you mean that you can't have the header row contain a value that is different than the name of the column from the view in the database?

It would seem to me that you'd be able to do this and everything would be great...

To help us help you read this[/url]For better help with performance problems please read this[/url]

DuncEduardo SSCarpal Tunnel Points: 4410 More actions · Answer 4

REF123 (3/30/2010)
Thanks, Luke. I didn't realize that numbers in the beginning of the column names were contributing to the problem.
Unfortunately, I have requirements to keep/show the column names as is. I had origianally thought that my report would need to be more dynamic but I'm not sure that this is the case now. I think that renaming in the layout may be sufficient.
Thanks again!
Rachel

Hi, only stipulation is that the column name doesn't start with a number.

Once you have set the "name" of the field, you can rename the "value" property.

e.g. Name = [ID1-6], Value = 1-6