Friday, August 11, 2017

FDMEE Custom Reports: Query Definition

When creating a new custom report one first needs data and the Query Definition is the starting point in the process.  As I wrote in the first post of this series the report engine for FMDEE Reports is Oracle BI Publisher.  The query engine within BI Publisher generates and XML file and that XML file is joined up with the Layout Template and Translation Template to produce the Report output.



As with most of the ERP/EPM systems on the market the database supporting the
application can be quite intimidating and the vast number of tables confusing.  Fortunately most custom reports are derivatives of existing reports and therefore using the existing Query Definitions as a starting point can be beneficial in learning the tables used and their purpose.

For this exercise I am interested in a report to list Locations and the Data Load Rules associated with that Location. 

In the Query Definition section there is a Query that looks similar to what I need.  I will copy the SQL from the 3 text boxes.  The three different boxes allow for the use of inline parameters from prompts in the WHERE clause.


















Since my FDMEE Repository is on Oracle Database I will paste the SQL into SQL Developer.  Using [Ctrl] [F7] keys I can view the SQL in a nicely formatted layout.


Since I like to work in a Graphical view of my queries I will switch to the Query Builder view. This allows me to see the tables and joins easier.



The following image show some of the more frequently used tables within the FDMEE database/schema and what information they contain.


So now that I have a better understanding of the tables.

The report I want to produce is Data Load Rules per location and since the Data Load Rules are tied to Category I would like to know that as well.

Since my report only needs 3 columns I will create a new query in SQL Developer and drag the AIF_BALANCE_RULES, TPOVPARTITION and TPOVCATEGORY tables into the Query Builder and Join them as shown below.  Then I will select the columns I am interested in from the tables and finally I will set the sort order.  One tip with Reports it is faster to sort the data on query rather than within the report layout.



I will need to switch back to the Worksheet view in order to collect the SQL for my query.


In FDMEE I will create a new query definition named Location Data Load Rules and paste into the 2 text areas; the SELECT statement up to the ORDER BY into the Select Clause and paste the ORDER BY statement into the Group By/Order By Clause.



Save the Definition and then click the Validate Query to make sure that FDMEE does not have any issues with the query.




In order to work with the BIPublisher MSWord Template Builder I will need a sample data file. The Query Definition interface has a Generate XML button. Clicking this button allows me to create the sample XML data file. Typically it will return 25 rows of data.



When I open the file I can see the data structure of the query. In the case the result of my simple query there is the Location name, the Category name and the Data Load Rule name for each row of data. 



Now that have a sample data set I can create the layout for my report. But that is the topic for another post.

62 comments:

  1. There are few business credit building companies out there, however, those that are out there are taking advantage of the lack of knowledge from the general public regarding business credit and how to get a business loan. DON'T LET THEM RIP YOU OFF! https://cad.cheapsoftwaredownload.net/

    ReplyDelete
  2. The job of a Search Engine Optimizer, popularly referred to as an SEO helps the visitors to acquire the required information from the visited website. So, an SEO plays a major role as far as research and development are concerned. Both, the clients availing of the services of an SEO as well as the visitors to the website, many a times, prove to be too demanding for the Search Engine Optimizer. Well no pains, no gains. An SEO's job seems to be based on this pattern rather too strictly. link building company

    ReplyDelete
  3. The job of a Search Engine Optimizer, popularly referred to as an SEO helps the visitors to acquire the required information from the visited website. So, an SEO plays a major role as far as research and development are concerned. Both, the clients availing of the services of an SEO as well as the visitors to the website, many a times, prove to be too demanding for the Search Engine Optimizer. Well no pains, no gains. An SEO's job seems to be based on this pattern rather too strictly. forum backlinks

    ReplyDelete
  4. The job of a Search Engine Optimizer, popularly referred to as an SEO helps the visitors to acquire the required information from the visited website. So, an SEO plays a major role as far as research and development are concerned. Both, the clients availing of the services of an SEO as well as the visitors to the website, many a times, prove to be too demanding for the Search Engine Optimizer. Well no pains, no gains. An SEO's job seems to be based on this pattern rather too strictly. blog comments seo

    ReplyDelete
  5. I am glad to be one of many visitors on this outstanding web site (:, thankyou for putting up. 먹튀스토어

    ReplyDelete
  6. not everyone would need a nose job but my girlfriend really needs some rhinoplasty coz her nose is kind of crooked; 먹튀사이트

    ReplyDelete
  7. Hey there! Nice post! Please inform us when we will see a follow up! 먹튀검증업체

    ReplyDelete
  8. I’m not sure exactly how I found this blog because I had been researching information on Real Estate inWinter Springs, FL, but anyway, I have had a pleasant time reading it, keep it up! 먹튀사이트

    ReplyDelete
  9. - yo , Thank you a lot for making this website . I m into gambling niche and have found this website using search on google . Will be sure to share . I am affiliate and have found your site very informational Many thanks , see ya. :S 먹튀검증업체

    ReplyDelete
  10. Are you looking information on how to fund a home based business? Here is what you need to know. From risky business to power couple partnership, discover why couples everywhere are learning the secrets to running a successful business together while deepening their relationship. Find out why couple entrepreneurs are building the lifestyle of their dreams and protecting their assets and how you can too, by starting a business, growing your relationship and learning to achieve success together. quitmasturbating

    ReplyDelete
  11. It is in point of fact a nice and useful piece of information. I am glad that you just shared this useful info with us. Please keep us up to date like this. Thank you for sharing. 우리카지노계열

    ReplyDelete
  12. hello SEO guys, I love all your prasing comments, but can we discuss SEO too? 토토사이트

    ReplyDelete
  13. hello SEO guys, I love all your prasing comments, but can we discuss SEO too? Mega888 android and ios

    ReplyDelete
  14. Hello. splendid job. I did not imagine this. This is a impressive articles. Thanks! 먹튀

    ReplyDelete
  15. I’d need to consult you here. Which isn’t something I do! I quite like reading a post that could make people think. Also, thanks for allowing me to comment!
    918kiss xe88 apk download

    ReplyDelete
  16. i am using infolinks and chitika and i think infolinks have the best payout ,. 918kiss kiss918 apk download

    ReplyDelete
  17. i am using infolinks and chitika and i think infolinks have the best payout ,. 토토사이트

    ReplyDelete
  18. i love bob dylan, he is one of the best singer songwriter` 파워볼사이트

    ReplyDelete
  19. Thanks for the tips guys. They were all great. I have been having issues with being fat both mentally and physically. Thanks to you guys i have been showing improvements. Do post more 먹튀검증

    ReplyDelete
  20. This is an excellent post I seen thanks to share it. It is really what I wanted to see hope in future you will continue for sharing such a excellent post. 토토사이트

    ReplyDelete
  21. You ought to basically fantastic not to mention solid advice, which means notice 사설토토

    ReplyDelete
  22. Surprising article. Hypnotizing to analyze. I genuinely love to analyze such a good article. Much regarded! keep shaking.  토토사이트

    ReplyDelete
  23. Thank you for helping people get the information they need. Great stuff as usual. Keep up the great work!! 사설토토

    ReplyDelete
  24. I can’t believe focusing long enough to research; much less write this kind of article. You’ve outdone yourself with this material without a doubt. It is one of the greatest contents 먹튀검증커뮤니티

    ReplyDelete
  25. i was just browsing along and came upon your blog. just wanted to say good blog and this article really helped me 사설토토

    ReplyDelete
  26. Thanks for providing recent updates regarding the concern, I look forward to read more. 메이저놀이터

    ReplyDelete
  27. useful information on topics that plenty are interested on for this wonderful post.Admiring the time and effort you put into your b!.. 메이저놀이터

    ReplyDelete
  28. Very interesting  information!Perfect just what I was looking  for! 안전놀이터추천

    ReplyDelete
  29. Thank you for sharing such a good and useful information here in the blog for everywhere. 먹튀검증

    ReplyDelete
  30. Excellent information providing by your Article thank you for taking the time to share with us such a nice article 사설스포츠토토

    ReplyDelete
  31. I  have read your article; it is very informative and helpful for me. I admire the valuable information you offer in your articles. Thanks for posting it.   사설토토사이트

    ReplyDelete
  32. Your way of explaining all in this post is actually fastidious, every one can simply understand it, Thanks a lot. 먹튀검증

    ReplyDelete
  33. I am happy to find this post Very useful for me, as it contains lot of information. I Always prefer to read The Quality and glad I found this thing in you post. Thanks 안전놀이터

    ReplyDelete
  34. This is actually the kind of information I have been trying to find. Thank you for writing this information 바카라사이트

    ReplyDelete
  35. "This is so good. Thank you for the post.

    " 슈어맨

    ReplyDelete
  36. Great delivery. Sound arguments. Keep up the good work. 먹튀폴리스

    ReplyDelete
  37. Thank you for your post, I look for such article along time, today i find it finally. this post give me lots of advise it is very useful for me shipping from china

    ReplyDelete
  38. That is the excellent mindset, nonetheless is just not help to make every sence whatsoever preaching about that mather. Virtually any method many thanks in addition to i had endeavor to promote your own article in to delicius nevertheless it is apparently a dilemma using your information sites can you please recheck the idea. thanks once more courtier immobilier hull

    ReplyDelete
  39. Good focuses you composed here..Great stuff...I think you've made some really fascinating points.Keep up the great work. 이기자벳 먹튀

    ReplyDelete
  40. This comment has been removed by the author.

    ReplyDelete
  41. Thank you for helping people get the information they need. Great stuff as usual. Keep up the great work!!! 메이저사이트

    ReplyDelete
  42. This is an interesting post that I'll like to share with you 파워볼사이트

    ReplyDelete
  43. Regular visits listed here are the easiest method to appreciate your energy, which is why why I am going to the website everyday, searching for new, interesting info. Many, thank you . This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free. Impressive web site, Distinguished feedback that I can tackle. Im moving forward and may apply to my current job as a pet sitter, which is very enjoyable, but I need to additional expand. Regards. This web journal is truly awesome. The data here will definitely be of some help to me. Much appreciated!. I hope I can see more posts. I want to read your articles. Because they are very useful, I always wait for new posts. They are my cup of tea. Thank you for your contribution to the community. 먹튀검증

    ReplyDelete
  44. hello, i am so thrilled i found your blog, i am here now and will much like to mention thank for a high-quality submit and all spherical thrilling internet site. Please do preserve up the super work. I can not 토토사이트

    ReplyDelete
  45. the put up is clearly incredible. It’s various accent data that is composed at some point of a fundamental and vital approach. Thank you for sharing this article. The substance is virtually composed. This web do my paper for me log is often sharing useful actualities. Hold sharing a number of posts. I truly cherished studying your blog. It was thoroughly authored and clean to apprehend. In contrast to other blogs i've read which can be honestly now not that proper. Thank you lots! Wow, exceptional, i used to be wondering the way to treatment zits obviously. And observed your site with the aid of google, discovered plenty, now i’m a piece clear. I’ve bookmark your website online and additionally add rss. Hold us updated. 토토사이트

    ReplyDelete
  46. the next time i examine a blog, i'm hoping that it doesnt disappoint me as tons as this one. I suggest, i understand it became my desire to study, however i really thought youd have some thing thrilling to say. All i listen is a group of whining approximately some thing that you may restoration if you werent too busy searching out attention. Hello, i think your site is probably having browser compatibility troubles. Once i study your internet site in safari, it appears pleasant but when starting in net explorer, it has some overlapping. I just wanted to present you a short heads up! Different then that, exceptional weblog! 에볼루션바카라 승인전화없는 토토꽁머니

    ReplyDelete
  47. high-quality beat ! I would love to apprentice whilst you amend your internet web page, how can i subscribe for a blog website? The account aided me a appropriate deal. I had been tiny bit acquainted of this your broadcast furnished shiny clean concept. I discovered your weblog the usage of msn. This is an exceptionally properly written article. I’ll make certain to bookmark it and come returned to study extra of your beneficial information. Thanks for the submit. I can honestly comeback. Very high-quality put up and immediately to the factor. I am now not positive if this is truly the exceptional area to ask however do you human beings have any ideea in which to rent some professional writers? Thank you �� 토토경비대

    ReplyDelete
  48. it’s the exceptional time to make some plans for the destiny and it is time to be glad. I've examine this post and if i could i choice to suggest you few interesting things or recommendation. Perhaps you may write subsequent articles regarding this article. I preference to read greater matters approximately it!. Quite phase of content material. I simply stumbled upon your blog and in accession capital to say that i get simply loved account your weblog posts. Any manner i will be subscribing for your feeds and even i success you get entry to consistently speedy. I’m without a doubt impressed along with your writing talents as well as with the layout to your blog. Is that this a paid topic or did you regulate it your self? Both way keep up the high-quality quality writing, it’s rare to see a notable weblog like this one these days.. 헤이먹튀

    ReplyDelete
  49. youre so cool! I dont think ive read anything which includes this prior to. So first-class to discover anyone with many original ideas on this problem. Realy thank you for starting this up. This fantastic website may be some thing this is required on the net, somebody with a little originality. Valuable problem for bringing something absolutely new on the net . Quality with your permission permit me to seize your feed to preserve updated with imminent post. Thanks a million and please keep up the pleasurable paintings 먹튀대피소

    ReplyDelete
  50. Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon. Excellent website! I adore how it is easy on my eyes it is. I am questioning how I might be notified whenever a new post has been made. Looking for more new updates. Have a great day! Thank you for giving me useful information. Please keep posting good information in the future I will visit you often . 카이소

    ReplyDelete
  51. This was really an interesting topic and I kinda agree with what you have mentioned here!Hello, I have browsed most of your posts. This post is probably where I got the most useful information for my research. Thanks for posting, maybe we can see more on this. Are you aware of any other websites on this subject . Really impressed! Everything is very open and very clear clarification of issues. It contains truly facts. Your website is very valuable . 카지노

    ReplyDelete
  52. After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article. The next time I read a blog, I hope that it doesnt disappoint me as much as this one. I mean, I know it was my choice to read, but I actually thought you have something interesting to say. All I hear is a bunch of whining about something that you could fix if you werent too busy looking for attention. I read a article under the same title some time ago, but this articles quality is much, much better. How you do this.. 카지노

    ReplyDelete
  53. I am definitely enjoying your website. You definitely have some great insight and great stories. Your article has piqued a lot of positive interest. I can see why since you have done such a good job of making it interesting. I am incapable of reading articles online very often, but I’m happy I did today. It is very well written, and your points are well-expressed. I request you warmly, please, don’t ever stop writing. Nice post. I was checking constantly this blog and I’m impressed! Extremely useful info specially the last part I care for such information a lot. I was seeking this certain info for a long time. Thank you and good luck. 헤이먹튀

    ReplyDelete
  54. What I don't comprehended is very you're currently not actually substantially more all around preferred than you might be presently. You're extremely shrewd. You understand along these lines significantly with regards to this subject, created me as far as it matters for me envision it from such countless different points. Its like people are not included except if it is one thing to do with Girl crazy! Your individual stuffs extraordinary. Continuously handle it up! You are very much appreciated than you may be at this moment. 먹튀신고

    ReplyDelete
  55. What I don't comprehended is very you're currently not actually substantially more all around preferred than you might be presently. You're extremely shrewd. You understand along these lines significantly with regards to this subject, created me as far as it matters for me envision it from such countless different points. Its like people are not included except if it is one thing to do with Girl crazy! Your individual stuffs extraordinary. Continuously handle it up! You are very much appreciated than you may be at this moment. 해피머니상

    ReplyDelete
  56. Way cool! Some extremely valid points! I appreciate you penning this post and also the rest of the site is also very good. Wow! This can be one particular of the most beneficial blogs We have ever arrive across on this subject. Actually Great. I am also a specialist in this topic so I can understand your effort. It’s really a nice and useful piece of info. I’m glad that you shared this helpful info with us. Please keep us up to date like this. Thank you for sharing. 카디즈에이전시

    ReplyDelete
  57. I can see that you are an expert at your field! I am launching a website soon, and your information will be very useful for me.. Thanks for all your help and wishing you all the success in your business. Thank you again for all the knowledge you distribute,Good post. I was very interested in the article, it's quite inspiring I should admit. I like visiting you site since I always come across interesting articles like this one.Great Job, I greatly appreciate that . I am appreciative of your assistance and look forward to your continuing to work on our account. I really appreciate the kind of topics you post here. 카지노마트

    ReplyDelete
  58. One of the moreimpressive blogs I’ve seen. Thanks so much for keeping the internet classy for a change. You’ve got class. I mean it. Please keep it up because without thenet is definitely lacking in intelligence. thc carts bulk

    ReplyDelete
  59. Copyright protection is afforded to books, movies, music, photographs, and a wide variety of other creative expressions of an idea -- but did you know that vessel hull designs and architectural works are also eligible for copyright protection? This article touches on the history of copyright law to explain how these seeming outliers to intellectual property have been granted the same copyright protections as any other creative work. Vintage Newborn Props

    ReplyDelete
  60. Excellent read, I just passed this onto a colleague who was doing a little study on that. And he actually bought me lunch because I found it for him smile So let me rephrase that: That are unquestionably superb. The majority of tiny specs are fashioned owning lot of track record competence. I'm just seeking to it again quite a lot. I found this is an informative and interesting post so i think so it is very useful and knowledgeable. I would like to thank you for the efforts you have made in writing this article. 안전놀이터

    ReplyDelete
  61. All About 원샷홀덤 Program Had Helped Many Peoples To Earn Money

    ReplyDelete