Can't pull report of Summary Sheet fields into Power BI (using connector) or Excel using Live Data

Andrew Stills

I've been trying to run a summary sheet report on all my projects and pull this report into Power BI to perform calculations and/or charts from. I've tried using the PBI/Smartsheet connector and although it will pull in a regular report and/or sheet, it will not pull in a report based on Summary Sheet fields.

(Note the error... this does not occur on sheets or reports not based on summary sheets.)

Further, I tried to automatically load the report into an Excel file using Live Data Connector, which I would then load pull into PBI. This too failed to recognize any column data in the smartsheet report within the query wizard. Again, it will load a report based on regular columns and sheets.

(Note below: both the "Project Summary Report" and the "Project Workshop Order" are ticked but only the workshop shows table or column data)

Is there a trick I am missing here? My goal is to have the PBI charts and stats auto update business level metrics and pull them back into a Smartsheet Dashboard. My entire organization will be using these reports and many will have custom reports generated from the data so it is not feasible to have this be a manual process.

Any pointers are greatly appreciated.

Best Regards,

Andrew

Chris Mondeau

@Andrew Stills

Our organization uses reports that reference an entire workspace (this any newly added sheets to the workspace automatically get added to the report). Of course this report will have loads of sheets that probable aren't needed, but we filter the data down in SS reports with a unique column to that type of sheet, or a System Generated column that's unique (since Smartsheet Reports currently cant filter off of sheet names matching a criteria).

In PBI you can then source the report made in SS then run some data transforms or calculations in the query editor, or use some of the data visualizations to help group your data into a format that might work.

A SS alternative to summary fields is to create a separate sheet that uses external ranges to do all your calculations, just like in a summary field. This way you can everything calculated in SS, then just reference that sheet (or report if you have several calculation sheets) to source in PBI.

Hopefully that helps a little bit!

Chris Mondeau

Hi @Andrew Stills,

I had run into a similar issue in June 2020 when trying to use a sheet summary report in PBI (I got 400: Bad Error Request)

Smartsheet support informed that sheet summary reports are unsupported for now:

The error you encounter is presently an expected behavior. There is no API endpoint for summary reports, so there isn't anything to connect to. We recommend that you reach out to Microsoft to see if they can add the level parameter to the PowerBI call. For additional information, you may visit this link: https://powerbi.microsoft.com/en-us/blog/visualize-your-smartsheet-data-in-power-bi/

My work around was to build a global sheet report containing all the data and use a combination of filters and sql commands to sum my data.

Not sure if this helps, but maybe a SS guru can chime in with updates?

Andrew Stills

Thanks, @Chris Mondeau for the feedback.

Question: your work around of a "global sheet report," I assume this is a so-called roll up sheet?

First, it may be helpful to know, some of my data is only available in the Summary fields. I'm trying to avoid having to go back and create a roll up sheet for ALL of my projects and map the data to it from the Summary Sheet. Although this solution would be simple enough for projects moving forward, it is less elegant and potentially overcomplicates the project solution for future development.

My understanding is you cannot run calculations on a report and therefore my need to get this into PBI. If I am mistaken, where are you exporting the SS report to to run your SQL commands.

Or, better yet, is this all taking place in Smartsheet? This would be ideal as long as I can query the workspace (as a whole) for project data rather than add files manually to the report (filtering may work here)... but then I'm limited by not being able to ix row reports and summary sheet reports.

Definitely some good thoughts and direction to explore though. Thanks, again.

Chris Mondeau

@Andrew Stills

Our organization uses reports that reference an entire workspace (this any newly added sheets to the workspace automatically get added to the report). Of course this report will have loads of sheets that probable aren't needed, but we filter the data down in SS reports with a unique column to that type of sheet, or a System Generated column that's unique (since Smartsheet Reports currently cant filter off of sheet names matching a criteria).

In PBI you can then source the report made in SS then run some data transforms or calculations in the query editor, or use some of the data visualizations to help group your data into a format that might work.

A SS alternative to summary fields is to create a separate sheet that uses external ranges to do all your calculations, just like in a summary field. This way you can everything calculated in SS, then just reference that sheet (or report if you have several calculation sheets) to source in PBI.

Hopefully that helps a little bit!

Andrew Stills

Thank again Chris... I originally had a project roll-up sheet and moved away from it (and to Summary sheets) to maintain some better order in our file structures and to avoid having to filter the sheet titles for reports. The summary sheet allowed me enter project data quickly at project start-up and to run project specific calculations without having so many cell links out to the roll-up sheet (and the associated triangles muddying almost every cell from range calculations) or a bunch of hidden columns.

I only added summary sheets to the primary project sheet, as the support sheets didn't need the data and on the rare occasion it was needed it was easy to link it.

After reworking everything and putting together quite a comfortable and well organized system with easy project creation and very clean interface, I ran a summary sheet report which works well for capturing on the reporting data I need.

Same as you, we put our departmental projects into a specific workspace and pull the report off it... since it is a summary sheet report and unnecessary sheets do not have summary data (and common names it is easy to set up the report.

I moved all the projects into the system and supposed I would build the dashboards later based off the report. Hence my frustration when, after all this, I discovered the summary sheet report won't pull into PowerBI or Excel (or anything)!!!

Now I'm back to linking the data to a roll-up, a number of unnecessary hidden columns, and an additional sheet for every project to manage something that is readily available in the summary sheet section.

Here's how mine works now:

1) I added 6 hidden columns to capture the "field name" and various types of data (i.e. check box, date, contact, text/#, symbol) in the summary sheets

2) I created a section at the bottom of the project where I could place the data below our workflow (so adding/deleting lines would break the data) and then hid this using a filter and previous hidden helper column.

3) I linked the data in the summary sheet cells to the new columns

4) I created new roll-up sheet and linked all the data to it. It is essentially a mirror of the summary sheet but with the fields as the column names and the data as a single line of entry. I added one additional checkbox column to the sheet named PBI.

5) I created a new report on the workspace with a "When" filter for "PBI is checked"

6) Now I have a standard sheet report with which to import into PBI and run calculations.

All this just to be able to get summary sheet data out of Smartsheets Can you say enhancement request? 😉 It seems to me this would be a quick and easy feature add... but what do I know? The only computer class I've ever taken was keyboard in 1990... it included a free chisel and mallet!

Let me now if any of these ideas are helpful or if you have a more elegant solution.

Rebecca Cunningham

This is unfortunate to hear as I am running into the same issue. I created everything to be clean and run off of sheet summary information as opposed to hidden columns. No I have to go back in and add those columns in and create a different report/sheet that will then allow me to access the data from the Summary Report.

Have there been any updates since this last comment?

I was also looking into setting up an auto email of the summary report as an excel file, sending it to sharepoint, then using data shuttle the then upload/populate the latest version into a simple sheet. All to just get the information into PBI.

If anyone has any other suggestions please let me know. Would love if this was on the development roadmap to fix as this would allow me (along with countless others) to get my data into PBI without having to create a work around to get what is simply displayed in the summary report.

Thanks!

Andrew Stills

Hey Rebecca,

Thanks for the comment on my original discussion. Sadly, I cannot say I've found another solution or that I've been made aware of any changes.

That said, I have found some advantages to using the roll-up sheet on projects.

1) As projects become more complex they can often justify additional sheets and this is more easily mapped to a roll-up sheet.

2) many cell formatting options such as HTML links, colors, bold, italics, etc are not available in the summary.

3) Conditional formatting can be set on the roll-up sheet to bring additional value to your reports without the need for PowerBI. (I've even gone as far to add blank/shaded columns to segregate data in the reports to make it more presentable.)

4) I've found I have less reliance on PowerBI for displaying project information unless it is needed to combine data from multiple sources.

Feel free to reach out if you'd like me to take you through it on a teams call.

Cheers!