Getting "SQL Statement" from "Report SQL"

Hi,

there is no webservice or script to get the report SQL, Yellowfin doesn't store the SQL text in the config database, instead it is generated on the fly by the java code.

Maybe if you tell us what are you trying to accomplish, we can help in another way.

regards,

David

Actually I think I may have come up with a round-about way of achieving what you want. It is based on the fact that Yellowfin stores the report SQL in the ReportInstance table after it has been run.

Use a webservice to run the report

Then use another webservice to query the ReportInstance table.

If you think this might be what you're looking for then please let me know and I'll give you more details.

regards,

David

Hi,

Thank for the reply.

This is what i found. We can get the report SQL via belowscript.

select top 1 ReportGMTDateTime, i.SQLText from ReportHeader h

join ReportInstance ion h.ReportId=i.ReportId

where ReportName= XXXX

order by ReportGMTDateTime desc

What we trying to achieve is to fetch the report data into another database for further process.

That how we fetch the data current

1) user setup the report

2) we fetch the report data via webservice called

But there are some issue with that approached

1) some text data had this "," inside and hence we got read error

2) some of the script are take long to read the data from database and hence we got timeout and cause some performance issue in yellowfin

So, if we able to get the "Report SQL" then we can directly execute it against DB server and hence less performance issue. But the only draw back is once you change the yellowfin schema or logic then we will had to figure out again where to get "Report SQL"

Thx

Hi Yae,

yes, that's sort of the same idea I had. If it's any help to your project, there is a web service function that queries the YF DB directly, I have attached an example of how to use it (don't forget to remove the .TXT extension).

Also don't forget to add the 2 params to your web.xml that are listed at the comments at the top of the JSP.

Regarding the 2 issues you had with how you are currently fetching the data, I'm wondering if we can work around those issues anyway:

1) I'm not quite sure about the issue with the "," in the text data, could you please give an example of it.

2) If the timeout is occurring with the data source connection then you can increase the data source timeout, or if it's occurring with the Yellowfin config DB then you can increase that timeout by adding in a JDBCTimeout parameter in your web.xml file as described in this old forum post.

Regarding your concern about the schema or logic change, let me reassure you that the schema hasn't changed for those 3 columns you're using from ReportHeader and ReportInstance since YF was invented, it's very stable and I think it highly unlikely it will change in the future, and I also can't see the logic changing either.

regards,

David

Hi David,

1) I'm not quite sure about the issue with the "," in the text data, could you please give an example of it.

[yc] one of my colleague is already reported and it's already in Yellowfin CommunityIdea (1906)

2) If the timeout is occurring with the data source connection then you can increase the data source timeout, or if it's occurring with the Yellowfin config DB then you can increase that timeout by adding in a JDBCTimeout parameter in your web.xml file as described in this old forum post.

[yc] noted on that

ps. thank for assuring the schema nor logic won't change in the near future

regards,

Yae Chan

Hi Yae,

I have read Idea 1906 and now I understand the problem, yes unfortunately there is nothing we can do about that at the moment. So I can see that you will have to go ahead with your project to get around this problem.

Good luck! Please let us know how it goes.

regards,

David