Seeking ideas for customized data-driven notifications

[joyce.young]

Hello,

I’m on about week two of working on production dashboards and learning as I’m going. I’m also a developer/programmer with lots of experience as a database developer, but using BI and visual presentation of data outside of tables or spreadsheets is a bit new to me so I can use all the help and advice you can offer. I’m also looking into a few of the suggested books from this thread: https://dundas.influitive.com/discussions/topics/18576?page=1

Right now I’m looking for suggestions on how to best implement data-driven and time condition-based notifications on a dashboard.

My “IRS Form 8300” dashboard has two metric sets on it: one is a “MEMBER NEEDS TO FILL OUT THIS FORM” kind of metric set which is more often than not an empty set of data and the other is “MEMBER HAS THIS FORM FILLED OUT” which has historic information about how many times the form was filled out with amounts.

Right now I’m sending the excel file that contains both of these metric sets every weekday with a notification and usually the tab for “NEEDS” is empty. Since that is what we need to focus on from day to day I’d like to limit that to only sending it if the “NEEDS” metric set has data in it. Basically if rowcount is greater than zero on “NEEDS”, then send the notification, else do nothing. I suspect there is some custom scripting I might need to do for this?

Then I’d like to know how to send another/secondary notification to a slightly different user group if that same data has been sitting there for 7 days or longer.

Suggestions, advice and even wild ideas are welcome.

[ariel.pohoryles.1 (Ariel Pohoryles)]

To send notifications only when the "NEEDS" is not empty, I would add a count measure to your metric set and then setup a state that is checking if the count is greater than or equal to 1. You can setup that count measure to be hidden (uncheck the visible property for it) so it doesn't show on the metric set. Finally, you would setup a data driven notification with a condition of "enter state" where you will target the state you defined as mentioned above. Note that in V4 this may require targeting multiple cells but in V5 you will be able to just target an entire column in your metric set without having to target a certain selection of cells.

For the secondary notification - you would use the condition "stays in state" with 7 required samples out of 7 samples. That is again something that may be easier in V5 as you won't have to target specific cells but just specify the entire column. The beta progarm for V5 will be announced soon so I encourage you to try it then and share some early feedback with us.

[joyce.young]

Thanks for the insight. This solution sounds feasible, but I’m having trouble with the details so I’m hoping to get a little more help. I’ve read both pages you linked to and am not finding the info I need on either.

I created a count on my metric set. I’ve tried to come at this a couple ways and I’m not getting any email at all, even when I try to make it just send a notification for a “count > 0“. I also have “polls” not “samples” for options, so I’m not sure if that is just another way to describe what I need to do.

Screenshots provided to show where I’m confused.

I’d appreciate any help you can provide.

[sandy.wu]

When you choose Poll option, it means the system will sample the data based on the schedule rule for a number of polls, in your case, 6 polls. After 6 polls of sampling, if all sampling data stays in the target states, you will get an Email notification. From the screenshot you provided, it seems that you haven't specified the Target States, you will need to set a target states by dragging one of the predefined states (e.g. No_email_needed) to the Target States section or Add a Custom State.

[joyce.young]

Hello,

I’m still trying to figure out how to get the Data Driven Notification to work with a state. I’m not understanding how to correlate the STATE tab to (record) count > 0. I thought I had it but I have 10 records in my current data set and I have it set to POLL=1 but it’s not doing anything.

Suggestions?

[sandy.wu]

When you click “Add Target Cell,” you should see cells colored for the States that you defined on the metric set (e.g. count>0), something like this:

You can select any cell that has the target state for testing. In my case, I selected July, 2017 because this cell has the target state. When the system polls the data and sees the data stays in the state for 6 polls, it sends out the notification.

One thing you may need to be aware of is, when you select the target cells, you can only select a cell at its original displayed level, e.g. you cannot expand the rows and select the lower level cells if the table is originally collapsed.

If you still don’t get the Email notification, you may need to check a few things to make sure they were set up correctly:

1. What schedule rule did you set up for the notification? Was it on daily basis or Intra-Day recurrence?

2. Is your scheduler up and running?

3. Is your email setting correct? Can you test your email setting to make sure it works?

4. Make sure the Internal Application URL is set up correctly.

You may want to set up a Note-based notification first and add notes to the metric set. If everything is configured correctly, you should be able to get Email notification once a note is added to the metric set.

If you are still having issue with this, you might require more active troubleshooting. Please send the all the information I mentioned above to Dundas Support.

[joyce.young]

Hi Sandy,

Thanks so much for this help.

I know my notifications work on both our test and prod, since I have scheduled notifications on each running every day until I figure out how to do the data driven and state ones. I have not tried the note-based since I don’t see us using them. However I anticipate using the data-driven functionality quite a bit.

I do have a “Target Cell” of all selected.

I’m fairly sure it’s just me not understanding how the states work so I’m contacting support to see if I can get some hands-on help since I just can’t seem to figure this out.

[joyce.young]

I’ve got my states working; thanks Sandy.

Part of it was the scheduler was not enabled on my test instance, part was the email was not configured on my test instance and the biggest part was me not knowing how to do states.

So now that I’ve got count-based states worked out, I’d like to know how to do slightly more complex ones.

My next data driven notification should send a notification if the oldest record (row) per member in my table has a date that is seven days older than the current date. I can do this in SQL but I’m not understanding how to do it with a state.

For example, all the data (which represents a member) below should all be in the state “SEVEN DAYS OR OLDER” but I’m not sure how to set that state up since the rows will change from member to member.

[sandy.wu]

Hi Joyce,

I am glad to know that you got the data driven notification to work. Great job!

For this new requirement, you could create a Calculated Element in the data cube that calculates the difference between two dates. The calculated element will return the number of days difference between today and the transaction date. Something like this:

Please note that the script you put in the Data Cube Calculated Element is DundasScript simliar to C# programming language.

After this, you would have a new measure in your data cube.

You can then set up a State on DayDiff and check if DayDiff >= 7 in your state. Further, you will use this state to send out the Data Driven Notification.

Hope this helps.

Sandy

[joyce.young]

Thanks Sandy,

I’m understanding the concept, but the count of days between today and the transaction_date seems to be calculated wrong. And I’m also not sure I have the state set up correct, although I do seem to get the notification. Lastly I’m not sure that I have to do this as separate states (conditions) for each thing or if I can put multiple conditions into a state.

I’ve got the Calculated Element in my Data Cube:

Problem 1: It does create a problem with the Data Preview on the Data CUBE though:

Problem 2: It seems to work on my Dashboard, except that the numbers of days is not being calculated correct, e.g. the first instance where it seems Oct 16, 2017 to today is 9 days, but the second one shows it at 35 and the calculated value of 163 for TodayMinusSevenDays from the Sept 14, 2017 date is complete wrong).

Problem 3: I’m not sure this is what I’m trying to say with my state, even though it is sending me the email:

[elia.scher.1 (Elia Scher)]

Most likely that problems 2 and 3 are a direct result of problem 1.

I suspect that the data cube issue is because you have null values in the data and the sample that Sandy provided doesn't account for that. Could you get the technical details for that error and send them to Support? They should be able to help you resolve this.