Demystifying Grids (August 18, 2022)

Instructions for using the process and reports together

1. Import process.
2. Run at least one request.
3. Import standard report into your system.
4. Copy SQL code from the standard report that you imported into your system into a new custom report.

Note: Comment out the lines for the date limits in the where clause or the report won't run

5. Add the block of code below in the same area as the highlighted gray code in the screenshot below. 

cross apply    OPENJSON (gridvalues, N'$.gridCellValues')  
            WITH (  
              --field labels (column header names) with spaces get wrapped in double quotes
              Item varchar(100) N'$."Item #"', -- Select List
              Con_Line varchar(100) N'$."Con Line #"',        
              Description varchar(100) N'$.Description',
              Unit varchar(100) N'$.Unit',
              Qty varchar(100) N'$.Qty',
              Price varchar(100) N'$."Unit Price"',
              Total varchar(100) N'$."Total"'              
           )

5. Save the new query.
6. Click on Generate Columns.
7. Preview the report.

Click to download the content used in this webinar