Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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 ?

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thanks Xiaozin,

       

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi Anonymous,

         

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

         

        Regards,
        Xiaoxin Sheneg

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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 ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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 ?