REF: v8.0.7
Currently, there are three (3) Named Queries.
Report ID
Report Model
Report Value
Each of the Named Queries are based on a corresponding MSSQL Stored Procedure.
Report ID:
Parameters:
Start Date
End Date
Return:
ID:
4
5
Report Model:
Parameters:
Start Date
End Date
ID
Return:
“A”
“B”
“C”
Report Value:
Parameters:
Start Date
End Date
ID
Model
Return:
1, 1.23 (Row_Count = 1, Value Corresponding to Start / End / 4 / “A”)
2, 2.34 (Row_Count = 2, Value Corresponding to Start / End / 4 / “B”)
3, 3.45 (Row_Count = 3, Value Corresponding to Start / End / 4 / “C”)
4, 4.56 (Row_Count = 4, Value Corresponding to Start / End / 5 / “A”)
… …
x, x.yz Etc…
NOTE: Named Query Definition (Generic)
EXEC
: p1
, p2
…
, px …as required
NOTE: All named queries have been tested and properly return the data expected.
The report nested query data configuration is as follows:
Report ID
Report Model
Report Value
Parameters:
StartDate: dateArithmetic(now(), "-1", "week")
EndDate: now()
The expected parsing of the report queries would be Report ID, Report Model, then Report Value.
Report ID requires only StartDate / EndDate…
Where:
These values should be referenced from the report parameters.
Report Model requires StartDate / EndDate / ID…
Where:
The Start / End should be referenced from the report parameters.
The ID’s should reference the returned values from Report ID (e.g. 4 and 5).
Report Value requires StartDate / EndDate / ID / Model
Where:
The Start / End should be referenced from the report parameters.
The ID’s should reference the returned values from Report ID (e.g. 4 and 5).
The Model should reference the returned valued from Report Model (e.g. “A”, “B” and “C”).
The GOAL:
Report with the following page layout:
Page x:
StartDate - EndDate - ID - Model
<XYCharted Row Count / Value Data>
Extrapolating with data from above:
Page 1:
5/10/2020 - 5/20/2020 - 4 - "A"
<XYChart 1 / 1.23>
<XYChart 1 / 2.34>
<XYChart 1 / 3.45>
...
<XYChart x / x.yz>
Page 2:
5/10/2020 - 5/20/2020 - 4 - "B"
<XYChart 1 / 11.23>
<XYChart 1 / 12.34>
<XYChart 1 / 13.45>
...
<XYChart x / x.yz>
Page 3:
5/10/2020 - 5/20/2020 - 4 - "C"
<XYChart 1 / 21.23>
<XYChart 1 / 22.34>
<XYChart 1 / 23.45>
...
<XYChart x / x.yz>
Page 4:
5/10/2020 - 5/20/2020 - 5 - "A"
<XYChart 1 / 31.23>
<XYChart 1 / 32.34>
<XYChart 1 / 33.45>
...
<XYChart x / x.yz>
Etc...
No Data is displayed.
No Errors are presented.
Data is present and valid when the Named Queries are tested individually.
Questions:
- Will Nested Report Queries work with Named Queries (based on stored procedures)?
- Why are the Parent Query values not seen by the Child Query?
- Is there a limit on Nested Query levels? (…documentation indicates no limit (ref. video / IU).)
- Is there a way to test the Nested Query (from the Report Designer), outside of the Report Designer?
FYI - Database connections are valid / working and Data is available in the range requested.
Thanks for taking a look…