Hi,
I am trying to create a daily scheduled report which tells me if anyone considered a Development Contact (as per their constituency) booked a ticket/made an order yesterday.
The way I've gone about this so far is to create a dynamic list with the criteria of all Development Contacts, and then those with a ticket order date of the run date-1. I've then used the 'Execute an outputs set" scheduled report to get this to my inbox daily.
This method sort of works, but it doesn't look great and doesn't pull through their constituency or order details.
Does anyone have a better method of doing this? I'd love to hear your ideas!
I would suggest checking out the Order Export Utility — despite its name, it makes for a pretty good report format if you're trying to get order details. You can filter it by a dynamic list, and if it doesn't have all of the constituent information you need, there are 10 local use fields that can be customized using the LP_ORDER_EXPORT procedure that will display in the report's data formats (Excel/CSV/etc.)
Nick, thanks for this - it's super helpful.
Could you enlighten me as to the steps to add a custom field such as Constituency to this? I'm not used to updating procedures.
Thanks
You will need access to your SQL server. After connecting in SSMS, navigate to the LP_ORDER_EXPORT stored procedure in Object Explorer, right click, and choose "Modify". This will open the procedure code in a query window. Then you will need to add code to the procedure that updates the local_use0 column in the TW_ORDER_IMPORT table based on the passed-in @session_id parameter and the customer_no column.
What this query will actually look like will depend on what kind of constituency information you are displaying — it could be a specific constituency code, or the top code by rank, or a string of multiple codes. There should be a few other threads on this kind of thing, and I've got some custom views and functions that I use in my database for this.
Happy to help further in Slack chat, get info here: https://tessituracoders.bitbucket.io
Not sure if you want to tackle this...
I slapped together a jump off if you decide to build your own report. Let me know if you have any questions. The file is on my profile named "JonathanMace.sql"
Has 3 parameters, constituncey, start and end date.
You can adjust the final select to pull whatever order information you need and the constitunecy. You may want to consider end date too in reference to the constitunecy. The ssrs part is straight forward too, but if you need help again let me know.
Travis
Nick and Travis - thank you both for your help on this!
I think I'll need to get someone with some SQL experience to take a look now, but this is a great start.
Thanks.