“DataDriven”meansthattheselectionofproductsorcustomersinProphecyreportsisdrivenbydata,eitherfromyour Prophecy database itself or, in fact, from any other database. Here is a list of ideas for possible data-driven selections:
Idea1isafarmoreelegantsolutiontothe “Showmeonlytheproductslistedinmy customer” than any existing option!
A‘Special’datadrivenselectionscriptcomprisesjust two elements:•AnODBCdatabaseconnectionstringto connectProphecytothedatabasecontaining the data to be queried.•TheSQLofthequerythatwillberunwhenthe ‘Special’ is selected by the user.SpecialexpansionvariablescanbeusedintheSQL whichareexpandedeachtimeatruntime,justbefore theSQLisexecuted.Forexample,youwouldusethe variable{CustomerSelection}whenyouare returningalltheproductsboughtbythecurrently selectedcustomer.TheSQLwouldthenexpandfrom “..IN({CustomerSelection})...”to “IN(’cust001’,’cust052’<etc>”toreflectthe ‘live’ Prophecy customer selection.Thegoodnewsisthatfromauserviewpoint youdon’tneedtoknowthis!Youjustreadthe shortandlongdescriptioninthedialogbox pictured here!Onemorethingtonoticeaboutthedialogbox listingtheavailable‘Specials’.Lookatthe panellabelled‘Options’.Forselectionscripts whichdependonwhat’sselectedonthe‘other’ dimension(customerswhenyou’reinthe productselectororviceversaonthecustomer selector)youcangetProphecyto automaticallyre-runthespecialselection script.Forexample,sayyouhaveaproductselection scriptwhichselectsalltheproductswith forecastsforthecurrentlyselectedcustomers inyourreport.Whatthisfeaturedoesisre-run theproductselectionlogiceachtimeyouuse theCustomer selectortoselectadifferent customer.Theproductselectionisthereforetied dynamically to the customer selection!
Data Driven Product and Customer Selections are coming in this
Idea1isafarmoreelegantsolutiontothe“Showmeonlytheproductslisted in my customer” than any existing option!
How a user would use this feature
ThereisanewbuttonontheProductandCustomerSelectordialogboxes- “Specials”:This opens a new dialog box with the available special selection scripts.Whenyouselectoneof thesescriptsandclick ‘Go’theSQLqueryin thebackgroundis expandedandrun.Thelistofcodesitreturnswillbe automaticallypastedintotheselectordialogasifyou’d selected them yourself by hand.Remember,thisfeaturecanqueryanydatabase.Aslongas thequeryexecutesreasonablyquicklyandreturnsalistof product or customer code it can be used for this purpose.
How does it work?
A‘Special’datadrivenselectionscriptcomprisesjusttwo elements:•AnODBCdatabaseconnectionstringtoconnect Prophecytothedatabasecontainingthedatatobe queried.•TheSQLofthequerythatwillberunwhenthe ‘Special’ is selected by the user.
SpecialexpansionvariablescanbeusedintheSQLwhichare expandedeachtimeatruntime,justbeforetheSQLis executed.Forexample,youwouldusethevariable {CustomerSelection}whenyouarereturningalltheproductsboughtby thecurrentlyselectedcustomer.TheSQLwouldthenexpandfrom“..IN( {CustomerSelection})...”to“IN(’cust001’,’cust052’<etc>”to reflect the ‘live’ Prophecy customer selection.Thegoodnewsisthatfromauserviewpointyoudon’tneedtoknowthis!You just read the short and long description in the dialog box pictured here!Onemorethingtonoticeaboutthedialogboxlistingtheavailable‘Specials’.Lookatthepanellabelled‘Options’.Forselectionscriptswhichdependon what’sselectedonthe‘other’dimension(customerswhenyou’reinthe productselectororviceversaonthecustomerselector)youcanget Prophecy to automatically re-run the special selection script.Forexample,sayyouhaveaproductselectionscriptwhichselectsallthe productswithforecastsforthecurrentlyselectedcustomersinyourreport.Whatthisfeaturedoesisre-runtheproductselectionlogiceachtimeyouuse theCustomer selectortoselectadifferentcustomer.Theproductselectionis therefore tied dynamically to the customer selection!