Gateway Refresh - Query contains unknown or unsupported data sources. (null values)

[jfenico]

I have a Power Bi template that I send out to different parts of our organization. Depending on which department is viewing the report, the template can specify a certain number (up to 5) of servers in the report. I have the report written using parameters where the user creating the report can easily input the server names.

So if user A has 5 servers they want to query, they input 5 server names, and there are a few tables where I append all of the results into the report.

If user B only have 4 servers, they input 4 server names and the same thing occurs. However, I put in an If statement basically saying if parameter for server 5 = null, then the table will be null and we ignore it during the appending of data.

The report itself works fine in Desktop but I cannot get them to refresh because of the refresh error above in the title. I suspect the Source = null for any blank parameters might be the issue but would need to get confirmation from someone. If that is indeed the case, any workarounds?

[jfenico]

Digging a little deeper into this, if I remove the blank parameters and their associated queries, it seems the refresh is enabled again. Kind of puts a damper on the usefulness of the templates, hoping there is a way around this.

[GilbertQ]

Hi there

I think it has to do with a Parameter cannot be NULL.

What about if you had to change the logic, where if a user does not need the server they put in "N/A" into the Parameter. You can then change your if statement to look for "N/A" and then apply the logic there after?

[jfenico]

Hi @GilbertQ

I think we're getting close but when I changed it to N/A, a new datasource appears. The gateway is no longer gives me the error but is now looking for credentials for a bogus datasource named "N/A".

[GilbertQ]

Ok, so what if you let the users put in a connection to a valid source but that source doesn't return any data?

So that would work for the gateway because it would be able to connect. But it won't actually run a query. You could then use this data source name in your existing logic to change it from N/A to this data source?

[jfenico]

@GilbertQSo you're saying I should create a parameter for the data source as well? Or maybe I'm missing something.

[GilbertQ]

Hi there,

What I was saying is to create another data source. And create that data source in your gateway

[jfenico]

I see what you mean, I guess I could give that a shot. My only issue is coming up with an empty datasource. We query everything from a MySQL database (2 databases to be exact).

[GilbertQ · Accepted solution]

Hi there,

I see that you said that there were 5 server names?

Could you not create a 6th one that points to an existing server so that you know when it should not be used?

[jfenico]

@GilbertQThat did it! Thanks again buddy. Definitely an interesting solution but I see no reason why this won't work for our other offices in the future.

[GilbertQ]

That is awesome, glad you got it working!