SSRS 2008 r2 - How to permit user to amend text and then use it in report

  • caroline.allen

    SSC Rookie

    Points: 47

    Hi,

    I have a group of users who need to amend address details pulled in a report before it is printed out.  We do not want these amendments to be saved or recorded on CRM.

    I am struggling to work out how to pull the address, allow them to amend, and then use their amendments as the final version that is printed out.  I have tried with text box parameters which pull the address details from the CRM but cannot work out how to save their changes and re-use them.  We have the report built in a version which exports directly from the report screen however they are unable to amend that version.  We then tried to export to Word and built a version to suit but the data keeps moving about the page and the same report never prints in the same places.  We need to use the export from screen version if possible as it is to be printed onto a pre-printed legal form and the results need to be in exactly the right place.

    I have attached example of the report so far.

    If anyone could point me in the right direction I would be eternally grateful.

    Attachments:
    You must be logged in to view attached files.
  • Andrey

    Mr or Mrs. 500

    Points: 553

    you can substitute the values in the report

    example:   =iif(isNothing(Parameters!AddressLine1.Value)=False

    , iif(Parameters!AddressLine1.Value<> Fields!address1_line1.Value

    , Parameters!AddressLine1.Value

    , Fields!address1_line1.Value

    )

    , Fields!address1_line1.Value

    )

     

    Also, parameter AddressLine1 should be Visible and Allow blank value.

     

    • This reply was modified 2 months ago by  Andrey. Reason: added comment
  • David Burrows

    SSC Guru

    Points: 64444

    Create a dataset that selects the address lines and set each address line parameter to use default from the dataset and use the parameter values in the report.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • caroline.allen

    SSC Rookie

    Points: 47

    Hi Audrey, Thank you so much for the prompt reply.  It took me a while to get my head around where to use the code and how best to put it into the report.  The problem I had was that the user needed to see the Existing Address Fields from CRM in order to know if they need to make any changes/amendments.  The usual parameter box pops up before the report is run and at that point there are no address fields on show for them to reference.  However while I was testing out your response it occurred to me that I could use parameters to show the existing record next to another parameter to capture the changes.  This works brilliantly.  Thank you so much.  I have been battling for ages with this and now feel that I can see the end of the tunnel.  Very helpful

  • Andrey

    Mr or Mrs. 500

    Points: 553

    Caroline, good job!

    Glad to be of any help.

  • caroline.allen

    SSC Rookie

    Points: 47

    Hi,

    I have a slightly amended version of Audrey's code from above which works well for the following scenarios:

    • if I want to remove an entry from one of the address fields thereby leaving it blank
    • if I want to amend one of the address fields.
    • if the original field is blank and I want it to stay that way

    So thanks again Audrey for that.

    However, I am really struggling where the original field does not hold a value and therefore is blank or NULL.  The parameter is showing as a blank and I can type into the parameter but when I run the report it is not displaying the new value.

    I have tried a multitude of codes to try to get this sorted (2 attached) but I am unable to get it to work.

    Tried:

    • 2 parameters for each address line, One to show existing data and 1 to capture the amendment.
    • checking for IsNothing on the original field data
    • checking for IsNothing on the new parameter value

    Can anyone help with how to get the report to accept the new parameter value where there is no value in the original data?

     

    Attachments:
    You must be logged in to view attached files.
  • caroline.allen

    SSC Rookie

    Points: 47

    Hi David, sorry for the delay but have been battling with this report again for days.

    I believe that I am already doing what you have suggested however I am still having problems (Please see my new reply above).  If you are able to shed any light I would be grateful

    • This reply was modified 2 months ago by  caroline.allen. Reason: referenced post is above and not below this post
  • David Burrows

    SSC Guru

    Points: 64444

    Actually my post was too much as you are already retrieving the address lines.

    The only things I can suggest are (if you have not done so already)

    Change the SQL query to always return empty string instead of NULL (ie use ISNULL or COALESCE)

    If you cannot change the SQL then set the parameter Default values option to Specify values and use an expression to return the address and replace NULL with empty string

    Do not compare the parameter with the dataset, use the parameter value directly in the report.

     

    Far away is close at hand in the images of elsewhere.
    Anon.

  • caroline.allen

    SSC Rookie

    Points: 47

    Hi David, Thank you so much for the reply.  The ISNULL in the SQL query was just the thing and it all works a treat now.  Thank you so much for all the help.

  • David Burrows

    SSC Guru

    Points: 64444

    caroline.allen wrote:

    Hi David, Thank you so much for the reply.  The ISNULL in the SQL query was just the thing and it all works a treat now.  Thank you so much for all the help.

     

    Thanks for the feedback 🙂

    Far away is close at hand in the images of elsewhere.
    Anon.

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

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