# Report - Nested Named Queries Based on Stored Procedure

**URL:** <https://forum.inductiveautomation.com/t/report-nested-named-queries-based-on-stored-procedure/34975>\
**Category:** Ignition\
**Tags:** ignition80\
**Created:** [May 22, 2020, 3:12pm UTC](https://forum.inductiveautomation.com/t/report-nested-named-queries-based-on-stored-procedure/34975 "2020-05-22T15:12:46Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![JHortenberry](https://avatars.discourse-cdn.com/v4/letter/j/b3f665/32.png) [@JHortenberry](https://forum.inductiveautomation.com/u/JHortenberry)\
**Post date:** [May 22, 2020, 3:12pm UTC](https://forum.inductiveautomation.com/t/report-nested-named-queries-based-on-stored-procedure/34975/1 "2020-05-22T15:12:46Z")

</div>

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:

1. Will Nested Report Queries work with Named Queries (based on stored procedures)?
2. Why are the Parent Query values not seen by the Child Query?
3. Is there a limit on Nested Query levels? (…documentation indicates no limit (ref. video / IU).)
4. 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…