I’m going to explain how I used a Free Entry Quickview combined with one of the underused functions of the Advanced Find Feature in Prodiver, to create an interactive alert threshold. This technique can be used when your users want the ability to change the threshold to focus on particular groups of values.
I did this in response to a request from a client who wanted to set up several alert reports in their Diveport Portal. I used the same method in each one, using a different dimension and summary field according to what they wanted to measure.
- After creating your basic marker in ProDiver – in this example I was using Diver BI Time Series on the Customer dimension to measure the % difference between Sales this month-to-date and last month-to-date – the next step is to create a Free Entry Quickview (Edit Menu>Edit Quickviews).
2. Select the button “Add Free Entry Quickview“. I gave it the name “Threshold“. You need to set a default value but this can be changed by the user later. I have set the threshold here to -70, which means that when I set up my Advanced Find query I will be filtering on the records with a % change relating in some way to -70% (either =, >, < etc).
3. Click the “Find” button.
4. Then click the focus box and the “Advanced Find” button so that you get the options for creating an advanced query on your marker.
5. You will now have the functions for creating your query available to you. Click on your chosen summary column and use the function buttons to define your query. You can see this is where the Threshold Quick View will get picked up using the function “QVVal(Threshold)“. I also added an additional statement to filter out the empty rows which appear for the customers that were not active in either month.
6. You will be left with only those records that fulfill your query: in this case those customers whose sales were more that 70% lower this month-to-date than previous month-to-date. I applied this method to other reports on this Diveport to look at Product sales v last month; Invoices with a margin lower than a certain percentage; and Products with a margin lower than a certain percentage. But you could apply it to almost anything that you are comparing.
You can use this simply in Prodiver, or publish to Diveport as I did below.
7. Once published, the user can type in different percentage values and refresh the page by clicking GO, and create alert lists for various scenarios.
Additional Quickviews for further subdivision, say for Account Managers, would be a useful addition in certain cases. The lists can be used to address the issues raised – why aren’t these customers buying as much as usual? Why are these products being sold at such a low margin? etc etc. The lists can be downloaded to Pdf or Excel for attack by those responsible – or viewed on tablet by managers on the road for example. You can also send as alerts by daily email – more frequently if you have a need for real-time information and you have a Diver BI Tunnel set up into the database.
A few months ago Amanda O’Connor submitted a really helpful post on setting up an alert on exceptions. So all the steps relating to creating the broadcast to email for your exception report are covered off in her blog which you can read here.
If you have any queries on this technique please get in touch through the form below.