topic Re: New Workspace Dataset Parameter Properties in Service

New Workspace Dataset Parameter Properties

Anonymous — Thu, 14 Feb 2019 21:54:45 GMT

Hello,

I am not able to edit the parameter on a dataset in the settings of the New Workspace environment. I published the same report to both versions of the workspace and I am able to make the appropriate change in the first version; however, unable to edit the text in the latest version. This is an incredibly important feature when trying to switch between dev, test, and prod environments for SQL Servers.

Thank you,

Re: New Workspace Dataset Parameter Properties

GilbertQ — Fri, 15 Feb 2019 02:27:11 GMT

Hi there

Can you confirm if your Parameter data type is set to Text or Any?

If it is set to Any it will be greyed out.

Re: New Workspace Dataset Parameter Properties

Anonymous — Fri, 15 Feb 2019 15:31:08 GMT

Thank you - I thought I had the parameter set to text when I published to the Service but it must have been published as Any. I published as text and was able to edit the parameter. I did get an error; however, this could be due to a different issue I am having with unsupported data sources for refresh...

Maybe you can help with this issue I am having:

I have 30 reports that have typically 5+ SQL Server connections to a server that needs to be migrated due to a version update of our ERP. I've been trying to engineer a solution to be able to toggle back and fourth between servers for all of the connections in all of my reports using the following logic:

1. I created a table with two columns [Server],[Status] (status being a true/false identifier to choose which server to use)

2. I created a stored procedure to change the [Status] between different servers

3. I connect to the table in all reports, filter for [Status] = 1, then drill down on the [Server] to get the output of the server name

4. I then use the name of the query in step 3 as the source for all of my SQL Server connections.

I can get the report to refresh in the desktop after toggling between servers but when I publish to the web I cannot get the refresh schedule to work. Each sql source shows the same error:

Query contains unknown or unsupported data sources. SQL, ************.norco.com;DataAnalytics

I used the ********* to replace the actual server name

Any idea's on how I can use my intended idea and work around this error? I've tested this with the server name only and with the full domain as well. Both times I get the same error. What's interesting is that the server name in the error does not change when I toggle between the two servers (server1 is published and the error is still associated with server2) which leads me to believe this is a gateway issue.

Re: New Workspace Dataset Parameter Properties

GilbertQ — Sun, 17 Feb 2019 20:15:52 GMT

Hi there

Do you have both data sources set up in your Gateway?

Re: New Workspace Dataset Parameter Properties

Anonymous — Wed, 20 Feb 2019 14:55:19 GMT

Hi,

What is the data source when you use a SQL server connection but then drill through. I have the SQL server/database gateway established but the icon in Power Query after the drill through step appears to convert the data source to text.

The Servers query is identified with the ABC icon but you can clearly see from the M code above that I have navigated to an object on a sql server. Also, you'll see the parameters below but I am trying to avoid this solution because I want to bulk change 30 reports by simply executing a stored procedure.