none
BI Sample reports not rendering in PWA 2010 RRS feed

  • Question

  • I am trying to finish my setup of the BI Sample reports on PWA 2010 on my Production server.  I followed the procedure from Microsoft to setup Excel Services and Secure Store Services exactly as instructed but when I test the sample Excel file I don't get any errors but it won't render the chart as shown.  I have setup this same procedure on a Remote Test server the same way and it does work properly so I obivously have something different somewhere but can't find it.

    There is one difference I did note; the Connection properties for the Excel Services Authentication Settings on the Production server shows as SSO (Single Sign On) and the same on the Test server shows SSS (Secure Store Services).  I think this is because we are running Office 2007 on the Production server but the Test Server has Office 2010.

    Could this be the issue and if so are there any solutions other than upgrading the production server to Office 2010?

    If this is not the problem what else can I try?

    Forrest

    Wednesday, January 25, 2012 4:48 PM

Answers

  • I just wanted to end this on a good note.  Found the problem althought it did take my Partner and 3 others from Microsoft to find it.

    The Resource Capacity Settings under PWA Server Settings-->Additional Server Settings was not set correctly to look back far enough.  But the main issue was that the SQL Server Agent that runs this job to update after hours had stopped.

    Once the SQL Server Agent was restarted it updated and the BI Sample ResourceCapacity chart rendered properly.

    Hope this helps the next person.

    Thanks for everyones help

    Forrest

    • Marked as answer by Forrest Thursday, February 2, 2012 9:20 PM
    Thursday, February 2, 2012 9:19 PM

All replies

  • Hi,

    Which of the sample reports are you trying to run? Some of them won't show anything unless there is data in the instance, and the cubes have been processed.

    You mention the server has Office 2007 / 2010 installed? It's not a great idea to install the client applications on the server as they are single threaded and can lead to performance issues, the rendering of Excel on the server should be handled directly by Excel Services. To get the sample reports to render, it should just be a case of clicking on the report which will cause Excel Services to open up and render the report. How are you trying to open the reports?

     

     

     

     


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Wednesday, January 25, 2012 9:53 PM
    Moderator
  • Hi Alex,

    Let me be a little clearer.

    If I log onto either Server and click on the Sample report "Resource Capacity" it will render the chart on the Test Server in the browser that comes up but it will not render in the browser on the Production server.  There is an option to edit in Excel on the browser window.  If I click that on the Test server it will come up in Excel 2010 which I have loaded on the Test server.  On the Production server, which will not render the chart in the browser, I click on the Edit in Excel button but will not start the application because there is no Excel loaded on the Production server.  So I brought up PWA on my local browser to repeat the steps which I then was able to Edit the browser Sample file from my local machine which has Office 2007.

    I believe I do have some data enough to produce this chart.

     

    Forrest

    Wednesday, January 25, 2012 10:16 PM
  • Alex,

    By the way I do get this message when trying to Edit the browser file on the Production server.

    To open this workbook, your computer must have a version of Microsoft Excel installed and your Web browser must support opening files directly from Excel in the browser.

    If I don't have Excel loaded on the Production server how can it support opening files directly from Excel in the browser?

     

    Forrest

    Wednesday, January 25, 2012 10:36 PM
  • Hi,

    Thanks for the clarification. Here are some ideas to help you troubleshoot:

    • Are there any errors coming up in the Excel Services window when you click on it the first time?
    • Can you verify that the Secure Store Service Account has been granted access to the Reporting DB?
    • Can you verify that the MSP_EpmResource_UserView in the reporting db has data in it? That report uses that view
    • Verify the reporting on the production box has been set up as per http://technet.microsoft.com/en-us/library/ee662106.aspx

    Let us know how you go.

     


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Wednesday, January 25, 2012 10:49 PM
    Moderator
  • Alex,

    I'm pretty new to this so bear with me because I'm not an IT guy.

    • When you say Excel Services window are you referring to Central Admin --> Excel Services Application?
    • Yes to the 2nd.
    • Can you give me a way to check this for #3
    • There was a MS video on this that I followed for the Test server which worked as described so I followed the same procedure on the Production server.  I did not follow the technical bulletin you list.

    Forrest

    Wednesday, January 25, 2012 11:01 PM
  • Hi Forrest,

    Sorry, to clarify the first question, when you navigate to Business Intelligence > Sample Reports in PWA, and then click on the report, do you see an error? In fact, can you advise what you see?

    Q3 - Access the db using SQL Server Management Studio and run a select count(*) from MSP_EpmResource_UserView  in the Reporting DB. If you get more than a number more than zero returned, you have data.

    Q4 - I would verify your set up against the Technet article as it is kept current with learnings post RTM, which the video may not have.

    Good luck

     


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Thursday, January 26, 2012 12:04 PM
    Moderator
  • Thanks Alex,

    When I select the Sample report in question from Business Intelligence on PWA I get an Excel look in the browser with two tabs at the bottom. One labeled Chart with a blank Rectangle where the chart should show and a Tab label Data with a place for Row Labels going down and Values going across but there is no data listed.  Across the top outside of the sheet are three options; Edit in Excel, Data and Find.  There are no error messages.

    I'll get back to you on Q3 and Q4.

    Thanks

    Forrest

    Thursday, January 26, 2012 2:48 PM
  • Hi Alex,

    I was able to verify that there is data in the Reporting DB.  I got a number 337 when I ran the query.  So I am now running back thru the Technet Article you provided.

    Forrest

    Thursday, January 26, 2012 4:01 PM
  • Hi Alex,

    The only thing I can find from the Technet article that was not in the video is to Install SQL Server 2008 Analysis Management Objects.  But it is also not installed on the working Test server either.  Do you think that is required for this issue?

    I have SQL Server 2008 Analysis Services ADOMD.Net installed on both servers but not sure if that is close to the same or not.

    Other than that it appears to be the same.  Would it be worth trying to reset Credentials for the Secure Store Target Application?

    Thanks

    Forrest

    Thursday, January 26, 2012 6:45 PM
  • Did you install SP1?

     


    Thanks, Kashif
    Thursday, January 26, 2012 9:52 PM
  • Yes Sir it is on SP1

    Thanks

    Forrest

    Thursday, January 26, 2012 9:53 PM
  • Try this though its a very weird thing but as nothing is helping you, you have no other option. Just create new blank Site Collection on the root level from Central Admin and then try again. Let me know if this helps.

     


    Thanks, Kashif
    Friday, January 27, 2012 6:24 AM
  • There are a number of different Site Templates to choose from.  My inclination is to use the Collaboration/Microsoft Project Site template since we will be using this in that capacity.  Would there be a better choice or does it even matter?

    Forrest

    Friday, January 27, 2012 3:00 PM
  • Just for the sake of resolving your BI issue, create blank Site Collection and check, later on you can create whatever you like :)

     


    Thanks, Kashif
    Friday, January 27, 2012 3:43 PM
  • Hi,

    So the report is coming up, but with no data being displayed.  Typically if you are missing a top level site post SP1 it throws an error or just sits on loading. Is there a javascript error being seen in the browser? If so please post it.

    My only other guess is that Excel Services / Business intelligence is not set up correctly. When you set up the secure store etc, did you ensure name of the ApplicationID followed the case sensititive one stated in the technet article. Also in Excel Services, are the trusted libraries and connections set up the same?

    It's hard to narrow it down without see the environment, your best bet might be to reach out to your local SharePoint / EPM partner or raise a support call with MS.

     


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Sunday, January 29, 2012 1:27 AM
    Moderator
  • Alex,

    I believe I have entered all parameters correctly but I'd like to go back and run thru it once more to make sure and to recheck those items you mentioned.  Is there any problem with stepping back through each of these steps in the Tecnet Article?  In the Secure Store Setup area of this setup are generation of Keys and Credential setup steps.  If I redo these step will that cause any issues I need to know about?

    Thanks, Forrest

    Monday, January 30, 2012 3:26 PM
  • I just wanted to end this on a good note.  Found the problem althought it did take my Partner and 3 others from Microsoft to find it.

    The Resource Capacity Settings under PWA Server Settings-->Additional Server Settings was not set correctly to look back far enough.  But the main issue was that the SQL Server Agent that runs this job to update after hours had stopped.

    Once the SQL Server Agent was restarted it updated and the BI Sample ResourceCapacity chart rendered properly.

    Hope this helps the next person.

    Thanks for everyones help

    Forrest

    • Marked as answer by Forrest Thursday, February 2, 2012 9:20 PM
    Thursday, February 2, 2012 9:19 PM