When setting up the parameters for a report, it's possible to specify a default value for the first time a user runs that report, but there's no obvious way to determine a default dynamically. Has anybody found a way to do this? It's easy if you use the native SSRS parameter features, but I would like to be able to do it with the Tessitura report setup tools.
Here are a few examples:
1. I'm currently working on a utility for adding acknowledgment letters to contributions without having to do an adjustment, and one of the necessary steps is to choose a value from TR_ACK_TRN_TYPE. I have a way of figuring out which of those values is most likely to be correct, based on analyzing the transaction data, so I could just have that all happen behind the scenes, but I would prefer to make it a parameter in case we have situations where some other value is more appropriate. Ideally I would like the parameter to automatically select the most likely value so that the user can see what it is and decide if it needs to be changed. The way the system currently works, though, it will just remember whatever the value was the last time the report was run, which will actually be wrong most of the time.
2. I have another utility for adding, removing, and updating Creditees. Ideally, the way it would work is that if you're editing an existing creditee you select the contribution and the creditee and then the other parameters all populate with the existing values. Then you can change the ones you need to change and leave the ones you don't need to change.
3. Suppose you have a report that you usually want to run with a date range of the past 2 weeks, but you also need to be able to run it for an arbitrary period. With a scheduled report, you can specify a dynamic range based on the run-date, but for an on-demand report you can't.
All of these things are easy to do if you're using the native SSRS parameter handling, but at my organization our standard is to always use the Tessitura parameter handling.
On a related note, even if it's not possible to set defaults dynamically, it would be nice to be able to prevent the value from last time from loading on a parameter by parameter basis. Most of the time, having a report remember what you did last time is really useful, but for certain situations where you're very unlikely to ever use the same value twice in a row it just introduces an easy error: you forget to update that value and the report runs but you get the wrong data.
Nick and Travis: I'm currently doing things along the lines that you describe, at least some of the time. Usually I will make the parameter optional and then have the report use the parameter value if it's present, or calculate a value if the parameter is null. The downside of this approach is that the user can't see what will happen if they leave the parameter it blank, so in some cases it's really more confusing than helpful to do that. In other cases it works great.
I came up with a solution that works in some situations, in case anybody is still interested. (Actually, I solved it a while ago but just realized I never posted...)
My specific scenario is that I want to select a contribution record and assign a new acknowledgment letter to it, and I need to be able to set the transaction type (from TR_ACK_TRN_TYPE) that will be used in T_ACK_EXT. I have code in my procedure to figure out the most likely answer, but I need to be able to override it for special cases. At the same time, usually I will want to use the result that my procedure calculates, and having the parameter populated with the value from the last time it was run creates risk that the wrong value will be used by mistake. Ideally, the parameter itself would automatically be set to a calculated default, but that's not possible, so instead I want it to be defaulted to Null and you only set a value there if you want to override the calculated value. Here is what I did:
Parameter 1: Mode (Required)Run in preview or execute mode
Parameter 2: customer_no (Required)An empty field that you type into
Parameter 3: contribution ref_no (Required)A dropdown, with a Where clause that makes it only return contributions from the selected customer
Parameter 4: Transaction transaction_no (Required)A dropdown, with a Where clause that makes it only return transactions from the selected contribution
Parameter 5: Override Transaction Ack Type (Optional)A dropdown, which uses TR_ACK_TRN_TYPE. It has this Disable clause: "<<p4>> <1"
Parameter 6: Letter (Required)A dropdown, which uses T_FORMAT_INFO
What happens here is that when a new Contribution is selected in Parameter 3, the Transaction parameter is no longer valid and goes blank. When it goes blank, the Disable clause for Parameter 5 is activated, and the Override Transaction Ack Type gets disabled and set to None. Once you select a Transaction, Parameter 5 is re-enabled, but with no value. So you don't have the risk that you will accidentally use the value from last time. If you trust the procedure to calculate the correct value, you leave it blank, but if you don't you specify a value. If you aren't sure, you run it in Preview mode first, and the report tells you which value was selected, and then you can go back and change it before you run in Execute mode if necessary.
It's not a perfect solution, but it does give the option to sometimes override a value that you usually want calculated automatically. This case is more complex than is necessary--the basic theory is that you have a dropdown parameter with a Where clause that makes it dependant on another parameter, and then you have your override parameter set to Optional with a Disable parameter that checks whether that previous parameter has a value set.
I hope that helps somebody!