Kinetic BAQ - How to link a field as a parameter for a referenced query

Kinetic ERP
,
1

I’d love to get this to work, but haven’t figured out how to properly do it.

I know I can send an empty value and retrieve all records, then apply a filter - but that doesn’t work too well if I’m referencing a complex CTE query. How do I then specify a field to pass as the parameter? I tried using the scalar parameter value but no success there.

Has anyone else tried to do this? Or more importantly, figured out how it’s done?

Also, I can do this in app studio if I’m presenting data on a dashboard. This is to drive data feeing into a BAQ report.

1 Like
2

@MIR I believe just had this as well. Are you trying to use this BAQ to feed a report or dashboard?

3

Report.

For dashboards, it’s easy enough to bring in a new dataview and map fields to the parameter value.

1 Like
4

And just so we are clear, the parameter is filtering something in your subquery right?

5

Correct. The original query has no parameters. It’s driving a BAQ Report displaying information about open jobs.

The referenced CTE query has a JobNum parameter which fetches information from the MLP tables about that job.

It’s possible to run the CTE query by itself with no parameter defined, but it’s very time consuming.

1 Like
6

Exactly, that’s the problem I have not been able to solve for either and @MIR was trying to do something very similar as well.

7

I was able to figure out on how to utilize Filters of a Report Style that’s based on RDD that runs on a simple BAQ.

image
image823×367 21.7 KB

My BAQ has parameters for Product Group, Customer and Part Number.
In the RDD they are linked to Criteria Set’s Filters as opposed to Fiscal Period which is a Prompt.

image
image991×379 36.1 KB

Back in my BAQ, those parameters are set in appropriate Table Criteria with the IN Operation.

image
image886×653 63.9 KB

1 Like
8

That’s an interesting approach which may work if I set it up as a subreport on the SSRS form. I’m not sure how else I may be able to accomplish it.

My top level query/report is being run with no parameters defined. Essentially, I need the job number(s) identified by that report to be passed as the parameter to my CTE query/subreport.

The new Kinetic BAQ builder allows you to reference other queries but doesn’t seem to accommodate the use of parameters. Perhaps I’ll punt on that approach and try my hand at making it work within SSRS.

9

It does allow for parameters, it’s in the overflow menu.

10

I don’t see parameters as an option for referenced subqueries though. Can you give a screencap of what you see? I may be missing it.

11

You define subqueries for the overall BAQ and then you can use them wherever.

12

image
image748×142 7.56 KB

13

My problem is that I don’t need a parameter for the top level query, but my referenced CTE query requries one. I’m trying to pass a field from the top query to the referenced CTE query as its parameter.

Here’s the only options I’m presented with:

image
image958×585 23.2 KB

1 Like
14

Isn’t that a join? Like you make the top level query then you make a CTE and join the top level query to whatever table you’re trying to use to relate that CTE table to right?

1 Like
15

I know this is late, but I wrote up some documentation from an Epicor webinar a couple of weeks ago on sending a parameter from dashboard to BAQ.

How To Add a Silent Parameter To A Kinetic Dashboard.docx (1018.9 KB)

Maybe it’ll help.

Joe

3 Likes
16

There is no way to link a field.

The way it executes - first the referenced query are executed, and put their result into temp-table (or similar).

Then the current query is executed using the data from that temporary storage.
So it is not possible to use the field value from the query that is not executed yet.