Manual SQL Parameter to Subquery

aduna

Hello World

I have two database tables, one of them with our articles, and the other with parent-child combinations.
Now I try to show the whole article tree. We made two queries, in the main query we get the one parent article.
In the second query, we get recoursively all childs and child-childs and so on.

Now our problem:
In the first query, I get the article id, which I need in the second query, to get the right childs.
Normally I define the second query option "Linking SQL Generation" as "Parameterized SQL" and the article id form the parent query will be passed correctly to this subquery's where clause,
but because of the complex recursively SQL statement, the report builder cannot build the subquery's where clause correctly, or rather to the right position.
So I try now to use the subquery's "Linking SQL Generation" option "Manual SQL", so that I can handle the article id parameter by myself. So I defined a parameter and include this in our second query's where clause.
But the subquery does not care, when I change the parameters value. I set the parameter on the "AfterOpenDataPipelines" event, but this is too late, because the subquery is loaded also already.

I cannot find a function which will execute after the main query and before the subquery, to set my article id parameter, is there one such?...

Or the second solution could be, to reload the subquery in the event "AfterOpenDataPipelines", but how to?...

We use the ReportBuilder Enterprise Edition with Version 19.0 Build 76.

Can somebody help me?

nardmoseley

Hello Aduna/Entwicklung,

1. Please update your profile with your real name. You can also add a signature to your profile.

2. How many levels deep is the article tree?

3. Please provide details about the report layout, is it a master/detail (i.e. main report with a subreport in the detail)?

3. The main query has a single record or multiple records?

4. Please post the main and detail SQL here.

aduna

Hello Nard Moseley

1. My profile is up to date now.

2. The recursive level deepness cannot be defined exactly, every parent article can have different child structures with unequal levels. But the recoursive SQL statement works fine.

3. Exactly, I have the main query, with the derived subquery in the detail area of the report.
In the header section I let print the one parent article, the article id, the name and the description.
In the detail section I want to print the whole child tree, with the id and the name, for each one line.

3b. The main query selects just one article, the parent part.

4. the main query's statement:

SELECT ArticleID, Label, Description
FROM tArtikel 
WHERE ArtikelID = 194

the recoursive subquery's statement:

WITH APQ AS 
(
	SELECT 
		p1.ArticleID,
		p1.Label,
		NULL as ParentArticleID
	FROM tArtikel p1
	WHERE p1.ArticleID = :ParentID
	UNION ALL
	SELECT 
		p2.apFkPositionArticle,
		p2.apLabel,
		p2.apFkArticle
	FROM tArticlePosition p2
	INNER JOIN APQ q ON q.ArticleID = p2.apFkArticle
)
SELECT *
FROM APQ

If I use the subquery's option "Linking SQL Generation" as "Parameterized SQL", the report builder set the where clause parent article id correctly, but truncate all below the where clause. So I tried to use "Manual SQL" to set my own parameter "ParentID" after the main query opened. But I cannot find the right event to set my parameter, the event which will be expired after the main query is opened, and before the subquery is expired.

Thanks in advance.

nardmoseley

Hi Thomas,

Thanks for updating your profile

From the Design workspace, define a Report.Parameters[ ] item, called paArticleID. Set the DataType and initial value. Optionally set AutoSearchSettings.Enabled True.

For the main query use the Query Designer to bind the search condition to the parameter.

For the detail query, use manual SQL and reference the parameter as :paArticleID

RB will replace the parameter name with the value, prior to opening the datapipelines.

Here's a link to an rbWiki article with details on using report parameters:

http://rbwiki.digital-metaphors.com/end-user/fundamentals-end-user/report-parameter-fundamentals/

If you still find you want to use an event to do it manually, then try implementing the detail DataPipeline.OnMasterRecordPositionChange event to close the datapipeline, apply the parameter value, re-open the datapipeline.