+1 800 256 3608 (toll-free in North America) or +49 7531 90 60 10| service@combit.com

Report Server: Dynamic SQL in query

(Carlos Carvalho) #1

I suggest to implement the following feature:
Allow bind/substitution variables in a query. Allow a query to be changed with user/application parameters.

A typical use case might be:
Using Acess Control a user could be limited to see some privileged info, therefore the retrieved data must be filtered.
The where condition needs to be dynamic to allow different data retrieval depending on each user privileges.

Although this represents some security issues (SQL Injection), due to RGPD some sensitive info might be displayed, or not, depending on user privileges (e.g. address, phone number, etc…).

(combit - Jochen Bartlau) #2

Would using parametrized stored procedures or table valued functions be an acceptable workaround here? If so, I’d update the status to “planned”. If not - what would be required additionally?

(Carlos Carvalho) #3

Can those parameters be from outside the query?
Meaning, this:
select pkg.method(t1.columnA) from table1 t1 where columnB = ‘abcd’
is not equal to:
select pkg.method(:bind) from table1 t1 &SubstitutionWhere
What do you think about?

(combit - Jochen Bartlau) #4

Not sure if I completely follow you. A stored procedure might have a parameter of “any” type and can use it in its query. A simple example courtesy w3schools would be

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

We’ll offer means to freely parametrize the @City parameter in Report Server 25. I’ve posted a design study of the (not yet localized) UI in this thread a couple of days ago, should be pretty self explaining:

(Carlos Carvalho) #5

Hello Jochen Bartlau,

Version 25 looks will improve it by adding procedures but can @CompanyName and @CustomerID(from your image) values be given by calling the combit API execute report method?

Regards,

Carlos

(combit - Jochen Bartlau) #6

If you’re using the Client API Assembly, you pass a PreparedReport instance to the ExportAsync method. This can hold the required report parameters. The following is an excerpt from the Client API sample. The full sample is also on GitHub:

// Prepare an export and set the options:
PreparedReport preparedExport = _rsClient.Exporter.PrepareExport(reportTemplateId, exportProfileId);
preparedExport.DisableCaching = chkDisableCache.Checked;

// Copy the rows of the report parameter listview to the report parameter list of the export:
foreach (ListViewItem listViewItem in lvReportParameters.Items)
{
	string parameterName = listViewItem.Text;
	object paramterValue = listViewItem.Tag;

	preparedExport.ReportParameters.Add(parameterName, paramterValue);
}
await ExportAndDownloadFiles(preparedExport);