Dynamically Referencing a Field Name

Is there any way to reference a Data Set field name dynamically in an expression?
For example, I have 5 reports that have the same layout but reference different columns.
For example, TextBox1 would be one of the following names depending on the report:
=Fields!A_Total.Value
=Fields!B_Total.Value
=Fields!C_Total.Value
=Fields!D_Total.Value
=Fields!E_Total.Value
I would like to build the field name dynamically instead (e.g., "Fields!" & Parameters.XXX.Label & "_Total.Value").
I know we could use IFF statements but that gets messy.
I have used dynamic sql in stored procedures to solve this issue for other reports, but I was wondering if there was an easy way to do this within the report (I have limited ability to modify this particular stored proc).
Thanks,
Mike=Fields(Parameters.WhichField.Value & "_Total").Value
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Mike Lyncheski" <MikeLyncheski@.discussions.microsoft.com> wrote in message
news:CCFFB904-03B0-41CF-87C9-ED43CC027355@.microsoft.com...
> Is there any way to reference a Data Set field name dynamically in an
expression?
> For example, I have 5 reports that have the same layout but reference
different columns.
> For example, TextBox1 would be one of the following names depending on the
report:
> =Fields!A_Total.Value
> =Fields!B_Total.Value
> =Fields!C_Total.Value
> =Fields!D_Total.Value
> =Fields!E_Total.Value
> I would like to build the field name dynamically instead (e.g., "Fields!"
& Parameters.XXX.Label & "_Total.Value").
>
> I know we could use IFF statements but that gets messy.
> I have used dynamic sql in stored procedures to solve this issue for other
reports, but I was wondering if there was an easy way to do this within the
report (I have limited ability to modify this particular stored proc).
> Thanks,
> Mike
>

Dynamically Positioning the table in the report layout

HI ,

Is it possible to dynamically position the table in the report.

i,e. I will have two table, first table will be displayed only if the query returns data, else it willl be hidden.

When the query does not returns any data, then the second table will be displayed( which will have some text displayed).

But my requirment is, both the table when displayed will be displayed from the first page itslef.

Something like this, First report will be vertically aligned to the top of the report. and the second table will be vertically alligned to the first table. So when the first table is hidden, then the second table will be rolled up while rendering the report

I have done it in other tool, but could not figure out in this tool.

We do something similar here . all of our reports have a small text box located at the top of the body section. This text box has a conditional visibility statement. If the data set returns no data this textbox will display a " no data returned" text string to the report.

All you need to do is add this statement to the visibility expression ( for the table or text box

=IIF(CountRows("DataSet")=0,False,True)

then some text to the table or text box explaining the lack of data.

="No data was returned for the chosen parameters."

hope this helps

|||

Thanks sir,

That was a good idea.

|||

FYI: There is also another way of doing this. Data regions (matrix, table, list, chart) and subreports have a so-called NoRows property. If set, this is the message to display in a textbox (which replaces the data region when no rows of data are available).
Note: the NoRows property is available in the VS properties window.

-- Robert

|||

You are right Robert,

But, the problem with this approach is that the whole table structure will be replaced with the text specified in that property.

some people would like to see the table structure as well , even if the report does not have any data.

Hope i am making some sense.

Dynamically collapsing textboxes (and adjusting layout)

Just curious if there's a way I can remove a field from my report at run time and shift all the fields underneath it up.

I basically want to end up with the following:

Design time=============================Field 1: Fields!Field1.ValueField 2: Fields!Field2.Value// This one will be blankField 3: Fields!Field3.ValueField 4: Fields!Field4.ValueRun Time - eliminate any blank fields=============================Field 1:"Data 1"Field 3:"Data 3"Field 4:"Data 4"

Problem is, if I set the visibility of the field to false, it still takes up space on the form (as it should). Any suggestions as to how to shift all the fields up without using a table?

Thanks!

I guess I should clairify a bit

This is what is happening now:

Run Time - eliminate any blank fields=============================Field 1:"Data 1"Field 3:"Data 3"Field 4:"Data 4"I want to collapse Field 2 up so that thereis no whitespace between 1 and 3

Not sure if this is even possible...

dynamic SQL stored procedure problem

Hi,
I have a project that all its reports are based on Dynamic SQL Stored Procedure. First, I had to handle the problem that in the Layout Tab there were no fields by adding them manually. However, when I try to show the report in the Preview tab I got the error: "Procedure x Expects a parameter @.y that was not Supplied." Despite the fact that I define the parameter in report parameter and in the Data Tab I got the correct SP answer.
When I wrote in the data set query "exec x @.y=1" and chose "Text" instead of "Stored Procedure" I got the correct SP answer, but because all the reports get their parameters from the application (user) I can not leave the report like that.
From http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.comYou should be able to do this:
exec x @.y and then map the query parameter to the report parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Avi" <savi176@.yahoo.com> wrote in message
news:1bda4f2c-19e6-4b8c-9bc9-c3d68cfcba8b@.developmentnow.com...
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab there
> were no fields by adding them manually. However, when I try to show the
> report in the Preview tab I got the error: "Procedure x Expects a
> parameter @.y that was not Supplied." Despite the fact that I define the
> parameter in report parameter and in the Data Tab I got the correct SP
> answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but because
> all the reports get their parameters from the application (user) I can
> not leave the report like that.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com|||Alot of times this will happen when you do not have the parameter defined in
the dataset. RS takes your parameters but ends up not passing them to the
stored proc because you haven't explicitly told it to in the properites of
the dataset. Check it by going to the appropriate dataset. hit the elipsis
(...)-> parameters tab. You need to list the parameters that the proc takes
in the correct order that it takes them. The name value list would look as
such
NAME VALUE
@.x =Parameters!X.value
@.y = Parameters!Y.Value
See if that helps!
Michael Abair
Programmer / Analyst
Chicos FAS Inc.
"Avi" <savi176@.yahoo.com> wrote in message
news:1bda4f2c-19e6-4b8c-9bc9-c3d68cfcba8b@.developmentnow.com...
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab there
> were no fields by adding them manually. However, when I try to show the
> report in the Preview tab I got the error: "Procedure x Expects a
> parameter @.y that was not Supplied." Despite the fact that I define the
> parameter in report parameter and in the Data Tab I got the correct SP
> answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but because
> all the reports get their parameters from the application (user) I can
> not leave the report like that.
>
> From
> http://www.developmentnow.com/g/115_0_0_0_0_0/sql-server-reporting-services.htm
> Posted via DevelopmentNow.com Groups
> http://www.developmentnow.com

Dynamic SQL Stored Procedure problem

Hi,
I have a project that all its reports are based on Dynamic SQL Stored
Procedure. First, I had to handle the problem that in the Layout Tab
there were no fields by adding them manually. However, when I try to
show the report in the Preview tab I got the error: "Procedure x
Expects a parameter @.y that was not Supplied" Despite the fact that I
define the parameter in report parameter and in the Data Tab I got the
correct SP answer.
When I wrote in the data set query "exec x @.y=1" and chose "Text"
instead of "Stored Procedure" I got the correct SP answer, but
because all the reports get their parameters from the application
(user) I can not leave the report like that.
Any Ideas?
Thank you.
AviI've had this problem a few times, and ended up solving it by simply
shutting down and restarting Visual Studio (after saving the report, of
course). Not sure if it will work for your problem, but its worth a
try if you haven't done that already.
AB wrote:
> Hi,
> I have a project that all its reports are based on Dynamic SQL Stored
> Procedure. First, I had to handle the problem that in the Layout Tab
> there were no fields by adding them manually. However, when I try to
> show the report in the Preview tab I got the error: "Procedure x
> Expects a parameter @.y that was not Supplied" Despite the fact that I
> define the parameter in report parameter and in the Data Tab I got the
> correct SP answer.
> When I wrote in the data set query "exec x @.y=1" and chose "Text"
> instead of "Stored Procedure" I got the correct SP answer, but
> because all the reports get their parameters from the application
> (user) I can not leave the report like that.
> Any Ideas?
> Thank you.
> Avi

Dynamic SQL Stored Precedure problem

Hi,

I have a project that all its reports are based on Dynamic SQL Stored Procedures. First, I had to handle the problem that in the Layout Tab there were no fields by adding them manually. However, when I try to show the report in the Preview tab I got the error: "Procedure x Expects a parameter @.y that was not Supplied."Despite the fact that I define the parameter in report parameter and in the Data Tab I got the correct SP answer.

When I wrote in the data setquery"execx @.y=1"and chose "Text" instead of"Stored Procedure"I got thecorrect SP answer, but because all the reports gettheir parameters from the application (user)I can not leave the report like that.

Try this: As a data set write static query that returns all the fields of your dynamic query and uses all the parameters. Then refresh report so it will update parameter list and field names. After that you can change static query to dynamic.

I did it like this:

declare @.sql varchar(max); set @.sql = '';

set @.sql = '.....'

exec (@.sql)

Maciej