“Saved Data” Select Expert/Formula

Monday 19 May 2014 @ 8:52 am

In the current versions of CR, you have 3 choices for the select expert and the selection formula.  The main one is Record Selection which is the one you use for database fields and most formula fields.  Then there is Group Selection which is used for subtotals and other summary fields.  But the third an most recent addition is the one for “Saved Data”.  I have ignored this feature since it was introduced, mainly because the explanations I read didn’t make any sense to me. The idea is that you can put rules here when you only want the rule to apply to saved data. But if you have saved data you can put the same rule in the regular record selection formula and just tell the report to use saved data, so it seemed to simply confuse things. It might make sense for those using the SAP viewer where you are limited to saved data. This would allow you to deploy parameters that don’t ask the user about refreshing the data.  But since my customers typically use viewers that CAN refresh data this doesn’t come up.

But after a customer asked me about this feature I gave it some more thought.  I realized that a better way of saying this is that it is a rule that is applied locally, meaning that it doesn’t get added to the WHERE clause of the SQL query. No one has said it that way but I just tested it and that is exactly what happens. This opens up several useful avenues.

1) I have seen cases where the selection criteria creates a WHERE clause that is misinterpreted by the database. It happens when the fields in the selection formula do not match the indexing scheme in the database. (This only happens in a few less commonly used databases). In those cases I would normally have to write a formula to use for selection, which can prevent the report from passing that rule to the SQL. Now I see that I could move those rules to the “Saved Data” formula and accomplish the same thing.

2) Another potential use is when the report I create is run from within an application. Some applications generate their own selection formula based on choices that the user makes in the application screens. I sometimes find that the rules I add to the selection formula are overwritten by the application’s selection formula. This provides another workaround for that problem.

3) And this provides a way to make group level subreports be group specific, without having to hit the database for every group.  See this article for more on this use.

The only downside is that because the “Saved Data” selection formula is rarely used, any rules put there might be forgotten, causing some extra troubleshooting.  So that means one more place to check when a report isn’t returning the records expected.









Leave a Reply

Jeff-Net
Recrystallize Pro

Crystal Reports Server