SQL statement with parameters in groups

[itds]

Hi,

we have a little question concerning SQL in Stimulsoft Reports.Net.

First of all we created a new datasource based on a SQL statement like the following one:

Code: Select all

SELECT DISTINCT [tSoftwareCache].[SoftwareCacheID], [tSoftwareCache].[SoftwareName], [tSoftwareCache].[SoftwarePublisher], [tSoftwareCache].[SoftwareVersion], [tSoftwareCache].[OSType] 
FROM [tSoftwareCache], [tSoftwareKeywords], [tSoftwareProducts], [tSoftwareGroups]   
WHERE [tSoftwareCache].[SoftwareName] LIKE [tSoftwareKeywords].[SoftwareName]   
AND [tSoftwareCache].[SoftwareVersion] LIKE [tSoftwareKeywords].[SoftwareVersion]   
AND [tSoftwareCache].[SoftwarePublisher] LIKE [tSoftwareKeywords].[SoftwarePublisher] 
AND [tSoftwareProducts].[SoftwareID] =? AND [tSoftwareGroups].[GroupID] = [tSoftwareProducts].[GroupID] 
AND [tSoftwareGroups].[AccountID] = [tSoftwareCache].[AccountID] 
AND [tSoftwareKeywords].[KeywordType] = 0   
AND [tSoftwareKeywords].[SoftwareID] =?
...

As you can see the statement contains two unnamed parameters, the whole statement contains more but we think that this fact is not relevant for the problem. We would prefer using named parameters but here in the forum we read that this is not possible with an OleDb connection - and it really does not work.

The specific problem now is that the SQL statement from above is only executed at the compiling time of the report. Our report has the following design.

Code: Select all

...
Group: [tSoftwarePorducts].[SoftwareID]
    Virtual Table: SQL statement from above with current [tSoftwareProducts].[SoftwareID]
        ...
Group Footer
...

So as you can see we need the SQL statement from above to be executed more than once with different parameters for each item in the group. How can we achieve this purpose? Because of the like statements it is impossible to execute the statement at the beginning without SoftwareID and use filters during group building. Is this possible and if yes how can we pass the SoftwareID from the current group to the SQL statement.

Thanks in advance. :feelgood:

[Edward]

Hi

Please set ConnectOnStrart property of the detail sql DataSource in false, ReconnectEachRow in true. And there is a possibility to use variables from the Dictionary in sql queries like this:

select * from Categories where CategoryID ={myVariable}

MyVariable can contain e.g. 1

Thank you.

[itds]

Ok, thanks so far, but how is it possible to pass a parameter to the sql query depending on the base group. As you can see in tghe example above the SoftareID from the current group should be passed to the sql statement before executing. How does this work, either using a variable as you described or direct? We could not find a way to take the datafield from the table above and pass it to the sql statement.

[Edward]

Hi

In that case you need to add another query and this query must be the Master in the Master-Detail relationship between these two datasources. So in the BeforePrintEvent of the Master you can change the variable MyVariable according with new 'Master' parameter value and the detail query will be executed with that correct master parameter.

Thank you.