locked
SSRS 2012 - 2 Fields which Appear in Preview of Report are blank via Report Viewer RRS feed

  • Question

  • Hi Guys,

    First time posting, apologies if this one has been answered elsewhere but I've been googling for 2 days and can't find the solution to this.

    I have created a basic report in SSRS which essentially takes data from a shared dataset I created which selects all data from a View - the view is combining 2 tables, one contains rows which represent safety inductions, the other table is a list of Vendors and their Address information. I'm using a basic tablix to display the data.

    Fields as follows:

    T1.InductionID, T1.Vendor, T2.Vendor_Address, T1.Inductee, T1.CGI_No, CONVERT(VARCHAR,DATEADD(yy,1,T1.Induction_Date),103) as "Expiry_Date"

    There isn't really anything wrong with the query as far as I can tell - when I preview the report in Visual Studio all fields come through. When I build\deploy the report the 'Vendor_Address' and the 'Expiry_Date' fields are blank.

    The interesting thing is that when I open the *.rdl.data file in Notepad++ I can see the correct data sitting in there - it just isn't coming through for those 2 fields in the web report viewer.

    I am using IE11 to view reports.

    Any advice would be greatly appreciated. Please let me know if there's further information I can provide that is useful.


    Sunday, October 30, 2016 10:53 PM

Answers

  • You mean the "data" for those two fields don't appear in the Report Viewer right?  The field names appear on the report but no data is beneath them ... is that what you are describing?  I'm under the impression we are trying to tackle the data issue.

    This is odd indeed.  Are you using the same account or SPN for your connection when you connect via VS and from the http://.../Reports/  ?  I ask because this could simple be a permissions issue tied to an access restriction on those fields in the table.

    What version of SSRS / SQL Server / SSDT are you using?  On your third bullet from above, when you re-added the fields, did you go back into SSDT to do this or did you use Report Builder from the http...

    You might want to see if you can reproduce this behavior when developing and deploying using report builder vs SSDT.  Report Builder is a free download from Msft and is dependent on which version of SSRS you have.


    SOTATS, Inc.

    • Marked as answer by BrandonS_AU Monday, October 31, 2016 1:29 AM
    Monday, October 31, 2016 12:52 AM
  • Good to hear it.  Sounds like during one of your initial deployments you must have deleted those fields and then when you edited it in SSDT and actually had those fields back in the .rds  when you were re-deploying you had the deployment option set so it will not update the data source and dataset on the SSRS site.

    I've been there and done that before.  Typically you don't want to update those when you re-deploy ... you only want to overwrite the .rdl report file.


    SOTATS, Inc.

    • Marked as answer by BrandonS_AU Monday, October 31, 2016 1:45 AM
    Monday, October 31, 2016 1:43 AM

All replies

  • You probably need to click the "refresh" icon on your report to pull down your latest data in your SSRS web site connection.

    Prior to doing that please confirm that your caching is not set to "Always run this report against pre-generated snapshots" for your report and dataset and data source.

    Also make sure your connection string on the website is really pointed to the source you expect.


    SOTATS, Inc.

    Monday, October 31, 2016 12:10 AM
  • Hi Ulysses,

    Thanks very much for the reply.

    • Processing Options for this report (and all others) are set to "Always run with the most recent data" and "do not cache temporary copies of this report".
    • Clicking refresh in the Report doesn't solve the issue.
    • As an exercise I have tested removing these fields, refreshing the shared and report datasets, building and deploying the report - then re-adding the fields, refreshing the datasets and build\deploying the report. The issue still persists.

    [EDIT]: I can also confirm the report viewer is pointing to the correct source - in this case there is no development\production environment, we only have the one SSRS server set up pointing to a single DB which is in effect a production DB.

    I have also tested moving the query from the view directly into the shared dataset, so instead of just doing a 'select * from [view]', I'm handling the joins and date conversion directly in the dataset's query.

    Same result; data appears fine in the preview in Visual Studio, just those 2 fields don't appear in Report Viewer.

    Best Regards,

    Brandon.




    • Edited by BrandonS_AU Monday, October 31, 2016 12:28 AM
    Monday, October 31, 2016 12:23 AM
  • You mean the "data" for those two fields don't appear in the Report Viewer right?  The field names appear on the report but no data is beneath them ... is that what you are describing?  I'm under the impression we are trying to tackle the data issue.

    This is odd indeed.  Are you using the same account or SPN for your connection when you connect via VS and from the http://.../Reports/  ?  I ask because this could simple be a permissions issue tied to an access restriction on those fields in the table.

    What version of SSRS / SQL Server / SSDT are you using?  On your third bullet from above, when you re-added the fields, did you go back into SSDT to do this or did you use Report Builder from the http...

    You might want to see if you can reproduce this behavior when developing and deploying using report builder vs SSDT.  Report Builder is a free download from Msft and is dependent on which version of SSRS you have.


    SOTATS, Inc.

    • Marked as answer by BrandonS_AU Monday, October 31, 2016 1:29 AM
    Monday, October 31, 2016 12:52 AM
  • Hi Ulysses

    You're correct, the issue is with the data not flowing through to the report, the headers are present in the tablix when viewed in the Report Viewer.

    I'm using SSDT 2016 with the target version set to '2008 R2, 2012 or 2014' - target SQL Server Db and SSRS is 2012. I'm using pass-through windows authentication, my user is assigned the db_owner role of the data source in question.

    Happy to be wrong, but I can't see any way that this is a perms issue as all the other data which is appearing in the report is sitting in the same tables and there is no explicit security set in the view (though I bypassed the view and did a direct query just in case).

    I have been doing all of my report building via SSDT - I'll give it a go with the report builder (which I believe I can just DL the compatible version via the Report Viewer as it is part of the installation) and report back.

    Cheers,

    Brandon.

    Monday, October 31, 2016 1:05 AM
  • Hi Ulysses,

    I've managed to get this working now, the answer was essentially to recreate the report in the report builder including data source and datasets.

    Interesting to note, when I tried to use the shared dataset that I had created on the server via SSDT in the Report Builder, the 2 fields in question were not present. When I created the dataset again using a 'select * from [view]', all fields were present and when I saved the report they all appeared in the report viewer.

    I have no idea what the difference is from a data access point of view..

    Thanks very much for the prompt response and advice.
    • Edited by BrandonS_AU Monday, October 31, 2016 1:29 AM
    Monday, October 31, 2016 1:29 AM
  • Good to hear it.  Sounds like during one of your initial deployments you must have deleted those fields and then when you edited it in SSDT and actually had those fields back in the .rds  when you were re-deploying you had the deployment option set so it will not update the data source and dataset on the SSRS site.

    I've been there and done that before.  Typically you don't want to update those when you re-deploy ... you only want to overwrite the .rdl report file.


    SOTATS, Inc.

    • Marked as answer by BrandonS_AU Monday, October 31, 2016 1:45 AM
    Monday, October 31, 2016 1:43 AM
  • I just checked my deployment settings and that is exactly what the problem was - both 'Overwrite Datasets' and 'Overwrite Data Sources' were set to false.

    Thanks very much!

    Monday, October 31, 2016 1:47 AM