How to get filter criteria from Yellowfin DB?

Steven Sun shared this question 5 months ago
Answered

Hi Team,

I'm trying to collect all filter criteria (master query, minus, intersect etc.) and calculated fields setting for all existing segmentation files from the system.

It's tedious to open each file and copy the setting one by one,

Are there any tables from DB recording what I need?

Please kindly guide me.


Thanks and regards

Best Answer
photo

Hey Steve,

Thanks for reaching out with this.

I have unfortunately been unable to locate a method to collect ALL of the filter criteria.

We in support have queries to find all filter used in reports:

SELECT ReportFilter.ColumnOperator, ReportFilter.WhereClauseOperator, ReportHeader.ReportName, ReportHeader.ReportStatusCode
FROM ReportHeader
LEFT JOIN ReportFilter ON ReportHeader.ReportId = ReportFilter.ReportId AND ReportHeader.ReportStatusCode='OPEN'​
And a query to List Custom Query Filter value by report name:

S​ELECT rh.ReportId, rh.ReportName, rh.ReportDescription, td.DataChunk
FROM ReportHeader rh
JOIN CachedFilter cf ON rh.ReportId=cf.ReportId
JOIN TextData td ON cf.TextId=td.TextId
WHERE rh.ReportName = 'CustomQuery'​
I've reached out to see if we can construct a query to pull the information you are looking for an will update you with what I am able to find!

Best,

Jared

Comments (6)

photo
1

Hey Steve,

Thanks for reaching out with this.

I have unfortunately been unable to locate a method to collect ALL of the filter criteria.

We in support have queries to find all filter used in reports:

SELECT ReportFilter.ColumnOperator, ReportFilter.WhereClauseOperator, ReportHeader.ReportName, ReportHeader.ReportStatusCode
FROM ReportHeader
LEFT JOIN ReportFilter ON ReportHeader.ReportId = ReportFilter.ReportId AND ReportHeader.ReportStatusCode='OPEN'​
And a query to List Custom Query Filter value by report name:

S​ELECT rh.ReportId, rh.ReportName, rh.ReportDescription, td.DataChunk
FROM ReportHeader rh
JOIN CachedFilter cf ON rh.ReportId=cf.ReportId
JOIN TextData td ON cf.TextId=td.TextId
WHERE rh.ReportName = 'CustomQuery'​
I've reached out to see if we can construct a query to pull the information you are looking for an will update you with what I am able to find!

Best,

Jared

photo
1

Hi Steven,


Unfortunately there is no real way to get this without also creating some tedious SQL, which would also require quite a lot of effort, even then, you will still need to update most of the options from the UI, so don't think it's really going to help you with not having to manually update each item.


To better help us understand what you're trying to achieve, can you give us an example of what you're trying to do, why, and the number of reports you would need to update (and frequency) ?


Thanks,

David

photo
1

Hi Jared, David,

Sorry for the delay and thank you for your both advices!

I think the first SQL from Jared is already very helpful, even after I've completed the tedious work to copy and paste all filter value from UI.

Much appreciate it!

photo
1

Glad we were to provide our advice here.

As david said there isn't necessarily a path to mitigate the tedium in getting the filter criteria from the YF DB. If you are happy with the advice here we can go ahead and mark this ticket as answered for now.

Let us know if you have any other questions regarding this!

Best,

Jared

photo
1

Hi Jared,

Thank you for the support? Let's close this ticket.

photo
1

Hey Steve,

I'll go ahead and close this out. Please feel free to reach out to us regarding any other questions or issues!

Best,

Jared