using sub selects

[Bob Cergol]

Hello,

Faced with the need to add the total of one column from a small set of child records to a number in a single parent record, where that parent record is one row of output in a report what is my best approach?

Should I create a separate report that defines a key figure and then define relations that allow this report to be executed for each row retrieved by the "parent" report, where that parent report defines a formula field that adds the key figure to another field in the report?

Or is it possible, and better, to use SQL script in the underlying view or view dictionary. In the past I have gotten error messages from DAI warning that the use of select statements or sub-selects are not allowed in the context of whatever my report was trying to do. (I never understood what my error was so just worked around it.)

When I look at the SQL DAI generates for its reports I see lots of nested select statements -- so I am thinking I should be able to add SQL to my underlying view or define a global SQL var in its dictionary that would do something like:
Select sum(AA_Price) from Jcoraa where Jcoraa.job_no = Jcjobs.Idno and Jcoraa.Ok_to_Bill = 1

Any guidance would be appreciated.

Thanks,
Bob

[Carsten Sørensen]

If you more often need the total sum AA_price or it is very fast to calculate - then adding it as an additional column to the view will be the fastest and easiest to do. The reason is that you will have the column at the lowest level of a datasource available all over Dynamic AI.

If you need it on several reports but want to avoid that it is being calculated for other reports on the same data-source - then the dictionary approach using SQL Vars is the right approach.

Using a cross - report key-figure reference to the summation is ok - but will be executing the key-figure as a separate SQL statement together with all of the other SQL surrounding the report on which the key-figure is defined. This might in some scenarios be a little overkill. Also key-figure lookups will not imply any relations.

You might also just defined the formula directly on the reports that needs them.

The error you are receiving we need to investigate. Do you have the user right to add sub-selects (I guess you have)..

Best regards,
Carsten

[Bob Cergol]

Thanks Carsten, that was helpful.

I decided to make a column in the table itself to hold the total, though I think I will have future scenarios where I will want to try a sub-select in the view.

bob