Creating multiple Pie charts in Reporting from the a single named query

Ignition
1

Hi all,
I am trying to setup three pie charts which receives the data from the same named query but the only difference is with the parameter set up for filtering the data in the named query . For Instance, I have a named query called 'DefectsSummary' and the parameters set up within the named query are as follows : canisterNo, enamellerNo, i want to set up one pie chart with canisterNo : 1 and another with canisterNo : 2 , I have already set up a parameter in the report parameter named CanisterNo which is bound to the parameter of the named query. What I want to achieve is to render with the named query parameters for each chart by using only one named query as a datasource.

2

In Reporting, you can define multiple Data Sources. Make 2 or more Data Sources of type "Named Query" and be sure to specify a unique "Data Key" name and enter the parameters you need for each one. Then configure each pie chart to point to the correct data key.

Multiple datasources, same named query.

image
image1472×197 21.7 KB

image
image287×282 11.1 KB

1 Like
3

is it possible to run one data source for all the charts?

4

Yes, it is possible. Use a script datasource instead and call the named queries there. Assign each response to the queries to a unique data key as described before.

2 Likes
5

In addition to the option of using named queries in a script I recommend you review the nested queries page as you could likely run those queries in a nested query:

Scripting:

1 Like
6

my named query is like this :

WITH ResultCounts AS (
    SELECT result + ' - ' + defect_area as combined_result, COUNT(*) as result_count
    FROM IGN_EDGE_API_NQ_ENAMEL_INSPECTION_RESULTS 
    WHERE t_stamp BETWEEN :startDate AND :endDate
    AND (
        (ISNULL(:enamellerNo, 0) != 0 AND enameller = :enamellerNo)
        OR (ISNULL(:enamellerNo, 0) = 0)
    )
    AND (
        (ISNULL(:cannisterNo, 0) != 0 AND canister = :cannisterNo)
        OR (ISNULL(:cannisterNo, 0) = 0)
    )
    AND (
        (ISNULL(:partNoFilterList, '') != '' AND part_number IN 
            (SELECT TRIM(UPPER(value)) FROM STRING_SPLIT(:partNoFilterList, ',')))
        OR (ISNULL(:partNoFilterList, '') = '')
    )
    GROUP BY result + ' - ' + defect_area
),
RankedResults AS (
    SELECT 
        hi.t_stamp as inspection_t_stamp,
        hi.result + ' - ' + hi.defect_area as combined_result,
        rc.result_count as result_count
    FROM IGN_EDGE_API_NQ_ENAMEL_INSPECTION_RESULTS hi
    LEFT JOIN part_data pd ON hi.part_number = pd.part_number
    LEFT JOIN IGNITION_DATA.dbo.INSPECTION_DEFECT_TYPE_GROUPS dfct_grp ON dfct_grp.defect_type = hi.result 
    LEFT JOIN ResultCounts rc ON hi.result + ' - ' + hi.defect_area = rc.combined_result
    WHERE hi.t_stamp BETWEEN :startDate AND :endDate
    AND (
        (ISNULL(:enamellerNo, 0) != 0 AND hi.enameller = :enamellerNo)
        OR (ISNULL(:enamellerNo, 0) = 0)
    )
    AND (
        (ISNULL(:cannisterNo, 0) != 0 AND hi.canister = :cannisterNo)
        OR (ISNULL(:cannisterNo, 0) = 0)
    )
    AND (
        (ISNULL(:partNoFilterList, '') != '' AND hi.part_number IN 
            (SELECT TRIM(UPPER(value)) FROM STRING_SPLIT(:partNoFilterList, ',')))
        OR (ISNULL(:partNoFilterList, '') = '')
    )
    AND (
        (ISNULL(:defectsOnly, 0) = 1 AND hi.result != 'PASS')
        OR (ISNULL(:defectsOnly, 0) = 0)
    )
    AND (
        (ISNULL(:defectFilterList, '') != '' AND hi.result IN 
            (SELECT TRIM(value) FROM STRING_SPLIT(TRIM(UPPER(:defectFilterList)), ',')))
        OR (ISNULL(:defectFilterList, '') = '')
    )
),
GroupedResults AS (
    SELECT
        inspection_t_stamp,
        CASE 
            WHEN combined_result LIKE '%-H%' THEN 'OTHER'
            ELSE combined_result
        END AS result,
        result_count,
        ROW_NUMBER() OVER (
            PARTITION BY 
                CASE 
                    WHEN combined_result LIKE '%-H%' THEN 'OTHER'
                    ELSE combined_result
                END
            ORDER BY inspection_t_stamp DESC
        ) AS group_rank
    FROM RankedResults
)
SELECT 
    result,
    SUM(result_count) AS total_count
FROM GroupedResults
WHERE group_rank = 1
GROUP BY result
ORDER BY SUM(result_count) DESC

where what i want to do is to control the value of the parameters 'enamellerNo' and 'cannisterNo' for each plot. Is there a way in nested query to set the value of the params for each instance and bind that data key to the respective graph.

7

With a Table Group and nesting within a query that starts with selecting all :cannisterNo and :enamellerNo options, you can likely achieve something like that yes. For a query like that, I would recommend you first practice with Table Grouping/Nesting using this example:

Use the dataset the User Manual mentions from the Nested Queries example, and then you can setup the simple Table Group example. To start mapping it to your case substitute the EquipmentID with your IDs.

2 Likes