cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
norbyian Frequent Visitor
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

Accepted Solutions
norbyian Frequent Visitor
Frequent Visitor

Re: Service Report stops returning data

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
Community Support Team
Community Support Team

Re: Service Report stops returning data

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
norbyian Frequent Visitor
Frequent Visitor

Re: Service Report stops returning data

Thanks Xiaozin,

 

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

Community Support Team
Community Support Team

Re: Service Report stops returning data

Hi @norbyian,

 

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

 

Regards,
Xiaoxin Sheneg

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



For learning resources/Release notes, please visit: | |
norbyian Frequent Visitor
Frequent Visitor

Re: Service Report stops returning data

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

norbyian Frequent Visitor
Frequent Visitor

Re: Service Report stops returning data

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 ?

TheNov3mberist Regular Visitor
Regular Visitor

Re: Service Report stops returning data

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. 

norbyian Frequent Visitor
Frequent Visitor

Re: Service Report stops returning data

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 Smiley Wink I wonder if the fix could be to not use sql authentication .... can we use an AAD account ?

norbyian Frequent Visitor
Frequent Visitor

Re: Service Report stops returning data

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 55 members 1,170 guests
Please welcome our newest community members: