Home Datapipelines
New Blog Posts: Merging Reports - Part 1 and Part 2

Manual SQL Parameter to Subquery

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?

Comments

  • Hello Aduna/Entwicklung,

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

    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.











    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
  • 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.
  • Hi Thomas,

    Thanks for updating your profile :smile:

    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.


    Best regards,

    Nard Moseley
    Digital Metaphors
    www.digital-metaphors.com
Sign In or Register to comment.