Output/SQL: Constituent Info, Order Details to include Initiator

Here's some help to get you started. You'll still need to add in the contact perf tables you'd like include. Make sure to define which criteria you need in the WHERE clause too.

SELECT DISTINCT o.order_dt, o.order_no, o.initiator_no, ini_name.display_name AS initiator_name, o.customer_no, own_name.display_name AS order_owner_name, o.delivery,
d.description AS delivery_method_desc, o.channel, sc.description AS channel_desc
FROM T_ORDER AS o
JOIN FT_CONSTITUENT_DISPLAY_NAME() AS own_name ON own_name.customer_no = o.customer_no
JOIN TR_SALES_CHANNEL AS sc ON o.channel = sc.id
JOIN TR_SHIP_METHOD AS d ON o.delivery = d.id
LEFT JOIN FT_CONSTITUENT_DISPLAY_NAME() AS ini_name ON o.initiator_no = ini_name.customer_no

This is great, Sara. Thank you so much!

I'll give this a whirl and pop back in if I get stuck. Much appreciated

Charles the Order Export report under Ticketing Processing gives you everything except the initiator.  the import_ref_no in that report is the order number.   We are on V14 maybe V15 includes the initiator in that report.  You might want to check if your on V15. 

Thanks, Terry.

We are still on v14 and I had tried using the Order Export Utility. But, like you mentioned, it did not include the initiator. Good exercise in seeing how the SQL works (thanks again Sara) and continue to learn more about the standard reports. We went live this past August--so still coming up to speed on data extraction.

Hi Charles,

You might also be interested in looking at the Reporting View: BI.VT_ORDER_DETAIL? It has most of what you're after in one place. BI.VT_CUSTOMER also provides easy access to customer information in one place. These reporting views are enhanced as needed with every version release.

https://community.tessituranetwork.com/tessitura_software_forums/f/tessitura_shared_reports-9/18887/tessitura-analytics---new-views

Best,
Chris

What is the best way to bulk extract the BI views for the organizations who are on RAMP who would like to do advanced analytics based on this data?

Thanks, Chris.

I did take a look at the BI View tables but wasn't sure about the recency of the data. I appreciate you including the link to the new views. Much appreciated!

Hey Tom,

Great question! Was nice to meet you at TLCC.

I have been wondering the same. We are still on v14 with plans to upgrade to v15 in the coming months. What I learned about Tessitura Analytics at the conference was encouraging, but I also see the need to tap directly into the data to develop ad-hoc analysis models with whatever flavor of BI tool one chooses. I'm wondering if there are limitations currently due to security on RAMP. I'm wondering if other RAMP-based orgs have utilized an in-between cloud solution--maybe Azure?

We went fully live on Tess this past August and are still learning our way around the platform.

Hi Charles,

The BI Views query the live Tessitura database tables, so they'll produce up-to-the-moment data.

Best,
Chris

Hi Tom and Charles,

I'm stepping out of my depth a bit, but if you are able to write a custom report that pulls data from a BI view, can it then be scheduled to run and save as a delimited file to your FTP server location, (this assumes you're not including sensitive customer information.) Then pick it up from there?

Chris

Hi Charles and Terry! The Order Export Utility can be saved to Excel/CSV and includes 10 additional "local use" columns when you do so. The report calls a customizable stored procedure called LP_ORDER_EXPORT which is specifically designed to let you populate things like this into the output. It will pass in a session id, which you can use to modify rows in TW_ORDER_IMPORT. This approach means that you can easily make the report available to end users when you're finished. Remember to experiment in your Test system first. 

-Michael Flaherty-Wilcox, Tessitura Network 

Has anyone tried using OData calls to SSRS reports?  See this description.  This looks like it should work with Microsoft's Power Query/ Power BI

https://docs.microsoft.com/en-us/sql/reporting-services/report-builder/generate-data-feeds-from-a-report-report-builder-and-ssrs?view=sql-server-2017

However, I'm not clear how many other tools support ODATA?

In doing a quick look I see that R's CRAN repository has this.

https://cran.r-project.org/web/packages/OData/OData.pdf

Which means that most other tools that support R Libraries might also support this.  

The question is how performant, something like this might be.

Looks like there might be some Python Tools as well.

https://www.odata.org/libraries/

Anyone want to sit down with me and see if we can figure out how to make this happen?

Hi Michael! Great to see you here!

This is very helpful information and something I will need to experiment with in our Test system. If I can get it to work, Christmas will come early for a few folks that have given me their report wish list!

Appreciate the tips and tricks, Michael.

Thanks,

Charles