Change 'publish to service' reports to 'get data from file'

[Rbernhardt]

I saw this blog post, which mentions if you use Get Data on service and select a PBI file from sharepoint/one drive it will auto update once you make edits as opposed to having to manually publish all updates.

I have a good 200 odd reports which were uploaded via the desktop publish feature and they all have a web link we shared with our suppliers (I know, not a good business practice, but its Premium is expensive).

Any time I update the 'base' report I have to make the 200 odd copies and re-publish each and everyone of them. This take a good 4 hours.

Is there a way to replace the reports on service so they auto grab updates from sharepoint/one drive WITHOUT having to delete them (which will kill the web links).

[avatorl]

I'm not sure I understand what you're trying to get with onedrive... If you need 200 reports for 200 different customers (and you have no dedictated capacity to use RLS) then Powershell Cmdlets / REST API is what will help you to automate the process and create 200 reports (same design, but different data in each report) automatically (in one click). Then, you'll need to create 'publish to web' links manually for each report (I'm afraid there is no API method to do that, but you need to to that only once for each report (customer). Then you can update all 200 reports in one click (republish same .pbix with different parameters, so each report contains only data of a specific user), it won't affect existing 'publish to web' URLs.

[Rbernhardt]

I already have the reports.....read the article I posted. It says if you use Get Data on service and select a PBI file, any changes that are done to the file fill be automatically published once the file is saved. You dont have to do it manually. But this only works if you use Get Data.

All the files I published, was using the Desktop version's Publish to Web. 

[avatorl]

---

@Rbernhardt wrote:

I already have the reports.....read the article I posted. It says if you use Get Data on service and select a PBI file, any changes that are done to the file fill be automatically published once the file is saved. You dont have to do it manually. But this only works if you use Get Data.

---

I think I undersatnd you. You published 200 reports from the Desktop (and then shared them as 'publish to web') . It means you have 200 dataset/report pairs and now you want to link all the reports to OneDrive .pbix file. But I assume all your reports have different parameters (e.g. customer name, otherwise it makes no sense to have 200 reports). Now let's say you saved .pbix file into OneDrive and you managed to link existing reports to the OneDrive file (which I don't think is possile). When you will save a new version of the .pbix file into OneDrive - all 200 report/dataset pairs will be replaced with the latest version of the .pbix file. It won't kill 'publish to web' links, but it will overwrite all parameters and you'll get 200 identical reports which makes no sense. Use REST API. Don't use OneDrive.

[Rbernhardt]

Half correct.

Changes to the dataset are reflected on all 200 reports, yes. But if I make a visual change, add a new page etc....I have re-publish a new copy for all 200 (and create the 200 template files again).

The way I do the full process is:

• Create a report then publish to service
• Make a copy of said report
• Delete all connections inside the reports (both power query and dax) - Visuals and whatnot will appear broken
• Click Get data and select the freshly published dataset
• All visuals are now working again
• Apply a filter for all pages (X customer, Y customer, etc...)
• Save as, give it a unique name
• Select next filter and save again....repeat 200 times

[avatorl]

So, it's a live connection to one dataset and repot level filter... Wow, it's painful to manage. You won't be able to automate this. You need to change your approach and OneDrive is not a solution at all. OneDrive refresh replaces both dataset and report.

See the video from my first comment. This is what you need. Apply a paramter based filter in Power Query. Use Powershell to publish 200 copies of the report (report/dataset pairs) and to udpate the parameter (customer name) in each dataset. Lesson 6 in the video is about what you need (just with different countries instead of different customers). You'll need to recreate all you 'publish to web' links once, I don't think you can avoid this problem. But it will save you a lot of time for all future updates.

[avatorl]

I'm sorry, I don't understand. What is 'Desktop version's Publish to Web.'? You create 'publish to web' link in the Service.

If you created 200 reports and then shared them using 'publish to web' link then you can use API to replace all the reports without losing the existing 'publish to web' URLs. But I'm not sure how API will work with a dataset linked to OneDive .pbix file. And I don't thik OneDrive is supposed to be used for this purpose. But I'm sure that Powershell is what can be used to manage multiple reports when you need 200 reports for 200 customers and you have no dedictated capacity.

[Rbernhardt]

I tried to use Get data in the same workspace and select the same file that was uploaded using the publish to web, but all it did was add a duplicate report with the same name.

[avatorl]

Well, it's your responsibility to your customers to use or not to use 'Publish to Web'. But if you need to create 200 copies of a report than use PowerShell. You can publish your .pbix file 200 times with different names, update parameters in each report with different values and refresh all the reports automatically.

See Exercise 6 in this video https://www.youtube.com/watch?v=WaKvZgjTWmo