SQL Variables: questions

[Bob Cergol]

1) Is a "global sql var" global to the connection or to the view; the name suggests to the connection, the navigation for creating them suggests it is known only to that view.

2) Do I need to be concerned about how many SQL variables I designate in table data dictionaries? I assume there's overhead. I remember reading somewhere that creating them as a plus column in a report is perhaps more efficient, but then I'd prefer to create these just once in one place.

3) Should I be able to reference other variables when defining new ones? (I carry a lot of cost subtotals in an order record that are used in several combinations to make other calculations. I'd like to reference some calculations in other calculations without actually repeating the entire formula.

Example:
Labor_Cost = lbr_act +lbr_spl +lbr_alt
DOC_Cost = mtl_act +mtl_spl +mtl_alt +pur_act +pur_spl +pur_alt +ppr_act +ppr_spl +ppr_alt
Job_Cost = labor_cost +DOC_Cost
Value Added = Price (itself a calculated var) - DOC_Cost

These items would be used frequently in many reports.

I'm getting an "invalid column name" SQL error when opening the report in the design tool that suggests to me that either I cannot reference these vars in forumlas for other vars -- or perhaps I'm not doing it
correctly.

Thanks,
Bob

[Carsten Sørensen]

Regarding 1)
I will double check and get back to you - but it should be possible to create connection wide global SQL vars. I.e. even though the interface suggest making the SQLVAR connection+data-source specific, I can remember that we allowed previously that you could remove the data-source dependency.

Regarding 2)
The overhead is minor. I don't think you will be able to see any performance or memory impact of either the dictionary or report level SQL vars. We have many reports defined in other installations with up to 800 available columns +/- SQL vars - without any real impact on performance.

Regarding 3)
In the current version you have limited reference functionality. The next release already have the reference functionality that you request. I believe the reason that you see an error is caused by that issue.

Best regards,
Carsten

[Carsten Sørensen]

Re.1) I double checked. We removed the option to create cross-datasource SQLVARS (i.e. defining SQLVARS on connection only - and not specifying a datasource). The reason was that it would only be useful for very few types of SQLVARs. I.e. a constant or a system date. Remember that the SQLVAR would be an available column on all data-sources for a specific connection - i.e. all underlying data-sources would in principle have to have a SQLVAR compatible structure to be valid.

Best regards,
Carsten

[Carsten Sørensen]

btw: To maintain or copy similar SQLVARS across multiple data-sources should apart from having to do it - be a minor task from the Administration using "list edit" ctrl C ctrl V copy paste functionality:

Navigate to the record that you want to copy multiple times using the keyboard arrow keys

step1.jpg (90.54 KiB) Viewed 8561 times

Hold down shift and paint the full record using right arrow - copy the record using Ctrl + C

step2.jpg (89.06 KiB) Viewed 8561 times

Navigate to the bottom of the list and move 1 down to add a new empty row

step3.jpg (93.92 KiB) Viewed 8561 times

Paste using Ctrl V

step4.jpg (99.81 KiB) Viewed 8561 times

move down + paste using Ctrl V again

step5.jpg (137.1 KiB) Viewed 8561 times

Move to the cells that you need to edit. Complete changes clicking UPDATE or Ctrl U.

step6.jpg (131.5 KiB) Viewed 8561 times

[Bob Cergol]

Thank you for the detailed replies.

Its interesting to see the copy/paste functionality ... as I assume it works the same on all DAI grids where write privileges are active.

Another trick you know that I don't is how to post 6 images in a single message! (I once tried and got a message saying three was the limit -- probably a good thing...)

Bob