Sometimes shit happens. One receives a work request that seems idiotically simple but the tools provided by the system makes it a living hell.
Something like that happened me these days, because the way someone planned the queries system in SAP.
Because that, I decided to write my troubles and their solutions here, to keep track of them and to have a place to go where I’ll need to manage queries again.
Previous note: I’m writing this post thinking about my experiences with a 4.6 system. I’m not sure if 4.7 queries are better or if ERP 5+ are the heaven in comparison…
I.1. What’s a query?
A query (in SAP) is an “easy” way to extract data from a table or a join of tables and make simple reports using basic tools without the need of SQL knowledge. Sometimes people wants not to hire an ABAPer for easy tasks, and then SAP queries join the game (pun intended).
I.2. Query, InfoSet, QuickView. Weird summary (more info here)
A query is a simple (or not too simple) data selection. You can create a query through transaction SQ01. I’m not sure if the transaction itself does the authority checks for each table used. We must assume it does, because the other option is to think someone is a dumbass in Walldorf.
An InfoSet is a tool for make simpler queries. It does the same a query do, but you can cut the fields’ list. Once you’ve made an InfoSet you can create queries from it.
A QuickView wants to be “the easy made easier”. It’s a graphical query maker. Sadly, its query is only visible for the user who made it. Luckily, once your QuickView has been made, you can create an equivalent Query (with its InfoSet).
I.3. When someone asks you to create a QuickView
Don’t do it. Just and plain. No.
Ok, do it, but it will be useless, because the user will not be able to use it. You will need to transform/copy it into a query. Why not to do it as a query directly? Because QuickView is easier? I doubt it, and anyway, the work you’ll need to do after make the QuickView maybe will not worth the effort.
I.4. But hey! You’ve made a QuickView. And now?
And now you can fry some potatos and eat it. It’s user-only visible, and worse than that, you cannot transport it. If you are lucky/smart, you’ve made it in the production system. If you are unlucky, or your Basis team cuts your rights in production, you’ll need to transport/copy it.
And there are no tools to copy a QuickView, nor through clients nor through servers (or I did not find them).
Ok, let’s try to fix the mistake.
First: go to SQ01, use the menu: “Query -> Convert QuickView…”
But be sure you are in the global functional area!
Ok, you’ve decided to go “hardcore” and you will make a query. Let’s go to transaction SQ01.
II.5. Queries: functional areas
The first mistake I made (and almost a deadly one): once you reach SQ01, you are “located” in a “local” query area. What does it mean? It means anything you’ll make will NOT be visible in other clients nor transportable to other systems. For Pete’s Sake! Why? No clue, somebody in Walldorf decided by me.
Let’s use our friend the menu again: “Environment -> Query areas”. You will see a small window offering you some options. Try to select (double click) “Global area (cross-client)”. Worked? Cool, you can start to work now. I will advice anyone to NOT work in the “Standard area (client-specific)” although the query will never must be transported. You’ll never know the evilness of the user requirements, and move your work from the standard area to the global one could be a pain in the ass.
But you are lucky again, because you have the poor Vic who has fought your fight before and provides you the next point
II.6. Copying items through functional areas
You’ve made your choice and get the wrong option, or no one talked you about functional areas, or you thought SAP would make easy things really easy. You’ve created your query in the standard area and you need it transported, or visible in other client. What to do now?
Obvious: go to the InfoSet screen. Obvious my butt! But I’m not here to flame but to help you, my fellow idiot. Menu “Environment -> InfoSets”
You are in a new cool pretty screen where you see some InfoSets (if anyone has created them) and few buttons. Seek for a truck. Load your personal items there and leave the work, it doesn’t worth the cash we earn by do it.
Ok, the mortgage… almost forgot it. “The truck” is the transport button. You can reach it through the (yes!) environment menu: “Environment -> Transports”. Click one of them (I challenge you to click both at once) and you will reach a screen who deserves a prize. I’m not sure the kind of prize, but. It’s a useful screen who saved my life, but its design is… weird?
Anyways, we need it, right? Don’t become drama queens and let’s use our new friend.
You will have more or less options to work with depending on the system you are working or how the fascist your Basis team is. Normally you’ll find almost two:
– Copy Standard Area -> Global Area (yessss!!)
– Copy Global Area -> Standard Area (I cannot wonder why someone would use it)
If you have transformed a QuickView, the first thing you’ll must do is to copy the InfoSet the system has created when processed your QV. After it, copy the query. And no, you cannot do both things at the same time (bless your favorite deity, you can… simply, I’ve not find the option with my initial hurry; note to self: read the whole screen once you reach a new one).
Be careful, your “copies” would OVERWRITE other objects with the same name in the global area. Be sure you are not doing it, or some fellow friends will kill you.
II.7. How to save time and effort: the Global functional Area
If you work there (or you’ve read this post until now, or you didn’t but had the same problems I had with the Standard one and came to the Global one to continue your work), each time you create a new item, the system will ask you for a development class and a transport request. After it, everything is (almost) plain and easy.
II.8. How to create ellaborated queries
If you are really looking for this information, I will be cruel with you: learn by yourself, search for a guide, a book or a course or, the best option, hire an ABAPer. I’m sorry, but life is hard, and I must earn some cash to pay my mortgage.
III. Transporting and trobleshooting
Once you have solved your problems (query-related) through this nice guide, you are ready to deploy your queries to other systems. The time has come and we are ready!
Just use the transport method your systems use and … cry again because it doesn’t work. Well, the query itself probably will work, don’t be scared. But maybe you’ve created a transaction and used the query’s program to give the user a nice way to use it. And it doesn’t work. Don’t worry. It’s not the time to cry (yet): just return to SQ01, select your query and go to the menu option “Query -> More functions -> Generate program”. Check the transaction again and voilà! It must work (if it does, don’t ask me, I told you anything I know abot the queries’ system).
IV. Translating queries
Yes. Some weirdos (like me) work in multinational companies, and must develop our items in more than one language. Our queries must be translated (no, don’t be plain and think you can translate your query program alone… you will find no texts there… not in an easy way, almost).
You will look at right and left trying to know if someone is seeing you cry. Dry your tears and thank me matey! SQ07 is here for your translating pleasure (if you like bondage*, masochism and those kind of weird sex practices).
The transaction itself is (at least) easy to understand and use (at last!). Select your languages, your item (no, again you cannot translate more than one object per time) and press the “Change” button. You will navigate through all texts in you object and will be able to put your translation. The only bad thing is the screen is not tuned and will “cut” your long texts without an evident reason.
Final note: If you are translating queries that will be transported, be sure you generate your programs in all languages. I mean: you must log on in the system with the selected language, go to SQ01 and generate the program. I wish you don’t must do it a lot of times for a lot of languages, else if you love repeating tasks.
And I cannot think anything else to add to this post. Just let me wish you luck, my fellow query’ed idiots.
How to create a query transaction
There are two ways:
- the first one is to get the program created by SAP to execute the query (AQand_thousand_letters) and assign it to the transaction as if it was a normal report transaction.
- create a transaction with parameters, and assign:
- Transaction to execute: START_REPORT
- Skip initial screen: X
- And the next default values
- D_SREPOVARI-REPORTTYPE: AQ
- D_SREPOVARI-REPORT: user_group_12_positionsG
- D_SREPOVARI-EXTDREPORT: name_of_the_query
* If you are here because you’ve google’d “bondage”, you are in the wrong place. Or not, maybe you must think about change your career to ABAPer.