I feel like the Calculated Field with a static variable is easy enough and I’ve used that for things that I am controlling. But I thought the question was more about getting a value from a table, storing that value as a variable, and then using it multiple places in a BAQ so that you didn’t have to always join that table/create a subquery. His original question referenced getting some setting out of Company Config. Of course you could hard code those values into a calculated field, but that wasn’t the original intent. @JasonMcD correct me if I’m wrong.
Both.
1 Like
If you do the initial “get” of the value as a CTE, then you are basically getting the value once. It stores it in memory.
1 Like
That’s correct. It’s actually what’s called a cross join I believe, and while usually a mistake, can sometimes be useful.
Yeah I have used CTE’s before but I feel like it never actually speeds anything up. No doubt due to my own ineptitude.
So to combine the thoughts here for clarity, the consensus seems to be that:
If I want to grab a DB value and store it for future use in the BAQ
- Make a subquery that is a CTE
- Make a calculated field to store it – or I guess it could be just the actual field itself
- Ensure it returns just one row (many ways to accomplish that)
- Can bring in that subquery into other subqueries and no need to even join to it
- And then use the “variable” in expressions as needed
So you all feel this is roughly as efficient as a true variable?
I mean, if it saves you a bunch of work… it’s helpful. You can do some tests if you want, but with no way of actually using a variable, it’s kind of hard to know for sure.
2 Likes
That is a sufficiently cromulent answer.
Learn something new everyday. lol
I stole that from someone else on the forum recently, but I certainly used to be a Simpsons fan when it was cool.
1 Like
Yes I was on the phone lol.
Well you could create a stored procedure, and use that.
(Even in the cloud, but I’m not going to advertise how to do that 
)
Doesn’t have to be a CTE, it will likely be converted anyway
Correct
Correct
If you refer to it as “{MySubQueryName}”, you can access it without pulling it in at all/
It just must exist.
Yep
Jason,
I will be demonstrating how I store variables on a UD table for every user that can be called by any BAQ
Plus when I do a executive dashboard with multiple BAQs I can feed all of them in on step
Check out the Midwest Users Group meeting on 16th of February
1 Like