Forum Discussion

Anonymous

Not applicable

6 years ago

Credentials are required to connect to the SQL source when using parameter for query

Hello, I have a report that is scheduled to refresh everyday and it has been working fine until yesterday. I noticed that it failed to refresh and it looked like gateway not configured correctly...

Need Help

Anonymous

Not applicable

6 years ago

Hi GilbertQ ,

So I created two reports, the first one is calling stored procedures test.spTest and passing in 3 like below

let
Source = Sql.Database("server", "database", [Query="exec [test].[spTest] @SiteNo = 3"])
in
Source

The second report calling the same stored procedures but passing in the parameter SiteNo. Both of reports are working fine in Power BI desktop.

let
Source = Sql.Database("server", "database", [Query="exec [test].[spTest] @SiteNo = " & Number.ToText(SiteNo)])
in
Source

When I publish to my dashboard, I can see the gateway and able to select the data source then refresh the first report but get the message 'not configured correctly' when turn on gateway on the second report. This just happened recently as of June 25. Could you please try create a simple report with one parameter and see if it works for you? Also, I notice if the parameter I have as string then it's working but if parameter is number and I have to use Number.ToText then it's not working. Same as using Date.ToText() then it's not working.

Anonymous

Not applicable

6 years ago

I have a similar problem.

Same reports works on desktop PBI but does not work when trying to update data from web using a gateway.

Examples:

I build the query with strings/variables concatenation.

Depending how I set the concatenated variable, it works on both the environments or only from desktop:
variable = "" WORKS
variable = if 1=1 then "" else "" WORKS ONLY FROM DESKTOP
variable = "'" & "0" & "'" WORKS
variable = "'" & Number.ToText(0) & "'" WORKS ONLY FROM DESKTOP

(These examples are real tests made after I became desperate)

Anonymous
Not applicable
6 years ago
Hi Anonymous ,

It seems to me when use Number.ToText(0) or Date.ToText() then only work in desktop and it's not working when publish. This just happened recently. Not sure if need to report this issue.
- GilbertQ
  Super User
  6 years ago
  Can you show us what your data types are set to in the Parameters in your PBIX?
  - Anonymous
    Not applicable
    6 years ago
    Hi GilbertQ ,
    
    This is my steps:
    
    1. Created a stored procedures called spTestVy (see below)
    
    2. In my Power BI report, added Parameter called 'SiteNo' with type interger
    
    3. In my power query, I have the following. Need to use Number.ToText() to convert. If not, I'm getting error can't concatenate string with number
    
    4. In my report, only display this
    
    5. Published the report to my dashboard. Go to settings -> under Gateway connection, I get the message not configured correctly.
    
    6. Now if I change my parameter type to string then the gateway can see datasource.
    
    Something changed because it was working before when having the paramter as integer.