Filter values on view level or manual dimensions input?

Joonas K shared this question 4 years ago
Answered

Hello,

I have a set of views running based on which I build valuable sales reports. What I would like to know is whether there is a feature or workaround what allows you to basically "exclude" certain dimension values from all reports at the same time? For example, say we have a view that includes "Country" as a dimension, and I would like all reports based on this view to exclude UK. Is there a way whereby I can filter out "UK" across the board, from all related reports.


Another question which is related and would achieve the same result is if there is an option to "label" values, for example label UK as "don't include" across the board? I am basically saying it would be a great feature if there was a way to have manual input labels to results. E.g. if I would like to label UK as Europe and Brazil as South America without having these values automatically imported from the backend.


Basically, what I'm after is a way to either group or filter values or label values on a level that overrides report settings.

Replies (13)

photo
1

Hi Joonas,

Thanks for reaching out to support with your question.

It sounds like you are are trying to exclude specific fields from the view level, that propagate through to the report level.

I was able to achieve this by setting a table condition in the View Builder, in this case I removed China (ISOCODE "CN") from the Athlete Country column, and this was applied to existing and new reports.


9b8cc77c7657fe915779d54302b3da9d

7e380b96727a679490d232e0f4916af2

Just to clarify, "Athlete Country" has been mapped to the Person.isocode field, as a reference code

2e32703cf4268fc840086a049049ad6b

Does this sound like a viable solution here?


As to the second part of your question - if I understand correctly, you are looking to set "alternate labels" for fields... this could be achieved by using the "group data" column (you can have a "group" of one value here) -

1fde1cab27e1cb41b1a1f462afe2742f


Does this provide possibilities to achieve what you're looking for here?

Thanks,

Eric

photo
1

Hi,

Thanks for your response. I'm not yet quite sure if this is a workaround. I basically want to just be able to exclude certain dimension values across all reports, without having to go into every report and add filters/groupings.


Part 1:

Does the above work for other dimensions as well? "Country" was just an example, but in reality I want this to apply to something else. E.g. if I have discontinued sales of bananas. apples and pineapples and would like to exclude banana, apples and pineapples from all reports that show fruit sales, the above approach would work? I still want to show grapes, plums and cherries.

Could you guide me to where in the view builder I make these changes?


Part 2:

I'm familar with the grouping part, but this only works on report level. I'd like to be able to build a report, drop for example "Fruit" (dimension) and "Revenue" (metric) in the report, and this will show me a grouped dimension containing "Other Fruit". This would basically show as:

- Other Fruit $5.21 (groups bananas, apples and pineapples)

- Grapes $3.01

- Plums $8.87

- Cherries $0.51

photo
1

Hi Joonas,

Thanks for your reply.

Part 1 -

I do believe the table conditions would allow you to hide the dimensions as you would like in this case. The table conditions function can be found in the "Model" section of the View Builder, by clicking on the gear icon of the table you'd like to modify. Additional information on the process can be found on our Wiki -

https://wiki.yellowfinbi.com/display/user80/Table+Conditions

Part 2 -

I was able to group data in the View Builder "prepare" step, this creates an additional Dimension that includes the grouped values, that you can then use in reports. For example I created a USA - Canada group here -


26fa13749c45017fd7d6a3c5f911850a1f20c5d1f22691609c9f6a9572565249

Note that if you are excluding values with a table condition, these won't be available to be grouped, as they are being "ignored." If you are trying to exclude pineapples, bananas and apples in reports, by using the table condition function in Part 1, in this case you would need to create a second view without the excluding table condition applied, in order to group them and make reports with this "other fruits" group.

Does this make sense?

Thanks,

Eric

photo
1

Hi

At first, your suggestion on groupings in the view builder seemed like a great solution. Upon actually trying to implement some changes, I got an error as per the below snippet. It appears to me that values containing the sign ' in the name are not possible to group. Not sure how to work around this. Ideas?


/a00c5024e49123ae62c547f9dd98ab44

photo
1

Hi Joonas,

Thanks for the reply, sorry to hear this is not behaving as expected when attempted.

As this is an Amazon-based error return, this may be specific to the AWS platform. It can sometimes take a little time to get access to an AWS instance on my side for testing, but I'll run some initial tests and check my resources for any information initially.

In the meantime could you confirm that this is an AWS data source you are working with?

Would you be able to look into whether you have logging on the DB side, that may be able to capture the query being sent by Yellowfin? If not, we may be able to see the SQL output by enabling DEBUG logging in Yellowfin temporarily -

https://community.yellowfinbi.com/knowledge-base/article/how-do-i-turn-on-debug-logging-within-yellowfin-log-file

Thanks,

Eric

photo
1

Hi Joonas,

Just wanted to see if you had a chance to review my response at this time?

Thanks,

Eric

photo
1

Hi. Apologies for the delay. We use amazon redshift so it would be AWS indeed.

Regarding the DB logging I would have to ask a developer to get us logging, which is not to my preference since this issue is not really a super important issue, yet slightly annoying. Maybe you can look at it on your end anyway somehow?

Have a great weekend!

photo
1

Hi Joonas,

Thanks for understanding, I will reach out to our dev team to spin up an instance for me to "play around with."

Have you been able to confirm your theory perhaps by removing the ' from the affected field? It also sounds like the results are returned as expected when the data is not grouped?

There might be a discrepancy between the query Yellowfin is sending and what Redshift is expecting... this could be due to the JDBC driver in use, or some other incompatibility... would you be able to provide a compressed copy of your application logs folder at <Yellowfin>/appserver/logs for analysis?

Thanks,

Eric

photo
1

Hi Joonas,

Just wanted to follow up here, there may be an issue in the SQL query being sent to Redshift - could you provide the SQL output of the report from the "information" tab?

Could you try running this query directly against the db to see if it works?

If it does, there may be an issue with the Redshift JDBC driver, could you provide details on the Data Source JDBC driver you are using?


Thanks,

Eric

photo
1

Hi Joonas,

Just wanted to check in to see if you were able to obtain the information I requested here?

Thanks,

Eric

photo
1

Hi,

Thanks for the call. I got some valuable input. Although not everything seems like it can be resolved right now, I learnt new useful things.

I'll stay in touch!

Kind rgds

photo
1

Hi Joonas,

Thanks for the reply here, would you like me to keep this ticket open in this case?

Thanks,

Eric

photo
1

Hi Joonas,

I'm going to go ahead and mark this ticket as Answered at this point. Feel welcome to re-open with a reply if you have any additional related inquiries.

Thanks,

Eric

Leave a Comment
 
Attach a file