CODEWITHSUNDEEP
sqlt-sqlreporting-servicesssrs-2008
Alex Gordon
Alex Gordon

Reputation: 60691

how to update field names automatically after updating SQL

I am changing the command text for a data set inside the .rdl ffile:

enter image description here

I would like to know how can I update the resulting fields that are returned by the select statement:

enter image description here

I know that these fields must be automatically generated, so I was wondering if it's possible to update them right after editing the SQL code inline??

Upvotes: 2

Views: 148

Answers (1)

djangojazz
djangojazz

Reputation: 13232

Usually when someone wants to have a look at the data in command text they are wanting it for reference to an end user(from what I have seen). You may want to amend it but ultimately with reporting your first goal should be: "What am I doing this for?" If your goal is dynamic creation at runtime then I would avoid this and offer a few other suggestions:

  1. Procertize it. Making a stored procedure if you have the know how in SQL Server is a convenient and fast way to get what you want and you can optimize it if you know what you are doing with your SQL FU to get good results. The downside would be if you work with multiple environments you have to deploy your code for the TSQL as well as the RDL file.

  2. Use an expression to build the dataset at runtime. In cases where I have been told that the query itself was not properly optimized by other developers they have mentioned doing this. I myself do not always see the advantage of doing this versus just having your predicate construction work well with good indexing on the source engine. Regardless you can build your dataset at runtime. It would be similar to hitting 'fx' next to the text and then putting in something like this(assuming you have a variable named @Start):

    ="Select thing 
from table 
Where >= " & Parameters!Start.Value

    Again I have not really seen if this is really that much faster than:

    Select thing
from table
Where >= @Start

    But it is there if you just want to build it dynamically.

  3. You can try to build your expression dynamically from parameters being PART of the select statement. SSRS is all about the 'expressions' and what you can do with them. Once you jump in and learn how they apply to everything you can go nuts so to speak on using them. A general rule though is the more of them you use and rely on the slower your reports will become.

I hope some of this may help, I would ask first is something dynamic due to a need to be event driven or is performance related.

Upvotes: 2

Related Questions