I have a repeating text on graph report. The keyset fields are PointID, Depth, Reading, and Reading2. The graph shows up to 5 data sets of Reading2 for reach Reading for each Depth for each PointID. Beneath the graph I have created a table of specimen using text expressions. See below.
I'm working on selecting the Percent Retained on the #4 sieve and the Percent Passing #200 sieve for each sample right now. My expression for percent retained is the form of:
<<Format(<<Calc(100-<<Sql(SELECT [Percent_Finer] FROM [SV READINGS] WHERE [Reading]=4.75 AND [Depth]=<<Depth>> AND [PointID]='<<PointID>>')>>)>>,0)>>%
My expression for the percent passing is in the form of:
<<Format(<<Sql(SELECT [Percent_Finer] FROM [SV READINGS] WHERE [Reading]=0.075 AND [Depth]=<<Depth>> AND [PointID]='<<PointID>>')>>,0.0)>>%
Each expression only selects the data the first PointID with data and repeats it 5 times which makes sense. Is there a way to use the repeat variable with a SQL statement? Or do I need to have individual select statements and some fancy output expressions controlled by Data Items?
This is the current output:
Hi
I've used this approach for lab tests reports
<<Let(RL = _ <<Replace(_ <<SqlList(_ <<CR>>,_ SELECT '<<Tab!9xz>>' & [col1] & '<<Tab!27xz>>' & [col2] from [table] where ...._ )>>_ ,False,False,"xz",";C"_ )>>)>>__<<Iterate(_ Index,1,<<ListCount(<<Get(RL)>>,<<CR>>)>>_ ,1,<<CR>>,_ <<GetListItem(<<Get(RL)>>,<<CR>>,<<Get(Index)>>)>>_)>>
regards
Phil
Phil WadeDatgelBentley Channel Partner and Developer PartnerE: phil.wade@datgel.com | T: +61 2 8202 8600 & +65 6631 9780
Get the most out of gINT with Datgel Tools.
Thanks Phil. I couldn't get it to work. Ultimately, I just added two additional fields to my CBR table and manually input the percent passing for each sieve. While not ideal, it's more easily referenced into the report from there. Maybe I'll add a code to pull sieve readings data to those fields to streamline but it's not a big deal for two data entries.