DataFilterID in ReportBroadcast table

Hello Brendan,

Thank you for reaching out to us.

I'm afraid that I am not to sure what the DataFilterID links to. But for the detail you are looking for I would recommend using the broadcast field within the ReportFilter table to join to your broadcast or you could use a clause to find these filters by using the ReportFilter.FilterAlertCode field for the string "BROADCASTFILTER".

The reportfilter table will show you the where clause operator, column operator and the values used in the filters. You can then use the FieldTemplateID to reference this to the ReportFieldTemplate table which would allow you to find the field affected by this filter.

I hope this helps. Please let me know if you have any further questions.

Kind regards,

Nathan Goddard

Hi Nathan,

Thanks for your help with this - so the reportfilter table gives me the alert settings and values, and the ability to see what column(s) each alert is linked to. What about the assign filters settings for basic broadcast (current/default/bookmarked values) or Advanced filters (value applied per recipient per run)? Is it potentially through the reportinstance and reportinstancefilter tables if there's a way to link an instance to each broadcast event?

Kind regards,

Brendan

Hi Brendan,

Thank you for getting back to me.

Is it potentially through the reportinstance and reportinstancefilter tables if there's a way to link an instance to each broadcast event?

- Yes, you should be able to find what you are looking for within the reportinstancefilter or reportfilter tables. As for seeing each broadcast event, you could use the reportbroadcastresult table, this will show the individual broadcasts sent, to who, the reportID, broadcastID, etc.

I hope this helps, please let me know if you have any further questions.

Kind regards,

Nathan Goddard

Thanks Nathan, I did some more testing and comparison of fields yesterday and have managed to find a way to pull out saved filter values for bookmarks in different scenarios, and link up with advanced broadcast settings as well as event data. Happy to close this ticket.

Appreciate your help!

Kind regards,

Brendan

Hi Brendan,

I am glad to hear that you have managed to find what you need.

I have now marked this questions as answered.

Have a fantastic week.

Kind regards,

Nathan Goddard

Thanks again Nathan. Just to polish off how far I can push this broadcast reporting... some final questions:

• Is there a known way to convert the formats for timestamp fields like LastModifiedGMTDateTime (eg 20190814060456) and LastModifiedTime (eg 21896) in the broadcast table, similarly ScheduledLocalRunTime (eg 26100) in the TaskSchedule table into a timestamp eg hh:mm:ss? I've tried the various converters in the view to no avail.
• Is there a reason why the EventTime field values are all 0 (even though the actual event dates are recorded in EventDate)

Kind regards,

Brendan

Hi Brendan,

These are some very good questions, hopefully the below should help;

1. Convert timestamp (eg 20190814060456, there are a few elements here, depending on the DB type you are using the syntax may vary, I will be using MySQL in my example.

1a. For the date you can use the date_format() function, this will extract the date in any standard date format, examples can be found here.

1b. For the time you can use the time_format() function, examples found here.

1c. Or if you would like to do all of this within YF, you can select the field as a timestamp and use the timestamp formatter seen below.

2. Last Modified Time or Scheduled Local Run Time (eg 21896) these times are stored as seconds from midnight. So in the example below you can see that the last modified time is 38248 seconds which is 10:37:28. Within MySQL you can use the sec_to_time() Function to extract this from this form. Alternatively you could use the LastModifiedGMTDateTime field and use the options from point 1.

3. EventTime, I have checked all of my local databases to confirm (11), and these all show 0 in event time as well. I would assume that these field may have been used to store time in previous versions of Yellowfin but this is now stored in the GMTDateTime field in newer releases.

I hope this helps.

Kind regards,

Nathan Goddard

Hi Nathan, thanks for the help. I don't have the date_format/time_format/sec_to_time functions available in my instance but I have managed to convert the LastModifiedTime fields to timestamp using the Days Hours Minutes Seconds format option against the fields.

I've noted/cross-checked that the reportheader and reportbroadcast last modified date and time fields are in GMT time (and the GMT fields are different again!) but I'll work with that. Unless there's a setting on the instance for which timezone to store these in?

I might revisit these fields and the functions available once we've upgraded to the latest patch to see if anything has changed.

Thanks again,

Brendan

Hi Brendan,

Thank you for getting back to me.

Sorry if there was any confusion caused. The date_format/time_format/sec_to_time functions are database functions rather than functions within Yellowfin, you can use these within a freehand SQL calculated field within your view or report, but the using the timestamp format does give you these options as well. Effectively two options for a similar outcome.

As for the data stored within GMT, this is the function of the system and there is no way to change this. What you can do is set the field to have a set time zone which would mean that Yellowfin will adjust this data front end based on the logged in users timezone without having to store your data differently.

Select the Timestamp in your view then click on the down arrow and select Convert.

If you add a TimeZone Converter to the field it will allow you to set the Native TimeZone for your data. This means that when a user in Chicago views the data they will see that time has changed accordingly.

Hope this helps.

Kind regards,

Nathan Goddard

Hi Nathan, yeah I tried those functions as freehandsql in the view but it said they weren’t available. And I only get access to the Timezone convert option after I have applied a Java timestamp or SQL to timestamp (or similar?) converter and it reverts the date to 1 jan 1970 on all timestamps. So haven’t had much luck with those avenues, have tried whatever combinations I can think of to no avail. Not sure if it’s a version thing or otherwise.

Brendan

From: Support Queue <support@Yellowfin.bi>

Sent: Monday, November 18, 2019 8:21:10 PM

To: Brendan Codrington <Brendan.Codrington@acendre.com>

Subject: [EXTERNAL] New Comment in "DataFilterID in ReportBroadcast table"

Hi Brendan,

Sorry to hear that these options aren't working for you. When you stated that these options where not available in Freehand SQL what message are you seeing? Also can I confirm the database type on that your Yellowfin Configuration database is on?

Kind regards,

Nathan Goddard

Hey Nathan, when i use the freehand SQL builder it says "'time_format' is not a recognized built-in function name" (in both a freehand sql calculation and in a virtual table. And the Configuration Database (if that's what you're after) is Microsoft SQL Server 13.00.4001

Does that help at all?

Thanks,

Brendan

Hi Brendan,

Thank you for getting back to me, I believe I have worked it out, the example SQL I gave was for MySQL not MS SQL. Please find below examples of some MS SQL specific date/time formatting.

1. FORMAT ( value, format [, culture ] ) this would allow the input of the timestamp and then a selected recognized format, details of which can be found here.

2.CONVERT(datatype, value, format value), this again allows you to input the timestamp and enforce the format, details found here.

This should work through the freehand SQL as it will match your database type.

Kind regards,

Nathan Goddard

Hi Nathan, thanks for looking again... unfortunately those functions don't work either - it accepts them but it doesn't convert. Unable to get TaskSchedule.LastRunGMTDateTime to appear in any different format to 20191124130013. I'm using workarounds on this for now unless you have any other ideas...

Kind regards,

Brendan

Hi Brendan,

I am sorry to hear that those options didnt work for you.

I believe there may be one final option which would be to use a converter within Yellowfin. Looking at the data type this specific field is held as a decimal rather than datetime which explains the behavior we have seen. There are two options that may work, 1. Text to SQL Timestamp or 2. Number to SQL Timestamp.

I have added the link to the wiki below to guide you through doing this.

Data Type Conversions

Please let me know the outcome.

Kind regards,

Nathan Goddard

Hey Nathan, ah yes I'd tried this too but hadn't documented the results til now. If I convert using those options it doesn't work with the recognised formats eg yy/MM/dd (produces blanks) and if I use part formats (YYMMDD) it produces a result but it's wrong.

E.g. recommended format:

and

Altered format:

Thanks,

Brendan

Hi Brendan,

You will need to set the format for the converter to the format the data is in so that the system can interpret it correctly so you will need to set it to YYYYMMDDHHMMSS.

This will allow the system to manage this as a timestamp then within the field format you should be able to change this to your desired outcome.

Please let me know how you get on.

Kind regards,

Nathan Goddard

Hey Nathan, appreciate your ongoing attempts to get this to work :)

I had a mild panic that maybe I'd missed trying that, but I think I had and with this result - it errors once I get past adding YYYYMM with both the date and timestamp converters:

Am I doing something obviously wrong???

Thanks,

Brendan

Hi Brendan,

You don't appear to be doing anything wrong, I have done the same in my local environment and the below is my output.

I maybe that the data within your task schedule table also shows date times that are 0 which could be causing the error you are seeing.

Would it be possible for you to try the below SQL

CONCAT(LastRunDate, ' ', (RIGHT('0' + CAST(LastRunTime / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((LastRunTime / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(LastRunTime % 60 AS VARCHAR),2)))

This converts the LastRunTime (seconds) to Hours, Minutes, Seconds and concatenates the date to the front of this, then you should be able to use the String to Timestamp converter in yellowfin to allow you to format it.

I hope this helps,

Kind regards,

Nathan Goddard

Interesting - so this does do something, presenting the last run time in the format of Nov 26 2019 12:00 AM 20:00:28 . If I try then try and use the converters available (no string to timestamp but text to date/text to timestamp) I have the same issue as before, in that it doesn't recognise the values and produces nulls.

Thanks,

Brendan

Hi Brendan,

I think (hopefully) I may have found a solution specific to SQL Server, could you try using the following in a calculated field

DATEADD(second, LastRunTime,LastRunDate)

This should if it behaves as expected keep formatting from the LastRunDate (YYYY-MM-DD HH:MM:SS.sss) and add the time in seconds from the LastRunTime field. I have tested this directly in SQL Server.

This should then be able to be read as a date time field.

Kind regards,

Nathan Goddard

Oh thanks Nathan, I think you might be onto something here. Looks good in the view config, i'll need to do more testing within the reports I've set up to make sure it all aligns etc.

Here's hoping, I'll let you know.

Kind regards,

Brendan

Nathan, you will be pleased to know that I think we have landed on the moon here. These formatted last run timestamps line up with the max broadcast result timestamps, in the correct timezone, with the exception being exactly what I was trying to achieve which is showing when the last run occurs but, for whatever reason (eg no matching advanced broadcast recipients) no broadcast result is generated - see below example where last logged run timestamp is the max broadcast result timestamp.

Thank you so much for your persistence in finding a solution for my various questions - as a result, I've been able to build a really comprehensive suite of broadcast reports that will enable us to monitor both broadcast settings and results en masse.

Happy to close this off so you can put this thread to bed :)

Kind regards,

Brendan

Hello Brendan,

I am very glad to hear that we managed to get there!

It sounds like you have built a very interesting suite for managing your broadcasts, thank you very much for your patience on this. I have marked this questions as answered.

I hope you have a fantastic week.

Kind regards,

Nathan Goddard