I mentioned in my other post about creating a SSRS report that the very first step is to figure out your query. This will help you know what tables you need to reference, join, and pull from. I’m going to show a quick tutorial on how to use SSMS for this purpose.
You may need to install it on your server/computer but once you have, open it and you will be prompted to connect to a server. My development environment for AX is a computer called AXDEVANDREW so I’m going to be connecting to that.
Once you are connected, click on New Query.
Before you start writing your Select statement, be sure to change the database from master (or whatever it has defaulted to) to the database where your AX information is kept.
I then like to close the object explorer just to make more room on the screen.
I then start my Select Statement. I like to start with selecting everything from my main table. When you start typing the table names, a list should show up which tables are in the database so you don’t always have to type out the whole thing which is nice. After you have typed out the query, press Execute to run it.
To keep customer numbers, etc, confidential, I’m not going to show any results.
Then I start narrowing down which fields from that table I actually want to show. I also throw in a “where” clause so I can narrow down the records to make sure I know I’m getting all the right information. I put everything in uppercase because I find it neater but obviously that just a preference. That being said, I definitely do suggest a few things that you always do. Even if you are only pulling from one table to start, put the table name in front of the field that you are displaying. This way when you join tables later, if two tables have the same field name, your query will know which one you want to show. Also, put the fields on their own line with a comma at the start. This will make commenting just one line out much easier. We will dive into that a little more later.
Next I start joining tables where I need information from and put the fields in the select.
If you want to dive deeper into a table but don’t want to mess with your main query, you can click New Query which will bring up another tab.
Keep doing this with other tables until you have all the records you need. Here is my final query.
A fun little feature in SSMS is commenting out lines without breaking the whole statement. What you want to do is highlight the code you want removed and click the Comment button.
Now you can remove some fields temporarily if you don’t want to see them, or remove a join to a table and see what happens then. As you can see from the red squiggle lines, commenting out my join caused my SQL to break. Nevertheless it can be a great feature. To uncomment the code, just highlight the code and click the Uncomment button, right beside the comment button.
Anyway, now that I have my final SQL, I can use the same idea in my RDP class later to get the same information. It looks a little different in X++ but it’s the same idea. Here is what my query above looks like in X++ in my RDP class.
Hopefully this will help you with creating a SSRS report correctly!