Post reply

ssrs 2010 use fx button for inline sql

  • January 17, 2019 at 1:26 pm

    #409439

    In an ssrs 2010 report, my dba wants me to change a stored procedure and place the sql into the report as inline sql. The problem is if I use the 'query designer' button, the sql is too long.
    However if I use the FX button instead, the entire sql can be brought in. The window shows  'set expression for Query.CommandText'.
    However the report does not know where the parameters to the report are. The 'refresh fields button'does not show the columns that are to appear in the dataset.
    Thus can you tell me how to use the 'fx' button in this manner?
    If this does not work, is there a way to make a long string of sql work in the ssrs 2010 report? If so, can you tell me how to accomplish this  goal?

  • January 17, 2019 at 1:43 pm

    #2019269

    Go back and ask said DBA why he wants this. Sounds absurd. The nice thing about stored procedures is that you can assign rights to them. you can't do that to ad hoc SQL as far as I know.

  • January 17, 2019 at 2:07 pm

    #2019277

    Does the reportcurrently use the stored proc or is this a new report? I ask because, if youhave a report that starts with a stored proc but is changed to ad-hoc (inline)SQL - the parameters should not disappear in SSRS.  Either way - you can manually add theparameters.

    Other things you can try:

    1. Restart Visual Studio  and try again
    2. Blow away the Dataset and try  recreating it

    Both of these ^^have worked for me

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 3 posts - 1 through 3 (of 3 total)

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