Storing values from database in saved variable/array/etc for later use.
We're looking for ideas on how to store/save values for use later in a Crystal program.
We have a database de-code table that translates codes to text. Joining this table in the normal sql queries/sub-queries causes them to run slower than weu2019d like. So developers often end up putting the de-code translation into a formula. Which makes the program run fast, but requires a change whenever the de-code database has a change, so itu2019s a maintenance issue. (same if in a Repository).
Weu2019d like to be able to simply run a query separately to the de-code table and store the returned values u2018somewhereu2019, for use later in the crystal program, from both the main report and sub-reports. The values returned from the de-code table may range from 20 rows to 500 rows, so itu2019s not a lot of data. This way, our programs would always have the most current data available from the de-code table.
Can a Crystal program run a separate query one-time (via sub-report/etc), store the values somewhere (array/globals/etc) so that query doesnu2019t get executed again, then those values would be available to the rest of the program, either from main or sub-reports? Iu2019m not a developer, am the Enterprise Admin working with developers to find a better solution, and they arenu2019t sure how to go about this. We are at level CRXIR2/BOEXIR2.
Graham Cunningham replied
As long as the shared variables are referenced in a section below the subreport that assigns them values in the main report they can then be referenced in other subreports providing they are in sections below where they are referenced in the main report. In short Crystal processes top down so the subreport has to process to assign values to a shared variable. If the subreport has not processed yet then they will have no value.
As well arrays are limited to 1000 elements so you can't build them infinitly.
Subreports will execute whenever the main report is refreshed. You could however use an on demand subreport. This would only run when selected in the main report.