Hi,
I have an issue I'm sure many would have come across:
On a multiple plot (log template), I have several columns of data. I apply an output condition on each data record (like the soil unit check on depth). The condition is then checked for each column and it takes ages to produce the output. Would there exist a trick to allow doing the check only once (per table of course)?
If not, that would be great in a future version of gINT (like having dependant columns like for tables...)!
I hope it makes sense!
Thank you
Jerome
Hi Jerome
You can make User Report Variables for the these Output Condition calculations, and URV are calculated once per PointID (Keyset).
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.
Phil,
I've had a look at this. Yes we can do very interesting things with them. Unfortunately, I think it doesn't apply to my case because the variable I want is not only Point dependent but also Depth dependent...
It might be helpful if you shared what specifically you are trying to do. From the above I gather you want to have an output condition that considers depth. This can be easily handled using aggregate functions in the entire entity output condition field of the entity (item 1 in my original post). For example, the expression:
<<Calc(<<Min(<<SAMPLE.Depth>>,<<SAMPLE.Recovery_Length>> > 4)>> < 15)>>
Will suppress output of the entity for any point where the depth to the first recovery length that exceeds 4 is more than 15.
For example, if the sample table for point 1 has
Depth, Recovery_length
0, 1.5
4, 2.0
10, 0.5
12, 1.5
16, 5.0
18, 3.0
and for point 2 you have:
0, 1.0
10, 4.5
15, 3.0
The entity will not print for point 1 but will print for point 2 (ie the first depth (minimum) where recovery exceeds 4 is 16 for point 1 which is not less than 15 so false is returned. The first depth where recovery exceeds 4 for point 2 is 10 which is less than 15 so true is returned). You can get as complicated as you want to using aggregate functions or sql functions and it is only evaluated once per point.
If this is not what you are looking for, please provide specific details on what you are trying to accomplish.
Hi Szang,thanks for your help. Your item 1 is definitely not what I'm looking for as I want to keep some records of each entity after filtering.Let me describe more precisely what I want:
Let's say I have a log with 10 column-plots. Each of those show a different parameter from the same table of say 2000 records.I want to keep only records that satisfy a condition evaluated by sql aggregate from another table.
At the moment, the output condition check is done 10*2000*nb entities. I'd like to remove the 10 factor...Does it make sense?
Thanks again for spending time on my quest!
I think I understand what you are trying to do but to make sure I will present a specific example below. My example only involves 4 columns, limited data, and a fairly simple output criteria. Yours will obviously be a bit more complex.
I have a table called LAB_TEST. It has fields of Depth, LL ,PL , MC, and LAB_USCS. Data consists of results at 1 foot intervals from 1 to 9 feet as shown below.
I also have a table called LITHOLOGY. It has fields of Depth, Bottom, Description, and STRATA_ID
You want to print the lab test results in 4 adjacent columns using text vs depth entities but you only want to print results at depths where the strata_id is "COE-CL". In this case that will result in test values between the depths of 1.3 and 6 actually being printed. To do this you will need to write a body text output criteria in the entities using an sqlrange function. This will be checked once for each test result but it is the same check each time. In this case it will be checked 4 x 9 = 36 times for this 1 boring. It is your desire to eliminate the 4 in the the above equation and only perform the check 9 times for each boring.
If this does not represent what you are trying to do, you can ignore the rest of this post and respond with a SPECIFIC example.
The solution is to combine the multiple column entities into a single entity that prints all the required results in columns. This is done be setting up the column entity for the entire width of all results columns and then creating subcolumns within the entity for each individual result. An example of this is shown in the report design below.
The results for each column are generated using the Tab function as shown in the depth vs text entity main tab below:
Note that the text expression prints the results for all 4 columns in the proper position using the Tab function. I have done simple left alignment tabs but you can change the alignment as you see fit. You can also include checks on what to do if no data is found or add formatting to the results
The condition for output is in the configuration tab of the entity as shown below.
Note the Body Data Output Condition uses the SqlRange function to return the strata_ID from the lithology table associated with the current lab test depth and checks it against the literal "COE-CL"
The output result is shown below.
Note that only results for depths between 2 and 6 feet are printed even though data was available for depths between 1 and 9 feet. Since there is only one entity, the Sql function was only evaluated once for each line of data or a total of 9 times.
Obviously the above example will have to be adapted to your data structure and your situation but it hopefully gives you an idea of how to proceed.
Hi Szang,
thank you very much for this example. That's very interesting, I wasn't aware of the possibility to have subcolumns!
My issue is exactly like you describe, except that I want that for plots rather than text, so that makes it a lot more tricky!
Thanks again for taking the time for the reply
Please post a SPECIFIC example as requested. A specific example includes:
A sample of the data you are trying to represent. This includes relevant table and field names
A sample of the data that will be controlling whether the entity outputs or not. This includes relevant table and field names.
A picture of the desired output. If not available, a red lined example showing why it is not working or a scetch showing how it should look.
A screen capture of the entity that you have a problem with showing the current properties and settings. This includes both the main tab and the configuration tab as a minimum and any other relevant tabs. Make sure to expand any fields that have long expressions or copy and paste the long expressions into a code object in the post.
Any other relevant data or settings
This saves me and others from having to continually guess at what you are trying to accomplish.
To be clear, I see at least 3 potential solutions to this issue. There are likely more. Before I develope and test another solution, I need to know what exact constraints are imposed by the data and desired output. Replicating the current problem also gives me a baseline to test the solutions against.
Lacking a specific example to test possible solutions against, I developed my own specific example. There may be conditions and criteria in your situation that invalidate some of these solutions or I may have solved a question that you did not have.
This will be a very long post to completely describe and evaluate as many options as I can think of (there are probably more than I can think of). If you simply want what I believe to be the best solution, skip ahead to Method 5. It offers a 99% reduction in report generation time over an inefficient conventional solution and a 94% reduction in report generation time over an efficient conventional solution. However this solution may have problems if you intend to export vector files of the log to CAD.
Set up
I have a table called CPT with PointID, Depth key set. It contains the following fields:
Depth, Reading1, Reading2, Reading3, Reading4, Reading5, Reading 6, Reading7, Reading8, Reading9, Reading10, and PrintFlag. All fields are double precision numbers except PrintFlag which is Boolean.
I populated the table with depths at 0.01’ increments between zero and 30 feet which creates 3000 records for one borehole. All reading fields were populated using a random number generator to generate numbers between 0 and 1. The PrintFlag field was set to true for depths between 6 and 10 feet and false for all other records. An example of the table is provided in the picture below.
I also have a table called Lithology with PointID, Depth keyset. It has fields for Depth, Bottom, Description and StrataID among others. A sample of the data for the example boring is provided in the picture below.
I created a log template that has 10 plot vs depth entities side by side. Each entity is set to plot one of the reading fields in the CPT table. The report set up is shown below. The plot entities are labeled P1 through P10 in the body header.
It is desired to restrict output of all 10 plots to only those depths where the StrataID in the Lithology table is equal to COE-CL, COE-CH, 13, 14, 15, OR 16. In the example this occurs between depths of 5.4 and 8 feet. I tried 5 different methods of restricting the output of the plot vs depth entities to the desired range as described below. I then timed how long it took to generate a preview of the report for each method.
Method 1 – Baseline
This is a conventional solution. The expression below was placed in the Body data output condition field of each of the 10 plot entities.
<<Calc(_ (<<SqlRange(<<CPT.Depth>>,[LITHOLOGY].[Strata_ID])>> = "COE-CL") _ OR _ (<<SqlRange(<<CPT.Depth>>,[LITHOLOGY].[Strata_ID])>> = "COE-CH") _ OR _ (<<SqlRange(<<CPT.Depth>>,[LITHOLOGY].[Strata_ID])>> = "13") _ OR _ (<<SqlRange(<<CPT.Depth>>,[LITHOLOGY].[Strata_ID])>> = "14") _ OR _ (<<SqlRange(<<CPT.Depth>>,[LITHOLOGY].[Strata_ID])>> = "15") _ OR _ (<<SqlRange(<<CPT.Depth>>,[LITHOLOGY].[Strata_ID])>> = "16")_ )>>
For this case, the expression will need to be evaluated 30,000 times during output of a single boring (10 entities x 3000 records). In addition, the sql function must query the database 6 times for each evaluation. This method took 7:45 to generate (yes that is 7 minutes and 45 seconds) and represents what I am calling the baseline situation. Note that this method is extremely inefficient as it queries the lithology table 6 times for each point but the result of the query should always be the same. An example of the output generated is presented in the picture below.
Method 2 – More efficient output condition using Let and Get
As stated above the output condition is extremely inefficient because it runs the SQL query on the lithology table 6 times but gets the same answer each time. Multiplied by 30000 points this inefficiency adds up. Since the query gets the same answer each time we can use let and get functions to only run the SQL query once per data point. This is still a conventional solution using the body data output condition property to control output of the data. For this case, the body data output condition in all 10 plots was replaced with the expression below
<<Let(CurrentStrataID = <<SqlRange(<<CPT.Depth>>,[LITHOLOGY].[Strata_ID])>>)>>_ <<Calc(_ (<<Get(CurrentStrataID)>> = "COE-CL") _ OR _ (<<Get(CurrentStrataID)>> = "COE-CH") _ OR _ (<<Get(CurrentStrataID)>> = "13") _ OR _ (<<Get(CurrentStrataID)>> = "14") _ OR _ (<<Get(CurrentStrataID)>> = "15") _ OR _ (<<Get(CurrentStrataID)>> = "16")_ )>>
This method produced the desired output and took 1:24 (1 minute and 24 seconds) to generate a preview. This is a substantial improvement over the baseline condition as expected but still a significantly long time.
Method 3 – Report Variable Expressions
This method was suggested by Phil Wade above and involves setting up report variable expressions to determine the limits where output is desired. Report variables are only evaluated once per boring thus potentially reducing the workload when generating the report. For this case I set up 2 report variables as follows:
Report Var.Top_print = <<Sql(Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[PointID] = <<CurrSetKey>> AND ([LITHOLOGY].[Strata_ID] = "COE-CL" OR [LITHOLOGY].[Strata_ID] = "COE-CH" OR [LITHOLOGY].[Strata_ID] = "13" OR [LITHOLOGY].[Strata_ID] = "14" OR [LITHOLOGY].[Strata_ID] = "15" OR [LITHOLOGY].[Strata_ID] = "16"))>>
Report Var.Bottom_print = <<Sql(Select max([LITHOLOGY].[Bottom]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[PointID] = <<CurrSetKey>> AND ([LITHOLOGY].[Strata_ID] = "COE-CL" OR [LITHOLOGY].[Strata_ID] = "COE-CH" OR [LITHOLOGY].[Strata_ID] = "13" OR [LITHOLOGY].[Strata_ID] = "14" OR [LITHOLOGY].[Strata_ID] = "15" OR [LITHOLOGY].[Strata_ID] = "16"))>>
Note that the report variable type had to be set to “memo” in order to accommodate the long SQL function. I have expanded the expression for the Top_print variable (shown above) in the code window below to facilitate understanding. I did not do this in gINT because I was unsure if the print variable expression memo field supported standard gINT continuation marks and indenting (and I did not try it). Note that the expression below is not a valid expression as written because it has no continuation marks. The expression for Bottom_print is similar.
<<Sql( Select min([LITHOLOGY].[Depth]) FROM [LITHOLOGY] WHERE [LITHOLOGY].[PointID] = <<CurrSetKey>> AND ( [LITHOLOGY].[Strata_ID] = "COE-CL" OR [LITHOLOGY].[Strata_ID] = "COE-CH" OR [LITHOLOGY].[Strata_ID] = "13" OR [LITHOLOGY].[Strata_ID] = "14" OR [LITHOLOGY].[Strata_ID] = "15" OR [LITHOLOGY].[Strata_ID] = "16" ) )>>
I then placed the following expression in the Body data output condition of each plot vs depth entity:
<<Calc((<<CPT.Depth>> >= <<Report Var.Top_print>>) AND (<<CPT.Depth>> <= <<Report Var.Bottom_print>>))>>
Note this eliminates repeated evaluation of the SqlRange query but it still requires that the logical expression be evaluated 30,000 times to determine if the record should be output. Testing this solution resulted in a preview generation time of 49 seconds. This is an improvement over the conventional let-get method but suggests that, in this particular case, the sqlrange function is not taking a lot of processing time. Perhaps if the sql query was more complex or was being evaluated against a very large data table this method might offer more benefits. Also note that as currently set up, this method can only handle one range of output. If there are two zones where the desired output criteria is satisfied, the report variable expressions that I used will only pick up the top of the first and the bottom of the second as one big zone. Thus, it may not be suitable if your criteria will generate more than one zone of output.
Method 4 – PrintFlag set by gINT rule
This method was also suggested above. It involves using a gINT rule to write a field in the CPT data table that specifies which records should be printed. The gINT rule would pre-calculate the output criteria prior to report generation thus reducing the processing requirements at output time. For this example, I did not write the gINT rule to populate the PrintFlag field but it should not be too difficult to write. Rather, I manually populated the field with values that would result in an output range similar to what the desired criteria would impose. Note that a gINT rule will require a certain amount of processing time if set to run each time the relevant tables are modified and that is not included in this evaluation. The Body data output condition for each plot vs depth entity was set to simply
<<CPT.PrintFlag>>
Since this is a Boolean field it requires no evaluation but the program still has to retrieve the field from the record and look at it 30,000 times. The report generation time for this method was 7 seconds which is a huge improvement over the baseline conventional method (7 minutes 45 seconds).
Method 5 – Old timers block out hack
This is an unconventional method and involves allowing the plot vs depth entities to output without restriction. A separate graphic column entity is used to block out output that is not desired. This is set up as follows:
Each of the plot vs depth entities has the plot order set to -2 and the body data output condition is empty. A graphic column entity is added to the report with an x coordinate equal to the x value of the left most plot vs depth entity. The width of the graphic column is set to cover all 10 plot vs depth entities and the plot order is set to -1 so it plots on top of the plot vs depth entities. The graphic symbol is set to solid and color to white. Top and bottom depths are set to <<LITHOLOGY.depth>> and <<LITHOLOGY.Bottom>> respectively. The output condition of the graphic column is set to:
<<Calc(_ NOT(_ (<<LITHOLOGY.Strata_ID>> = "COE-CL") _ OR _ (<<LITHOLOGY.Strata_ID>> = "COE-CH") _ OR _ (<<LITHOLOGY.Strata_ID>> = "13") _ OR _ (<<LITHOLOGY.Strata_ID>> = "14") _ OR _ (<<LITHOLOGY.Strata_ID>> = "15") _ OR _ (<<LITHOLOGY.Strata_ID>> = "16")_ )_ )>>
So that it outputs only for depths that DO NOT satisfy the desired output criteria. The report design set up is shown in the picture below.
The output is shown in the picture below. For this example output, I have set the color of the solid graphic element to light yellow (rather than white) to show how it blocks out the undesired output. Note that it blocks out the column edge lines generated by the plot vs depth entity in addition to blocking out the output plot. If it is desired to have the column lines extend through the blocked out zone you can add fixed vertical lines (or vertical line to depth entities) to the report with a print order greater than -1 so that it plots on top of the blockout rectangles.
The report generation time for this method is 5 seconds. This represents a 99% reduction over the baseline conventional condition and a 94% reduction over the more efficient conventional solution. This is as expected since 30,000 body output conditions do not have to be evaluated. A very little time is required to generate the blockout rectangles but they block out all 10 plots at once.
I do not recommend this solution if you intend to output the log to a CAD vector file. I have had problems in the past (many many moons ago) with how CAD handles solid objects and print orders. Perhaps these issues have been resolved in the current versions but I did not test this. Further it is very easy to mistakenly delete or move the blocking rectangles in CAD. Thus, if you want to use this method with CAD, I recommend exporting the log to a raster file such as .png or .jpg before importing into CAD.
Limitations
Every effort was made to make the compared solutions equal in every way except the method by which the plot output was restricted. Timing report preview generation time may not be reflective of other methods of output or other data. I only tested these solutions on one specific example boring. Other borings and other data might result in special conditions (such as no matching criteria found) that need to be incorporated into the solutions and tested. I did not try to generate multiple borings at once.
I realize that the example does not make geologic sense (why would you do that?). It was a convenient situation that allowed me to use an existing test data base and library for development of potential solutions and is completely fictional.
Answer Verified By: jerome garnon