In dashboard filter, concatenate values to filter between two report

Roar Iversen shared this question 9 months ago
Answered

Hi, I have two reports, both linked in the dashboard with the same filter/column. However some of the values in the linked report field does not appear as selectable in the master report linked field. The consequence is then that I cannot choose to filter on values in the linked report which isn't in the master report. The consequence is that when filter some results are missing in the output as they are in the linked report only.

Is it possible to concatenate or control the values of the columns of the two reports such that I can filter on the complete selection of values between the two reports?

Comments (8)

photo
1

Hi Roar,

Thanks for contacting Yellowfin!

To help me understand your problem a little more, could you describe the values of the linked report fields that don't appear as selectable in the master report. Are they calculated fields or otherwise generated values?

My line of thinking is that it might be best to create those filters or values in the View, so that they can then be shared by both reports.

Let me know if that that helps or wouldn't work for what you want to create.

Kind regards,

Chris

photo
1

Hi,

Attached are four screengrabs. The first screen grab shows the main report on top and then the report linked to this. The two reports have similarly named fields as you can see from the data. Both have the same named fields I'm filtering on; Initiator and Man Rep.

The second screengrab shows the filter Man Rep and from the lower righthand shape you can see this appears in the listing of the second report, but doesn't appear in the search box when searching. My conclusion is that the filter only pulls data from the main report. The third screengrab shows the filter Initiator and from the search box this doesn't appear either.

Neither values are registered in Initiator/Man Rep columns of the main/top report.

To verify the filter I've attached a fourth screengrab showing successful extraction of reports where the Initiator and Man Rep appears in both report types.

I hope this clarifies some more.

I haven't worked with filter in views so I cannot say whether this would work or not, but the filters have to be dynamic.

regards Roar

photo
1

Hi Roar,

Thanks for clarifying. I'll need to get some feedback for you on whether this is intended, a bug or a current limitation of the design. As soon as I know I'll be able to advise further.

Kind regards,

Chris

photo
1

Hi Roar,

Can I ask how you've set up the link between the reports? It's probably best to make sure that the filter is present in both reports individually and then link filter to filter.

Kind regards,

Chris

photo
1

Hi,

Please find attached how I've setup the filters. I'm not using Link Data as I understand this have a different purpose. Also I understand that all the filtering is done in the main report, the others follow along.

regards Roar

photo
1

Hi Roar,

Thanks for clarifying. Before you set up the filter linking in screengrabs 6 and 7, was the green tick present for the second report, as in screengrab 8? i.e. if you remove the filter linking, can you link them the other way.

If that's not possible then there must be a limitation of that second report preventing linking. If it is, then I think it points to a limitation of Yellowfin in version 8 and I'll need to see if I can replicate it on my side.

Kind regards,

Chris

photo
1

Hi Chris,

The example I sent only has two reports, but the way I want to use and setup this dashboard is using 10+ reports. Wouldn't then linking the reverse way not be a solution?

And yes the green tick was present.

regards Roar

photo
1

Hi Roar,

Can I ask one final question on how you've set up your two reports - How have you set up the reports so that they contain the same fields but not the same data, is there a fixed filter you've applied to one at report creation?

Kind regards,

Chris

photo
1

Hi,

No, this is two different reports which have the same type data fields, e.g. the name of the reporter. The process is e.g. that a person can make one type of report or the other. Then I want to, by using one filter, know how many, by listing, he has made of both types of reports.

Not sure if this makes it clearer.

regards Roar

photo
1

Hi Roar,

As it stands right now, there's a parent->child relationship with filters on the dashboard, where the parent/master report dictates what can be shown in the remaining reports on the dashboard.

However, that said, I have a couple of solutions for you -

You could create another, third report that contains all data types and data, this report can be hidden, but all the remaining reports on the dashboard are linked to that.

If you feel confident using SQL, you could go into your master report, create a custom query filter that pulls in all the relevant data and then link your filters as before. We have some documentation here. Scroll down to the Filter Settings By Type section and then expand the Entry Style subheading. The Value List Setup describes using a custom query.


Let me know if either of those work for you.

Kind regards,

Chris

photo
1

Hi,

Thank you. Not sure how to solve, but I'll investigate making a third report.

regards Roar

photo
1

No worries! I'll keep this open for a while longer, in case you want to discuss it further.

Kind regards,

Chris

photo
1

Hi,

I tried a different approach where I use a union sub query, i.e. appending the data from four reports. The output looks nice, but when filtering it again, alas, it doesn't use all the data in the column, but only what is contained in the table which makes up the master query.

Is solving this easier by adjusting one of my filter settings?

See attached screengrabs.

regards Roar

photo
1

Hi,

Did one more test. Instead of using the values on the list I typed the exact correct name and executed the filtering. This as my name is not in the list, but in the reports appended to the main query.

Then I got the correct filtered result. So the values which, now, populates the list in the filter is from the main query only. Doable solution, but not workable as a permanent solution as it requires too much knowledge up front.

regards Roar

photo
1

Hi Roar,

Are you using Cached Values for your main query filter? If so, if you now rebuild the cache for the filter with the union sub-queries created, does it add the additional values?

Kind regards,

Chris

photo
1

Hi,

Please as I've never done a rebuild of the cache can you guide me?

To make sure I get it correctly the first time. If this works, does the cache refresh by itself for future?

regards Roar

photo
1

Hi Roar,

I'm not entirely sure this will work for your particular scenario, but using our Wiki page as a reference, scroll down to Dimension Filter Settings. You'll probably want to set the Value List Setup to Cached Values, rather than Cached Values on Demand, as I believe you intend to use this report in a dashboard, so you don't want them refreshing every time the report is run. Then click on Refresh Now to build the list of values.

dimension-filter-settings-dialog


You can configure the refreshing behaviour in the General Filter settings and set a schedule or do it manually.

03generalSettings

Let me know how that goes.

Kind regards,

Chris

photo
1

Hi,

My selecting for Filter Formatting looks entirely different from what you are showing, see attachment.

br Roar

photo
1

Hi Roar,

Is the site field just a standard text dimension? And are you looking at the filter settings from the master query?

Kind regards,

Chris

photo
1

Hi,

Yes, it is a text dimension and I'm looking at it from the master query.

/8547bd7ca63466d879c697a8703970b3

br Roar

photo
photo
1

Hi Roar

Apologies for the delay, I hope you had a pleasant Easter break!

I've had some feedback internally on what we can try here, but to get the ball rolling and so we know where to look, could you let me know which version of Yellowfin you're currently running? The full release number can be found at http://yourYellowfinURL/info.jsp

Kind regards,Chris

photo
1

Hi,

Yes - had a nice Easter break.

Version is 8.0.2., see attached file.

I also have noticed that sometimes a refresh symbol is displayed to the left of Refresh, see below, but I haven't paid enough attention to when and where.

regards Roar

/695c867f5232cce702e78f750dd23f92

photo
1

Hi Roar,

Having tested this in both 8.0.2 and the latest version of 9, it seems the behaviour is the same and when configuring a link to filter with a union sub-query, it removes the option to set up the filter formatting:

/14f9b9181ad40b45ffc328409c5f8596

/ab8c254e0dfdd0ab2406db09714a8362

Which leads me to believe that this won't be possible using the cached values method.

We may have to double back and possibly try the hidden 'master' report method. Let me know your thoughts.

Kind regards,

Chris

photo
1

Hi,

What I've done as a way to workaround to this is to use as the master view the dataset with the most common information - 95%.

The more important filters which might not hold the requested data as part of the master view, but will be in the sub-queried views, I've used the 'contains' option rather than 'in list'. This works, but means that I have to make more instructions to the user of the dashboard.

/6b4245059497522655fff02593ef0423

br Roar

photo
1

Hi Roar,

That's good to know that you were able to find a workaround using the 'contains' option instead. The setup makes sense to me, but I can see that having to explain it to users would require some additional steps. Let me know if there's anything else you'd like to discuss around this or if you're happy with that solution.

Kind regards,

Chris

photo
1

Hi Chris,

We'll see how this works.

If you have something handy on the 'hidden' master report I'd appreciate that. Otherwise we can close this for now.

br Roar

photo
1

Hi Roar,

It's essentially where you create child reports based off a report includes all the data available and isn't included in the dashboard, however your solution may be just as effective.

Kind regards,

Chris