Access Filter doesn't work

Hi Janus,

I hope you're doing well and thank you for submitting your query to Yellowfin support.

I have heard of this happening before, where the SQL query seemingly returns values during validation but the resulting filter doesn't contain any. Allow me to just do a bit of research into this and I'll get back to you shortly with some suggestions.

Kind regards,

Chris

Hi Janus,

So it can depend on how the query is formed and Yellowfin can be a little fussy as to the exact formatting.

What's the underlying database this is running against? If the ` characters are not required, can you try without them. Is this all from the same table? If this is all from the same table, you don't need to reference table names in the query. I see that you have Department AS referenceType but also People.Department AS ReferenceID, this seems to refer to the same field?

I do see that `People.Login ID` has a space character. We generally recommend removing all spaces from field names to make things easier.

Also the last line can be simplified to just People.

So it might look something like:

SELECT
'BENUTZERID' AS identifierType,
'Login ID' AS identifier,
'Department' AS referenceType,
'People.Department' AS referenceID (this may need to be changed here)
FROM People

Feel free to add back the ` characters if they're required by your database.

Kind regards,

Chris

Hey Chris,

Thanks in advance for the support.

We using Orcale with BMC Remdy and the Database behind that, but I think there is no Problem with that, because we get the Preview Data in Yellowfin.

The ` is required, because if I don't use it, the system gives me an error and doesn't recognize the column in my database.

I removed the reference table, because as you said there are not needed and I get the same output.

ReferenceType is set for Department, because for equality with the referenceID, i can change it to something else and add refrenceType depart or something like that. The reference ID is the column for department. Is it not allowed that they have the same name? I think the Problem is more specific for the User that YF don't recognize.

The spaces are also required, because of the Column name in the Database and you also get the output.

This is the Query I tryed again (Unfortuntaly no records/users):

SELECT
'BENUTZERID' AS identifierType,
`Login ID` AS identifier,
'Department' AS referenceType,
`Department` AS refrenceID
FROM `People`

My Second Try was, because of the example from yellowfin:

SELECT
'YFPERSONID' AS identifierType,
'Login ID` AS identifier,
'Department' AS referenceType,
`Department` AS refrenceID
FROM `People`

The YFPERSONID or BENUTZERID is like b0123456

Hi Janus,

Is it possible for you to give me a screenshot including the set of column names from the table and one row of dummy data?

By the way, 'refrenceID' is incorrectly spelled in your query. It should be 'referenceID'

You also need to make sure that the referenceType exists and has been created prior to running your SQL query. Otherwise the query may validate but no records will be returned if none of them match the referenceType. In my example, I'm using the columns and data as follows:

The table's data looks like:

and in Yellowfin I have created a referenceType called 'Test'.

The query in Yellowfin looks like this:

Finally, it validates:

And I can then hit submit and view the records:

I hope this makes things a little clearer. If you're still having problems, I'll probably need to take a look at your column headings for the table in the database to make sure it's being formed properly. As I mentioned, you need to specify the referenceType beforehand.

Kind regards,

Chris

Hey Chris,
I have to thank you for your screenshots, I tryed some things and I solved the Problem:

I had to Change BENUTZERID to USERID. It just translate the word from german to english, but Yellowfin needs the english word to identifie the identifierType.

Also I had to change the refrenceType to referenceType. Now it is spelled correctly.

Fortuntately it works now with this SQL Query:

SELECT
'USERID' AS identifierType,
`Login ID` AS identifier,
'Department' AS referenceType,
`Department` AS referenceID
FROM `People`

Thanks a lot, if I have other questions I open another ticket.

Best regards
Janus

Hi Janus,

No problem at all. I'm glad you were able to get it fixed!

I hope you have a great week ahead!

Kind regards,

Chris