cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
norbyian
Frequent Visitor

Service Report stops returning data

Hi

We have a Sevice report. This is working fine,  then stops returning any data. The report refreshes without error but returns no data.

 

The fix is to download the report to desktop, refresh then up load. Then it works for a while then stops again.

 

The report uses a SQL server account and connects to an azure SQL DB using direct query. 

 

Any ideas why this happens and how we can fix ?

 

Thanks in advance.

1 ACCEPTED SOLUTION

Finally I think this has been solved. This is a direct query report, so the filter below requires a download and refresh 

 

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInCurrentMonth([Date])) 

 

The solution is to change the logic so the query is passed through to the database

 

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([ThisMonthFlag] = "Y"))

 

This makes sense now. but I am not sure if this behaviour is by design ?

View solution in original post

8 REPLIES 8
norbyian
Frequent Visitor

This continues to be a problem. Each month we make a change to some flags in the date dimension, current month, current week. It is a though the date dimension is not refreshed because when the flags change data is no longer returned. (there is a currentmonth=Y in the report filter.

 

The fix is to download the report refresh and upload. The client is doing this manually every month. I have tried tweeking the sql account. As said in posts above there are a number of schemas in the database. we have not given access to some schemas. I am goin to take that away and give db read to all schemas as I have a hunch that the bug is there.

 

Next will be be db owner and then full admin 😉 I wonder if the fix could be to not use sql authentication .... can we use an AAD account ?

Finally I think this has been solved. This is a direct query report, so the filter below requires a download and refresh 

 

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each Date.IsInCurrentMonth([Date])) 

 

The solution is to change the logic so the query is passed through to the database

 

#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([ThisMonthFlag] = "Y"))

 

This makes sense now. but I am not sure if this behaviour is by design ?

v-shex-msft
Community Support
Community Support

HI @norbyian,

 

Did you use azure sql datasource?

 

If this is a case, current it seems like a known issue on azure datasource, I think your scenario may due to engineers fix operations.

 

Reference:

Power bi Support

Users may not be able to set credentials if they are using OAuth2 for SQL Azure or AS Azure data sources. Engineers are aware of the issue and are working on a fix . Next Update @06/08/2018 14:30 PST

 

Regards,
Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks Xiaozin,

 

Yes it is a Azure SQL Datasource. Would the fix you suggested apply when using an SQL account?

Hi @norbyian,

 

This issue seems closed on support page, did this works on your side?

 

Regards,
Xiaoxin Sheneg

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi,

 

It has just happened again. Report is working fine then stops returning data. I download the dataset, renenter the SQL creadentials and publish. All is well again.

 

One observation I could make is that (on this accasion) the report is using a mixture of dbo tables and another schema. On this report I noticed that the dbo schema was returning data and not the other schema. Maybe this is a known issue ?

 

Thanks

Just happened again. Data is only returned from the dbo tables. Download the dataset and refresh then upload to service. All is working again. Client is not happy. Anyone any ideas? Can anyone at Microsoft pick this up ?

Been having the same issue. It broke earlier this week when the issues began. Now that it's "resolved", I had to delete the data source from service and republish from the desktop. It appears to be working okay now but we'll see how it goes. 

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors