Error: Report has related reports linked by filters and the referenced filters could not be found

Rutger Tjallema shared this problem 2 years ago
Resolved

I exported a dashboard tab with many reports from our acc environment and try to import it in our prod environment.

Unfortunately, for one report (lets say Report A), I get a error during the import: 'Report has related reports linked by filters and the referenced filters could not be found.' I have several reports drilling through to the same child-report (Lets sat Report B and C). Report B and C do not have the error. After importing, they are working as expected.

But, and now it gets weird, when I remove the drill through functionality from Report A, make a new export, import it and I end up with the same error, but for Report B or C. Even though before they did not have an error and worked correctly.

What is causing this problem?

Attached you find two export xml-files. Import 6 gave an error for report Lead time to quote (working days) - one supplier, Import 7 for Lead time to quote - compare suppliers (new)

Comments (8)

photo
1

Hi Rutger,

it could be a bug, and there is only really one way we can investigate import/export bugs. Our developers will need to step through the code over here and see what's happening during the import.

So, could you please upload a full backup of your Yellowfin database to our FTP website (I have just made an account for you - you will receive an email containing your details, if you don't get it please also check your spam folder) and also we will need just the schema of your data source (no data, just the empty tables and columns).

Please let me know when you have uploaded them.


Also, I would say it is worth upgrading your 7.3 to the latest build because your current instance is 9 months old which means it is missing a lot of code-fixes, and you never know, if the upgrade fixes this problem then you won't have to go through the process of uploading the database dumps.


regards,

David

photo
1

Requested files are uploaded

photo
photo
1

Hi Rutger,

just letting you know that I have successfully downloaded and restored your 2 Yellowfin MySQL databases and your PostgreSQL datamart DDL, so all that remains is for me to replicate your issue and then raise a defect, although I will have to do this tomorrow because I've just run out of time.

regards,

David

photo
1

Hi Rutger,looks like there are 2 tables missing (sales_country_current, proc_seller_current) for the report "Price access products - one supplier


SELECT DISTINCT
   "supplier_relations_products"."reference_date",
   "supplier_relations_products"."speed",
   "supplier_relations_products"."term",
   AVG("supplier_relations_products"."mrc")
FROM "datamart"."sales_country_current"
LEFT OUTER JOIN "datamart"."supplier_relations_products"
ON (
   "sales_country_current"."country_id" = "supplier_relations_products"."country_id"
)
LEFT OUTER JOIN "datamart"."proc_seller_current"
ON (
   "supplier_relations_products"."seller_id" = "proc_seller_current"."seller_id"
)
WHERE (
   "supplier_relations_products"."speed" IS NOT NULL
   AND "supplier_relations_products"."term" = 12
   AND "proc_seller_current"."name" = E' - - select supplier - -'
)
GROUP BY 
   "supplier_relations_products"."speed",
   "supplier_relations_products"."reference_date",
   "supplier_relations_products"."term"


Are you able to send across a script so that I can add them to the datamart?

regards,

David

photo
1

Hi David,

sorry, i missed them. See the attached file!

Cheers!

photo
photo
1

Hi Rutger,

thanks for the new script. I ran it but it didn't resolve the issue with the report "Price access products - one supplier". Then I noticed that the schema in the script was called "ods" and didn't match with the schema of your other script, so I took the liberty of changing it to "datamart" but that still didn't resolve the issue. Then I noticed that the table names in the script were "proc_seller" and "sales_country" whereas that report is looking for "proc_seller_current" and "sales_country_current", so once again I took the liberty of changing the script by adding "_current" to those 2 table names.

But unfortunately that still didn't resolve the issue, the report still wouldn't run. Then I noticed in the console the following error:


YF:2017-10-23 10:55:42:ERROR (DBAction:doSelect) - Error occured selecting data:
 org.postgresql.util.PSQLException: ERROR: operator does not exist: bigint = cha
racter varying
  Hint: No operator matches the given name and argument type(s). You might need
to add explicit type casts.


and after further investigation worked out that it was referring to the join:


"supplier_relations_products"."seller_id" = "proc_seller_current"."seller_id"

because in the "proc_seller_current" table the "seller_id" is of type varchar:


/1FNgj+dAcLEAAAAAElFTkSuQmCCAA==


whereas in the "supplier_relations_products" table the "seller_id" is bigInt:


/AuwAAAABJRU5ErkJgggA=


So I want to change the datatype of one of the above "seller_id" columns but do not want to do the wrong one, so could you please confirm whether it should be varchar or bigInt? Also, could you please confirm if the other modifications I did to your script were correct (changing the schema and table names), because ultimately I want to have my environment over here the same as yours.


thanks,

David

photo
1

Hi David,


Thank you for all the effort so far. I see the setup of our DB is not fully clear to you. I should have given some explanation, sorry for not doing that.


The two tables datamart.proc_seller_current and datamart.sales_country_current are database view, based on the ODS tables of which I send you the DDLs. Attached you find the DDLs to create these views. I hope with this you can replicate our setup.


About the format type of the column seller_id: the ODS table is leading. Best is to change the type of seller_id of the table supplier_relations_products to varchar.


Thanks, Rutger

photo
photo
1

Hi Rutger,

thanks for the script, now all reports on the dashboard tab "Supplier Relations (New)" are executing without error.

But unfortunately during the process of trying to import the export file some more missing tables have come to light:


/wEsY7rJGsGCtQAAAABJRU5ErkJgggA=


- So if you could send across a DDL script for those 2 tables (datamart.proc_api_portal_buyer & datamart.suppliers_portal_account_activated) then that would be great!

I feel we are getting very close now.


- Also, just to make sure I do the same steps as you, could you please confirm the following:

1) export the dashboard tab "Supplier Relations (New)" from the ACCP environment, making sure to include all dependencies excepting the data source.

2) import the export file created in step 1) into the PROD environment making sure to use the REPLACE option for all content items.


- And one final thing: please tell me which version and build of Yellowfin is in your PROD environment (I know ACCP is 7.3 20170103).


regards,

David

photo
1

Hi David,

I forgot about those two (the related reports). Sorry for that. See attached file for the DDLs.

I can confirm that the export/import process is as you describe.

For PROD we have the same version and build:

System InformationApplication Version:7.3Build:20170103

Thanks again!

photo
1

Hi Rutger,

thanks for the script, and this time everything is complete - my environment is correct!

But the thing is, firstly I exported the dashboard tab "Supplier Relations (New)" and all dependencies from ACCP and then imported it into PROD and there were no problems at all. Then I thought that was strange, so I'd better try the Yellowfin Export file ("Supplier Relations NEW 20171016_6.xml") that you originally attached here, then during the course of the import process I became aware that there were 5 extra reports that weren't in my ACCP environment, they were:

SQL details - In bundle

SQL details - In order

SQL details - Pending

SQL details - stage check

SQL details - stage negotiated


So I guessed that's why my first replication attempt was successful - somehow my ACCP env was different than yours and thus my own export file was different than yours. So anyway, I kept on going through the import process with your export file and then I came to the screen you were talking about:

/l8CmQVTS4FtTPdCmqnLhi38jxvt9u7urpwHIcZYLFYTEGtosmloHamW0HtVYnjuFAoBLO7CIKAjWZIkrwhO1f0E5aC2pluBbVXJW6sDwv8W37whuxc0U9YCmpnuhXUXpUS1r4UyCqaohTUznQrqL0WKahVNEUpqJ3pVlCrSNEN0P8H4gxmCA5S5RgAAAAASUVORK5CYIIA


and then I continued with the import process and it went successfully. Then I tried to run that report "Lead time to quote (working days) - one supplier" and it ran successfully.


So what I can tell you is that the message you saw during the import process, "Report has related reports linked by filters and the referenced filters could not be found." is not in fact an error, it is only a warning. And also, the warning itself is actually a bug because there was nothing wrong with the import process. And in fact, you won't be aware but in recent months the whole Yellowfin Export/Import functionality has been totally revamped and bugs like that have been ironed out.


In summary I would say to just ignore that warning message on the Import Summary screen, and whenever it is possible, you should upgrade to the latest 7.3 and you will see a whole new Import/Export functionality.


regards,

David

photo
1

Hi David,

It is correct that you did not have the errors with the export you made. I removed the drill-through reports with the errors to be able to import the dashboard in PROD. After that, I made the copy of our database that i sent you.

So, based in your findings, I added the drill-throughs again in ACCP and made a new export and imported it again in PROD. I expected to have again the error at the end of the import process and planned to ignore it, as you recommended. But... I had no errors. Somehow, the bug disappeared.

So, I'm happy to inform you that i successfully imported the dashboard in our PROD environment! Later this month I will also upgrade to the latest build.

Thanks for all the help David!

Regards, Rutger

photo
photo
1

Hi Rutger,

that's great news, thanks for letting me know!

And in the future, should you stumble across any other bug, then we already have your environment set up over here so it should be quick and easy to replicate the issue.

thanks,

David