Restrict rows retrieved by query?

SamTrexler - it will be possible to implement these parameters in the SQL Query - I do it in a "content pack" I have created for our customers using Microsoft Dynamics NAV. The problem with creating a dynamic model for Dynamics NAV is that the tables, where the data is stores, is prefixed with their company name so table names is always different from customer to customer.

So what I did was creating a file with 3 free text parameters Server, Database and Company and I pass all 3 parameters on to the queryes against the source SQL database.

As I read the thread free text paramaters will not cut it for you and there is some limitations that you need to be able to fullfill if this is the right way for you to go.

1. You need to be able to create full parameter lists with all the values your users would want to filter on.

2. It will satisfy your users need only to be able to select on value for any of your parameter lists.

2. I don't think it will work for you if your users will need to be able to filter on a lot of different columns in your solution

3. You need to be able to write the query so it can handle that your users only filter perhaps 1 out of x parameters in your model.

Basically you will have to write one SQL statement that can handle all the mixes of parameter selections made by the user. The user however is limited to only being able to select the values of the parameters provided by you and from reading your previous posts this just seems to be to simple compared to what you want?

However if you want to test this I will give you an example of how you can use parameters in your SQL query.

In this example I wish to return all the customers from my customer table where country is US - the country is what my user selected from a parameter list containing all the possible countries in my table. In this example I have called my parameters list CountryParameter

let
    Source = Sql.Database(Server, Database, [Query="
--SQL Query Start
SELECT
	a.DWID_Customer AS 'CustomerKey'
	, a.CustomerNo AS 'Customer No.'
	, a.Name AS 'Customer Name'
	, a.CustomerLabel AS 'Customer'
	, a.CustomerPostingGroup AS 'Cust Post. Group'
	, a.Country AS 'Country'
	, a.City + ', ' + a.Country AS 'City'
FROM D_Customer a
WHERE a.Country = '" & CountryParameter & "'"
--SQL Query End
    "])
in
    Source

I hope it makes sence I am a bit tired.. he he