none
Sending visual report by email with data RRS feed

  • Question

  • I am using a local copy of MSPROJECT Professional 2010 and Office Professional 2010.

    I am trying to send a copy of an excel visual report data by email such that people can use the pivot table features on the data.

    I have created/saved usage templates that I use.  I run the visual report template and save this excel file to my local machine.  I separately saved the project OLAP CUB data on my machine.

    But for the life of me I cannot figure out how to link the two so that my saved excel file links to the OLAP Cub Data, nor do I know exactly what needs to be included in the email instructions for folks to use the files.

    Am I approaching this incorrectly?  All I want to do is send the pivot table out to people so they can view and manipulate the reports.

    Thanks in advance.

    Friday, July 26, 2013 11:53 PM

Answers

  • I have finally found the answer to this. Excel visual reports absolutely do link to an OLAP cube to provide the data for the report. After much messing around, I found a simple solution that works all the time. Follow these steps:

    1. Select the visual report that you want to produce. Select the level of usage data you want to include in the report (days, weeks, months, quarters, years)
    2. Click the save data button. Pick a cube from the drop-down list to use - I usually pick Assignment Usage
    3. Click save cube to save the data to a safe network or local disk location with a project specific unique name
    4. Create a brand new Excel workbook which is blank in the same or similar location to the cube
    5. In row 1 col 1, insert a new Pivot table
    6. In the following dialog box, select Use an External Data Source. Click Choose Connection. Click Browse for more and navigate to the cube directory you just saved. Pick the cube and click open. Click OK
    7. Your pivot table will now exist and be connected to the OLAP cube you saved
    8. Use  the pivot table fields to create the exact same report as the built-in Project / Excel fields - this is all variable as it depends on what data you want to see in the report
    9. Save the excel workbook with a Project specific name and finally everyone who opens / navigates the workbook will see accurate cube data, exactly as it was in MS Project
    10. If you email this around, ensure that the files are saved to a local directory structure exactly the same as when the workbook was created (as the data connection properties points to that specific location)

    Hope this helps you all as it was driving me mad.

    Regards Marc UK

    Thursday, February 13, 2014 11:11 AM

All replies

  • ctslaton --

    Excel Visual Reports do not link to the local OLAP cube in any way.  The Excel Visual Report is simply a static report of the data collected in the OLAP cube and displayed in an Excel PivotTable.  So, send the Excel Visual Report to the people who need to see it and tell them they can manipulate the PivotTable like any other Excel PivotTable.  They do not need the OLAP cube to do this.  Hope this helps.


    Dale A. Howard [MVP]

    Saturday, July 27, 2013 3:03 PM
    Moderator
  • Thanks Mr. Howard.  I must be doing something wrong.  After I save the .xlsx file, close project, and then reopen the excel pivot table.  The data shows up fine but if I try and move or add/delete fields I get the following pop-up error messages depending on what i tried:

    "Excel cannot find OLAP cube ProjectReport.  Either the OLAP database has been changed or you don't have permissions to connect to the cube.  Consult your database administrator."

    or the following message:

    "Data could not be retrieved from the external data source.  Error message returned by the external data source:  XML for Analysis parser: The CurrentCatalog XML/A property was not specified."

    Any thoughts?


    • Edited by ctslaton Wednesday, July 31, 2013 4:58 PM
    Wednesday, July 31, 2013 12:34 AM
  • Thanks Mr. Howard.  I must be doing something wrong.  After I save the .xlsx file, close project, and then reopen the excel pivot table.  The data shows up fine but if I try and move or add/delete fields I get the following pop-up error messages depending on what i tried:

    "Excel cannot find OLAP cube ProjectReport.  Either the OLAP database has been changed or you don't have permissions to connect to the cube.  Consult your database administrator."

    or the following message:

    "Data could not be retrieved from the external data source.  Error message returned by the external data source:  XML for Analysis parser: The CurrentCatalog XML/A property was not specified."

    Any thoughts?

    Friday, August 2, 2013 4:00 PM
  • Ctslaton,

    I have tested several scenarios. I think the issue is with the Compatability of versions.

    Which version of Excel are you using. I will assume it is 2010. In that case, before you actually save the report as an excel file, navigate ( In Excel) to File >> Convert, and save it as an XLSX file.

    You will get a dialog saying the conversion was successful and you need to close and reopen.

    Once you reopen, you should not see the error above.


    Prasanna Adavi,PMP,MCTS,MCITP,MCT http://thinkepm.blogspot.com

    Friday, August 2, 2013 5:43 PM
    Moderator
  • Thanks Mr. Adavi.  I am using excel 2010 and MSPROJECT 2010.  I do not see a file>>Convert option that you mention but if you mean file>Save As and then choose xlsx, that is what I was doing.  It is after I reopen it that I get the errors.



    • Edited by ctslaton Saturday, August 3, 2013 5:18 AM
    Saturday, August 3, 2013 5:15 AM
  • Thanks Mr. Adavi.  I am using excel 2010 and MSPROJECT 2010.  I do not see a file>>Convert option that you mention but if you mean file>Save As and then choose xlsx, that is what I was doing.  It is after I reopen it that I get the errors.



    Hi, just bumping the thread to see if I get any replies.  Still not sure if it is something on my machine/setup or just the way the software is supposed to act.  Any help appreciated.  Thanks.
    Monday, August 12, 2013 11:19 PM
  • Hi,

    I've found that MS Project 2010 Visual Reporter creates link  in MS Excel 2010 file to the temporary location, for example: C:\Users\[user name]\AppData\Local\Temp\Visual Reports Temporary Data\{57f11963-0693-4002-b22d-3ffc551fb9ef}\AssignmentTP.cub

    After you close MS Project file the link is not active anymore and it is not possible to use MS Excel Pivot functionality. Pivot Table functionality doesn't work without the link.

    Also I've found that the link in MS Excel file (Connection string) is incorrect and doesn't exists, because of that, the standard MS Excel 'Insert Slicer' functionality doesn't work until you manually correct the link.

    What I can do:

    1. Save .cub and MS Excel report files to the permanent location and recreate link between them. In that scenario you are not able to send your MS Excel file by e-mail.

    2. Create MS Excel template with the option  'Repeat all item labels', run MS Project Visual report, copy data to the another MS Excel file and create local Pivot Table there. 

    Both ways are not perfect.

    • Proposed as answer by marc9 Thursday, February 13, 2014 10:47 AM
    • Unproposed as answer by marc9 Thursday, February 13, 2014 10:47 AM
    Monday, November 11, 2013 12:38 AM
  • I have finally found the answer to this. Excel visual reports absolutely do link to an OLAP cube to provide the data for the report. After much messing around, I found a simple solution that works all the time. Follow these steps:

    1. Select the visual report that you want to produce. Select the level of usage data you want to include in the report (days, weeks, months, quarters, years)
    2. Click the save data button. Pick a cube from the drop-down list to use - I usually pick Assignment Usage
    3. Click save cube to save the data to a safe network or local disk location with a project specific unique name
    4. Create a brand new Excel workbook which is blank in the same or similar location to the cube
    5. In row 1 col 1, insert a new Pivot table
    6. In the following dialog box, select Use an External Data Source. Click Choose Connection. Click Browse for more and navigate to the cube directory you just saved. Pick the cube and click open. Click OK
    7. Your pivot table will now exist and be connected to the OLAP cube you saved
    8. Use  the pivot table fields to create the exact same report as the built-in Project / Excel fields - this is all variable as it depends on what data you want to see in the report
    9. Save the excel workbook with a Project specific name and finally everyone who opens / navigates the workbook will see accurate cube data, exactly as it was in MS Project
    10. If you email this around, ensure that the files are saved to a local directory structure exactly the same as when the workbook was created (as the data connection properties points to that specific location)

    Hope this helps you all as it was driving me mad.

    Regards Marc UK

    Thursday, February 13, 2014 11:11 AM
  • Thanks Marc.  Do you know if you can then update just the cube each time and reconnect the data link, and reuse the formatted excel reports that were developed?  Trying not to recreat/reformat the excel pivot table each time.

    Thanks in Advance.

    Thursday, February 13, 2014 11:53 PM
  • Yes you can. I simply open MS Project, follow the steps above again (i.e. saving the cube) and overwrite the existing cube. Then open the excel workbook connected to that cube and expand/collapse the data ranges (in my case monthly calendar) and the report data is instantly refreshed from the new cube data.

    I then just cut & paste the report data into a customised / consolidated spread-sheet with loads of automated calculations to see great dashboards for all our projects.

    Hope this helps.

    Friday, February 28, 2014 11:07 AM
  • hi Marc,

    I have tried above mentioned process step by step.  It doesn't work when I send the excel and.cub file over email. Recipient is not able to change the filters i.e. I have saved and generated pivot table with month wise filter.  When recipient is trying to change the the month from January to any other month. It gives connection error.

    Monday, April 16, 2018 2:43 PM