The last couple of weeks I have been working on a series of posts introducing Stonefield Query for Sage SalesLogix.  The first two posts have been focused on set up and interface layout. This week we are going to actually dig in and create our first report. 

I always try to stress in classes that I have taught the importance of sitting down and creating a report plan.  It can be as simple a drawing the report out and answering questions like these:

• 
  
  What are the fields I want displayed.


• 
  
  How do you want to group the report.


• 
  
  Are there any records you do not want displayed, what are the rules. 


• 
  
  Are there any formula fields.

You can read more about my thoughts on planning reports here.

Answering these questions gets your mind focused on your task.  My plan is to build an Account level report listing the Account, Account Manager, Status, Main Phone Number, along with the City, State, and Postal code displayed in one field.  Under the Account I will list the Contacts using a formula field to format the FullName of the Contact, their Status, work number, and email.  From my experience with other reporting writing tools I think I may have to create formula fields for formating the phone numbers, the contacts full name, and a combo City, State and Zip field.  I will not filter this report at this point. 

I want you to know that prior to writing todays post I have not built one report in Stonefield Query.  This will be my first of many I am sure. I started writing the report at 8:56am  – I completed writing the report at 9:25 am with some decent results.  I did not take screen shots while I went but I did take some notes and I will try my best to recreate the experience in the next few lines.

The first thing I did was create a folder to hold my report.  I right clicked anywhere on the tree control side of the interface and selected “New Folder” from the list that appeared.  I named the folder “Custom Reports”.

With my new  folder highlighted, I right clicked again and selected “New Report” from the list.

Immediately I received the “Report Types” screen. 

I used the default and selected “OK”, the” New Quick Report Wizard” opened. 

In step 1 – I named my report, left the defaults, and selected the “Next” button.  Later I discovered that SalesLogix was one of the choices in the “Data Group” drop down.  Playing around now I see using this drop down to filter the tables will make it easier to find the tables you want to work with in step two.

Use the “Table” field to select the table the display field resides in.  The “Available Fields” list along the left hand side will display the all of tables fields.  You can double click on or use the buttons in the center to move the display field from the left to the right. The left pointing arrows are the only way to remove a field from the list on the right.  A double click on a field on the right brings up the fields property window where you can set display options.  If you want to move a field up or down in list on the right then highlight it and drag to where you want it.

The formula button appears to be the place where you will create any formula fields you may need.  The fields that I thought were going to have to be formulated were all already available in the tables I selected.  Anyone who has went through the hassle of formatting phone numbers to display like these in Crystal will understand my relief. 

The “Values” button allows you to see the data of the highlighted field that you have called into your report in a distinct list view.  this gives you the ability to make sure the right field was selected

The “Properties” button, again, brings up the property window for any field highlighted in the list on the right.

At this point I am about five minutes into my report.  All of my fields are selected and this is what my report looks like by selecting the the “Preview” button. 

I know it is not pretty but we are only on step 2.  Closing the preview and selecting the “Next” button and I receive Step 3 of the wizard used for filtering.

With no filters thought up ahead of time I skipped this but I will guarantee we will come back to this section in a later post. Next brought me to the Step 4 which is where we set up our Sort Options.

Same idea as before, find your table and select your field.  To select the Direction highlight the field in the list on the right and select the direction in the center.  I love how straight forward that is.  Select the “Next” button and you come to what really amounts to the meat of this wizard, the Formatting Options area.  

I will also tell you that I went straight through this the first time but found my report pretty much as displayed above except now sorted by the Account Name. In fact I buzzed right through the wizard in about 7 minutes.  My challenge was figuring out the how to group the Contact under the Account.  So to get back to the Formatting Options section of the report wizard, I highlighted my report in the tree control and selected “Edit” icon located on the Tool bar.

Then in the drop down in the upper right hand corner I selected Step 5 Formatting Options

I played around with the Templates area a little with no luck and then I checked the “Advanced Layout” check box on the bottom center of the screen and I selected the the “Edit” button below it.

My world started coming together again.  First you will be asked if you want the objects to be autofitted, I selected no but that option is up to you.  Then I was presented with the Advanced Report Designer.  I started looking around in here trying to get me feet grounded. 

The File menu led me to the “Page Set Up” which then provided me with the “Report Properties” interface it was then I new I was on the right track.

I opened the Data Groupings Tab, Selected the Add button to see the Expression Builder.  The screen seemed a little complicated at first, I mean I did not know what kind of expression I needed.  Following some of the same logic we used in previous screens I selected the Account table.

I double clicked on the Account field and it appeared in the Expression above. Experimenting now. I selected the “OK” button which brought me back to the Report Properties where we see the field now in the Group Nesting Order. 

When I selected OK, I was brought back to the Advanced Report Designer where I could clearly see the new group area.

I am at about 12 minutes into building this report so I spent the rest of the time drawing the report out.  Moving the Account level fields into the Account group, arranging the Contact fields in their order in the details section and playing with the labels until everything made some sense.  You can see the results below. 

At this point I am very pleased with the tool.  I know Stonefield Query can do much more and that I have only touched the surface.  Next week we will look at building a report using Custom SLX Tables and maybe a little filtering to boot. Thanks for reading, stay safe, and keep smiling.