Filter Linking on Dashboards

Craig Dubin shared this problem 2 months ago
Resolved

Hi,


I am unable to link a filter from one Report to another Report on a Dashboard, when it is used in a linked filter, subquery hierarchy.

Comments (13)

photo
1

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

photo
1

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.

photo
1

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

photo
1

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.

photo
1

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

photo
1

Ok, give me a few

photo
1

Ok Simon:

When it's time, join your Webex meeting here.

More ways to join: Join from the meeting link https://rfta.webex.com/rfta/j.php?MTID=mf3d29b417fdbc8ed4a0936f5fb047c12 Join by meeting number Meeting number (access code): 2457 652 0591 Meeting password: nQm6K95rMaV

Tap to join from a mobile device (attendees only)

+1-650-479-3208,,24576520591## Call-in toll number (US/Canada)

1-877-668-4493,,24576520591## Call-in toll-free number (US/Canada)


Join by phone

1-650-479-3208 Call-in toll number (US/Canada)

1-877-668-4493 Call-in toll-free number (US/Canada)

Global call-in numbers | Toll-free calling restrictions


Join from a video system or application

Dial 24576520591@rfta.webex.com

You can also dial 173.243.2.68 and enter your meeting number.

photo
1

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");
Find All Filters used in Open Reports

SELECT ReportFilter.ColumnOperator, ReportFilter.WhereClauseOperator, ReportHeader.ReportName, ReportHeader.ReportStatusCode
FROM ReportHeader
LEFT JOIN ReportFilter ON ReportHeader.ReportId = ReportFilter.ReportId AND ReportHeader.ReportStatusCode='OPEN'​;
Let me know if this helps at all, or if you require further guidance. Otherwise, I will shortly go ahead and mark this as answered.


Kind regards,

Simon

photo
1

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!

photo
1

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

photo
1

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

photo
1

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!

photo
1

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