Filter Linking on Dashboards

Hi Craig,

I've just created a new ticket for the second issue we discussed on the call earlier.

I've made an attempt at replicating this, and I've actually faced no issues. Creating a Report with a filter that is linked 2 years prior > last year > this year (in the Master Query) is able to be linked on the Dashboard to other Reports, both as the origin filter and as the linking filter.

Have I missed a detail where the Reports come from different Views, or do the date fields come from different views in the Dashboard you are creating? It might be easiest to recreate the Dashboard from the ground up using 2 Reports (one that has been updated with the subquery and one that was causing issues) to try and understand the root cause of your issue.

Kind regards,

Simon

Simon,

Again, thanks for the time this evening. There's nothing irregular about how the views are setup. The only behavior that I've seemed to be able to denote is this:

1. Let's say there are 5 reports that use a ridership view and 1 report that uses an on-time performance (OTP) view

2. Of the 5 ridership reports, 1 has the subqueries baked in (eventually they all will). The 1 OTP report also has subqueries baked in.

3. If I use a ridership report as the master date filter and link all report date filters to it, the OTP chart date filter doesn't show up in the link filters interaction. If I use the OTP chart date filter as the master, all the other ridership charts except the one I set up with subqueries show up as date filters.

I suspect as soon as I convert all the charts to subquery reports, then they will not show up as date filters to link to.

Hi Craig,

I'm still really struggling to replicate this on my end. I've created a handful of Reports with different types of filters (regular and linked subquery filters) originating from different Views, and it seems to work in each case that I've tried.

I understand that you've got an existing Dashboard that you are changing over, however, would you mind trying to build the Dashboard from the ground up, starting with the Report that you've changed over to the new style, and adding each Report as you change them over?

My current suspicion is that it's the Dashboard that you're working on that might be the issue rather than the Reports/ filters. Adding the Reports one by one should rule this out, or at least help us identify exactly which Report is the problem. At this stage, I'm unclear even which Report is behaving incorrectly.

Let me know if these next steps make sense, and let me know how you go.

Kind regards,

Simon

Hey Simon,

I'm trying to do it now and right away, with just 2 reports, I run into the same issue. So I have one spot report that's got a date between and one report with the new logic to do the year offsets. I'm attaching some screenshots so you can see the configuration, but it's clearly not reading the date field for the filters. As a side note, I spent the day creating stored procedures, creating sproc views and am able to get around this problem in the meantime...that works, but extremely labor intensive, and difficult to support. I'm hoping this is a bug that can be logged because I can't imagine what might be wrong with the actual dashboard here.

I can get on another call if needed. In case you're wondering if I just used the same dashboard in case maybe there was corruption, I didn't. I started a new one fresh.

Hi Craig,

If you want to send through another webex invitation, we can have another look and try to identify what is happening.

Kind regards,

Simon

Ok, give me a few

Ok Simon:

Hi Craig,

Thanks for joining me on the call earlier.

As we discussed, the issue was that the Reports were using two different Yellowfin fields that were stored as different data types, Data and Datetime, and were unable to be linked on the Dashboard. The solution to this would be to use a consistent field as a filter value, where I would suggest using the raw 'calendar_date' field, to avoid excessive CONVERT/CAST calls.

I couldn't find a specific query to find the filters used in Reports, however, you should be able to use the logic and tables from the two queries attached to get a list of Reports with a particular field value as a filter. Please make sure to make a backup of your environment, and not make any direct changes to the database as this is unsupported by Yellowfin and may cause irrevocable damage

Find Report by Column Name (='DEMOGRAPHIC')

SELECT ReportName FROM reportheader as h
WHERE ReportId in (
SELECT ReportId FROM reportfield as f
INNER JOIN reportfieldtemplate as t
WHERE t.FieldTemplateId=f.FieldTemplateId
AND t.ColumnName="DEMOGRAPHIC");

SELECT ReportFilter.ColumnOperator, ReportFilter.WhereClauseOperator, ReportHeader.ReportName, ReportHeader.ReportStatusCode
FROM ReportHeader
LEFT JOIN ReportFilter ON ReportHeader.ReportId = ReportFilter.ReportId AND ReportHeader.ReportStatusCode='OPEN'​;

Kind regards,

Simon

Thanks again for the help, Simon! I'll check the queries out, though I can also see the convert/cast in the where clauses in reportinstance as well. I also noticed that in the view, if you look at the sql in the information tooltip, the calendar date (datetime in SQL) still gets converted to varchar...but at least in the report, I'm not seeing the conversions on the column itself anymore. Thanks!

Hi Craig,

The ReportInstance table refers to instances of the ReportHeader class, so they are all historic versions of the Reports that have been run. If there haven't been any changes to the Reports, I would disregard this. I'm not sure about the conversion in the View, I would double-check that there aren't other fields that are derived from the same database field, or if there are other conversions being applied to an instance of the calendar_date field.

If you are happy to continue investigating this on your end, I will go ahead and mark this as closed.

Kind regards,

Simon

Hi Craig,

I'm just messaging to let you know that I am marking this as closed for the moment.

Please feel free to reach out if you have any further questions, and someone will reach out to you shortly.

Kind regards,

Simon

Thanks Simon...been slammed with some other happenings, but will be trying to work through it this week. I'll write back if I need to.

Thanks!

Hi Craig,

Not a problem.

I'll mark this as closed, however, please feel free to reach out if you have any follow-up questions, referencing this ticket if needed. Please do not respond here as it may not be seen in a timely manner.

Kind regards,

Simon