Using Microsoft SQL Server Management Studio to determine query

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.

Step1 1 300x226 Using Microsoft SQL Server Management Studio to determine query

Once you are connected, click on New Query.

Step2 1 300x227 Using Microsoft SQL Server Management Studio to determine 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.

Step3 1 300x180 Using Microsoft SQL Server Management Studio to determine query

I then like to close the object explorer just to make more room on the screen.

Step4 1 300x165 Using Microsoft SQL Server Management Studio to determine query

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.

Step5 300x196 Using Microsoft SQL Server Management Studio to determine query

Step6 300x156 Using Microsoft SQL Server Management Studio to determine query

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.

Step7 300x161 Using Microsoft SQL Server Management Studio to determine query

Next I start joining tables where I need information from and put the fields in the select.

Step8 300x142 Using Microsoft SQL Server Management Studio to determine query

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.

Step9 1 300x139 Using Microsoft SQL Server Management Studio to determine query

Keep doing this with other tables until you have all the records you need.  Here is my final query.

Step10 1 300x224 Using Microsoft SQL Server Management Studio to determine 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.

Step 12 300x216 Using Microsoft SQL Server Management Studio to determine query

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.

Step11 1 300x125 Using Microsoft SQL Server Management Studio to determine query

Hopefully this will help you with creating a SSRS report correctly!

 

 

 

 

CategoriesAX