Concatenating a parameter and text to access a Field and its values

Question

Concatenating a parameter and text to access a Field and its values

Amaranthus

SSC Rookie

Points: 27
More actions
October 25, 2009 at 12:00 pm

#227419

Hi there -
I'm trying to build a reusable report that has many possible entry points that will each pass a single parameter, and that parameter needs to help determine what is shown in each textbox in my table.
It works like this (well, it doesn't, but that's what's I'm for here for help on!):
1. User reads their report, says "Hmmm, fascinating" and clicks on a value. That value calls a 2nd report, and passes it a parameter called @param with a value of "report1fieldA".
2. Report 2 receives the parameter, storing it in its own parameter called @param
3. Report 2 has a set of Fields called things like "report1fieldA_data1", "report1fieldB_data1", "report1fieldC_data1" and "report1fieldA_data2" and "report1fieldA_data3".
4. I want to be able to use the parameter passed from Report 1 to Report 2 to decide which of the Fields in Report 2 should be shown.
I was hoping there was a way of concatenating my parameter with some text to create an expression in a cell in my table can combine the parameter value "report1fieldA" with the string "_data1" and know which field to evaluate/display in the cell.
Does that sound possible? It's causing me a little Sunday craziness :hehe:
Thanks,
-amy.

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

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

Jon Spink SSCarpal Tunnel Points: 4010 More actions · Answer 1

I don't know any way of converting a literal string into something which could be interpreted as a field name. Better to pass your @param to the underlying stored proc of your second report and choose which fields to display there.

Amaranthus SSC Rookie Points: 27 More actions · Answer 2

Thanks for the suggestion Jon - you know how it is, you spend so long looking at something you stop seeing that there's a different solution. I'll try that later today and let you know if I get the results I need (I know the SQL part will work, I just need to test through the logic of what I'm trying to display in the report and make sure I don't miss anything).

Thanks again for your help!

benlatham SSC Eights! Points: 933 More actions · Answer 3

You can actually take a string and turn it into a field reference like this:

=Fields("FieldName").Value

So you could use the value of a parameter like:

=Fields(Parameters!MyParameter.Value).Value

Ben

Amaranthus SSC Rookie Points: 27 More actions · Answer 4

Thanks for the help guys - I went with option 1 in the late hours of last night and rewrote the SQL query to build the correct field name depending on the passed parameter and everything is great 😀

It's good to know that it can be done directly and text can be converted to a field name - I'll have to save that and try it out on the next run.

Thanks again,

-amy.

Jon Spink SSCarpal Tunnel Points: 4010 More actions · Answer 5

glad to help, though I didn't know about Ben's solution and it is a little neater...