Using Drill Anywhere with SubQueries

Renato Marcello dos Reis shared this problem 2 years ago
Defect Logged

Hi guys,

I was wondering if there's a way to use Drill Anywhere on a report who's using subqueries? Is this possible?

Thanks in advance

Comments (1)

photo
1

Hi Renato,

Thanks for reaching out. This functionality should be possible. What build of 7.3 are you currently on?

As of 7.3 20180208 an enhancement was developed and published where "there's been an option in Report Format -> Data

21095_image-2018-01-12-15-36-56-483

This will only show if there are A) Basic Sub-queries and B) Drill anywhere is turned on

Turning this on will mean that when you drill anywhere with a basic sub-query it will add that drill filter to the parent sub-query and the child sub-queries."

This functionality does not apply to advanced sub-queries, however.

Regards,

Mike

photo
1

Hi Mike,


Thanks for your help on this case.


I'm currently using 7.35.16 version (build: 20190306), but strangely, it's not working for me. My report has all the pre-requisites you've told. The report has 3 basic subqueries, but i've tried with only one to test. The fields I've used has drill marked on, and the functionality it's turned on as well.


There are any configuration needed to use this feature?

PS: The image you've attached is not appearing for me for some reason.

photo
1

Hi Renato,

There is indeed a configuration that needs to be toggled in Report Format > Data. It's called "Link Drill Anywhere to Basic Sub Queries" and is only available when there's basic sub-queries and Drill Anywhere selected under Analysis Style.


I've attached the screenshot as an image attachment instead so you can see what the configuration looks like.

Regards,

Mike13444d5f4000109490cb30b22d81901e

photo
1

Hi Mike,

I've taken some prints to show you my example. There are 2 prints below where you can see the same options you've marked on your example. They're marked on the configuration spots because it's on another language and so you can understand better. Is there anything wrong?


51062209917af7e25beed6754039686c


99641cdab2bfce2e56f3d80361099ee3

photo
1

Hi Renato,

The setup here appears to be correct. I can see the fields aren't hyperlinked here though, so I guess the next question is: have the corresponding fields been configured for Drill Anywhere functionality in the reports' View?2a53b4e3fe876ad3b54975d6f6c971da

Regards,

Mike

photo
1

Sorry Mike, I forgot to attach this another print on my previous reply. Here it is.

aea12df0a4363f869cc81da214218b19

photo
1

Hi Renato,

It appears that you currently can't use Drill Anywhere on fields that have Join's applied to them. It seems this setting just applies to being able to use Drill Anywhere on the remaining fields in the report. Although, I suspect this is a bug.


As you can see below, if I change the Master Query field, I lose the Drill Anywhere option:


/4RFL7wVZRPGAAAAAElFTkSuQmCC

/feF0tKD8NqoAAAAASUVORK5CYII=

Also as you can see, the field that's not selected as Master Query does have the Drill Anywhere option.

I can't say with 100% certainty this is unexpected behavior, but I've gone ahead and raised this as a Defect so I can see what the dev team comes back with. Any potential updates regarding this matter will be posted here.

Regards,

Mikecbe531453f8a9fc004c6c3b2ee000b02b6ee400bdb5f1e40726895dabcee0c62

photo
1

Hi Mike.

I hope everything is fine with you.

I know it has some time I openned this topic, but it comes to me a request to build a report which this might be the only solution.

The problem I have, is that I created some kind of workaround in this case, but it's not working either. I was hoping you might help me with some info about it, or even some feedback over the original problem.

The workaround I create was to add the same field twice. By doing this, Yellowfin will mark the second field as enable to use Drill Through. The problem, is that when we use the drill, it does not apply to the child subqueries even if we enable the option in data named "link drill anywhere to basic sub queries".

Would you mind take a look if this is something we can solve?

Thanks one more time.

photo
1

Hi Renato,

Thanks for reaching out. Unfortunately, there's no updates on this task but I'm happy to try and see if we can come up with a workaround for you.

I feel like I mostly understand what you're asking, but can you possibly supply some screenshots of what your setup currently is and point out what exactly isn't linked, so I can be 100% sure we're looking at the same thing here?

Thanks,

Mike

photo
1

Hi Mike,

Let me try to give you the info you asked.

I've created an example, so you can do the same test in your enviroment.

1. I've created a report using only two fields. Gender (Text Field) and some quantity value which represents a sum of an integer field from that View.

2. From there, I had to create a simple subquery linking the same field ("Gender").

3. The next step is to add the same value field to the report, so the values from the master query and subquery can be the same, because no filters were applied.

4. Then, I've added the field Gender a second time in the master query, and turned on the Drill Through option.

5. Also, do not forget to turn on the "Link Drill Anywhere to Basic Sub Queries" option.

6. From now on, the report is created, and in my case, we have the same values as you can see in the following image.

151bf945866511804c7435919bb9b1d3

As you can see, we have a simple subquery which shows the same values from the master query.

You can also see that the hyperlink field is the "Gender Field", who was added a second time as said before. The field used to link the subquery it's not hyperlinked.

If we just use the drill through feature to see for example, which corporations those values belong to, we now have the following situation.

a5ec5e1e8fcc26d40c63fa46657a1004

The main query will show the correct values. You can understand better if you just see the totals in the last line from the report, who is showing 47.826 in the main query which represents the female values for our example. The subquery just repeated the value and aplied a sum over then.

If there was more values on our drill, the subquery would just repeat the same value for each line.

I hope this can be of any help.

Thanks again.

Regards,

Renato Marcello

photo
1

Hi Renato,

Thanks for your response and for giving me replication steps I can use with our tutorial data. I'm able to get to the point you're showing:

/cdc7df819a768fa05a55fd978be51ead

In testing both options I see there's no difference in the SQL Query when toggling the "Link Drill Anywhere to Basic Sub Queries" option.

Off:

SELECT 
   T0.C1,
   T0.C2,
   T0.C4,
   T1.C7
FROM (
   SELECT DISTINCT
      "PERSON"."GENDER" AS C1,
      "TRAVELAGENCY"."AGENCYREGION" AS C2,
      SUM("ATHLETEFACT"."INVOICEDAMOUNT") AS C4
   FROM "ATHLETEFACT"
   INNER JOIN "PERSON"
   ON (
      "ATHLETEFACT"."PERSONID" = "PERSON"."PERSONID"
   )
   INNER JOIN "TRAVELAGENCY"
   ON (
      "ATHLETEFACT"."AGENCYID" = "TRAVELAGENCY"."AGENCYID"
   )
   WHERE (
      "PERSON"."GENDER" = 'FEMALE'
   )
   GROUP BY 
      "PERSON"."GENDER",
      "TRAVELAGENCY"."AGENCYREGION"

) T0
LEFT OUTER JOIN (
   SELECT DISTINCT
      "PERSON"."GENDER" AS C5,
      SUM("ATHLETEFACT"."INVOICEDAMOUNT") AS C7
   FROM "ATHLETEFACT"
   INNER JOIN "PERSON"
   ON (
      "ATHLETEFACT"."PERSONID" = "PERSON"."PERSONID"
   )
   GROUP BY 
      "PERSON"."GENDER"

) T1
ON T0.C1 = T1.C5
On:

SELECT 
   T0.C1,
   T0.C2,
   T0.C4,
   T1.C7
FROM (
   SELECT DISTINCT
      "PERSON"."GENDER" AS C1,
      "TRAVELAGENCY"."AGENCYREGION" AS C2,
      SUM("ATHLETEFACT"."INVOICEDAMOUNT") AS C4
   FROM "ATHLETEFACT"
   INNER JOIN "PERSON"
   ON (
      "ATHLETEFACT"."PERSONID" = "PERSON"."PERSONID"
   )
   INNER JOIN "TRAVELAGENCY"
   ON (
      "ATHLETEFACT"."AGENCYID" = "TRAVELAGENCY"."AGENCYID"
   )
   WHERE (
      "PERSON"."GENDER" = 'FEMALE'
   )
   GROUP BY 
      "PERSON"."GENDER",
      "TRAVELAGENCY"."AGENCYREGION"

) T0
LEFT OUTER JOIN (
   SELECT DISTINCT
      "PERSON"."GENDER" AS C5,
      SUM("ATHLETEFACT"."INVOICEDAMOUNT") AS C7
   FROM "ATHLETEFACT"
   INNER JOIN "PERSON"
   ON (
      "ATHLETEFACT"."PERSONID" = "PERSON"."PERSONID"
   )
   GROUP BY 
      "PERSON"."GENDER"

) T1
ON T0.C1 = T1.C5
I.e., they're exactly the same. As such, I just don't think we can come up with a workaround here as the functionality appears to just be broken - unless it happens to work out adding the same dimension in your sub-query and filtering there, which will only apply to the sub-query results. This is probably worth checking out in case it does work, but I can easily envision scenarios where it would not.

I've gone ahead and added this info to the task - that adding a duplicate of the Dimension in the Main Query does provide a hyperlink, but only applies to Main Query and that toggling the Link Drill Anywhere to Basic Sub Queries option doesn't actually do anything and have also requested an update. I hope to have something for you soon.

Regards,

Mike

photo
1

Hey mike,

I hope everything is fine with you.

I'm sorry to disturb you once more, but I received another task involving this exactly situation. Is there by any chance some feedback from the developers regarding this bug? Everytime I receive some report creation task, I keep myself hoping not to be something that I need to use subqueries.

It's a very useful feature that we cannot use for some time now.

Regards,

Renato Marcello

photo
1

Hi Renato,


Thanks for your message.


I've been looking at this ticket, and I believe it has been incorrectly identified as a bug. At the moment, this is expected behaviour with subqueried Reports. This is a BI feature is called Dynamic Filtering and Yellowfin does not yet support it.


You can find the Idea post here, which I can see you are already subscribed to - where I have added you to the relevant Enhancement Request.


I will keep you updated with any changes to the task on the idea post. Let me know if you have any questions in the meantime.


Kind regards,

Simon

photo
1

Hi Simon,

Just to clarify what I understand here. Are you saying that we cannot use drills when a report has a subquery because the drill will only apply to the main query as was pointed in this topic?

I've take a look under the idea you show, but it's not exactly the same case. That guy are asking to be able to use drill in the field used to join the queries. But in my case, the drill is not working for the subqueries even if we manage to enable the parameter that said it will. In other words, I just need the Drill Anywhere feature enabled for both queries, the main and the child query. The parameter says exactly the following:

"when using drill anywhere, the drill filter will be used on all basic subqueries"

It seems to me that this very parameter is not working as pointed before. Would you mind take a look?

Thanks in advance.


Regards,

Renato Marcello

photo
1

Hi Renato,


I'm unsure what build of Yellowfin you are using with reference to this question, however, there is no defective behaviour with different subqueries and Drill Anywhere functionality.


I've attached a video recording here that should help to explain.


The Report is separated into a Master query and a subquery, joined on Athlete Region (the first column). This can't be used in Drill functionality as I've already pointed out - but I've added a copy of that column to demonstrate.


In the first part of video, I test Drilling Anywhere on the Athlete Region, which filters by Region = Asia, and separates the column by Gender. Notice that the number of Athletes (620) remains the same in both sections of the query. Then, I do the same for the subquery, which filters the subquery on Demographic = Sport and Region = Asia, separating by Gender. Notice that the Master query still contains 620 Athletes for Asia.


Then I change the configuration so that subqueries are linked on Drill Anywhere. I do the same as above, however, notice that in the second part where I filter the subquery on Demographic = Sport and Region = Asia, the Master query is also filtered/ linked so that number of Athletes is 24 in both sections of the query.


Let me know if this is what you are after.


Kind regards,

Simon

photo
1

Hi Simon,

I was using 9.5 in this specific client, but I manage to update to the latest release, and I could manage to make this happen as your video explained.

Even though I could solve this item, I would like to suggest that you guys can develop the feature which will enable to use drill anywhere with the field used to join the queries as I think this can be very very very helpful.

Thanks a lot for your answer.

Regards,

Renato Marcello

photo
1

Hi Renato,


Thanks for confirming that with me. You can find the Idea post here, for the dynamix filtering functionality using Drill. I will update you there when I hear more for Developers.


Kind regards,

Simon

photo