Text Tables: Statistics For a Non Key Field

Within the LITHOLOGY table, there is a field .Stratum where each row in LITHOLOGY can be assigned a .Stratum integer 1-9. Suppose they are not necessarily in depth order (interlayered) and several LITHOLOGY rows within a POINT might be assigned the same stratum number.

I want to create a text table that contains rows .Stratum 1 through 9 and then in columns evaluations of the project data by stratum (rather than by boring). Column headers such as "Stratum ID", "min. depth stratum was encountered", "list of graphics used in this stratum", etc. However, .Stratum is not a set key by which I can dictate the table. How should I make the data expressions so that the table filters and outputs the way I want?

ie. For key set PointID, Header Text "Stratum ID", I want to output a row for each stratum encountered in the project.

Thanks,

  • I thought about that and provided instructions for the AND that you would have to add to the WHERE clause above. problem I saw was assembling the IN from a user report variable. As far as I know the pick list in a user report variable only allows picking one item so you would have to have a whole bunch of user report variables, assemble them into a properly formatted IN clause. Too hard jar for me. Else you would have to have the user manually type a properly formatted IN clause into a single user report variable. Too hard jar for the user. There may be a way, just didn't explore it enough. 

    Thought briefly about using a fence report that allows selecting multiple borings for a single sheet and exporting as csv text similar to above . Didn't explore but could eliminate some post processing.

  • Also,, I would verify that the method you showed above using iterate with a <<cr>> exports to excel as expected. As far as I can see that creates the illusion of multiple rows but us actually one row with 9 entries separated into 9 lines. It may import into excel as 9 items in a single cell. 

  • Have not tested this yet and likely won't be able to for a couple days.

    <<Iterate(_
      SID,1,9,,<<cr>>,<<Sql(_
        Select [LITHOLOGY].[Stratum] From [LITHOLOGY] Where [LITHOLOGY].[Stratum] = <<Get(SID)>> AND
        <<IIf(_
          <<Report Var.SelBors>> = "",True,<<Like(_
            <<Report Var.SelBors>>,"*,*",<<Like(_
              <<Report Var.SelBors>>,"*"[LITHOLOGY].[PointID]"*")>>,<<Like(_
                [LITHOLOGY].[PointID],<<Report Var.SelBors>>"*"
              )>>
            )>>
          )>>
        )>>

      )>>

    This uses the user report variable SelBors with these instructions

    "Leave blank = all borings in project, enter part of boring name (ie "B-" = all borings that start with that, enter comma delimited ", " list of boring names = only those borings."

  • Good point about the <<CR>> You are right, in fact when exporting to .XLSX it makes one row with 9 lines instead of  9 rows with one line each. I'm not sure how to get past this. Maybe use a comma delimiter and export to CSV?

  • Not sure I fully understand your expression but get the gist of what you are trying to do (probably because I have limited expertise in sql). If it works, it would not give the same functionality as a multi pick boring list. Further you still have to solve the 9 items in one cell problem. Using commas instead if <<cr>> would likely just place 9 comma separated items in one cell.

    I have a few ideas that I will try working on over the next several days. Let you know if anything pops up.