SQL statements and repeating text

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:

Parents
  • 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 Wade
    Datgel
    Bentley Channel Partner and Developer Partner
    E: phil.wade@datgel.com | T: +61 2 8202 8600 & +65 6631 9780

    Get the most out of gINT with Datgel Tools.

Reply
  • 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 Wade
    Datgel
    Bentley Channel Partner and Developer Partner
    E: phil.wade@datgel.com | T: +61 2 8202 8600 & +65 6631 9780

    Get the most out of gINT with Datgel Tools.

Children