George Jensen's Blog

Simply Using Parameters in SalesLogix Crystal Reports

2010-03-10T15:17:00Z

Recently I was asked to provide some help to a client on something that is very simple and very easy to accomplish. I have covered this same topic in a lot of my blogs posts but this very simple task is buried in with a lot of other detail, so I thought it would be a good time to just simply write about using parameters in Crystal Reports to bring it out front and center for the reader to use.

The set up for this post is you have a report that you want to choose whether or not to display certain information when the document is run. In this instance we will use the detail section of the report.

There are two items that work together to make this happen, a parameter and a formula.

The first step is to create the parameter because you need the name of the parameter for your formula. To add the parameter to your report open the Field Explorer, look for the label of Parameter fields in the tree control, right click and select "New" from the menu. When the Edit Parameter dialog appears, Name the parameter "ViewDetails", select from the Type drop down list the value "Boolean", under the Value list in the center of the screen enter a description for the True value of "Yes" and a description of "No" for the "False" value. Toward the bottom of the view you will see a list of Value Options, set the Prompt Text Setting to "Would you like to include the Details on this report?", set Prompt With Description Only setting to "True", and set the Default Value setting to "True". Select OK and the Edit Parameter dialog will close.

Now we can use this parameter in a formula to suppress the details section of the report. To do this the report should be in design mode. If you look along the left hand side of the report you should see a gray area with items like Report Header, Group Header, or Detail listed. These are the sections of the report. Right click on the Details section of the report and select Section Expert from the menu that appears, find the Suppress check box and select the formula button directly across from the field. When the Formula Workshop opens add the following:

{?ViewDetails} = false

Save and close. When you refresh the report it should ask you the question:

Would you like to include the Details on this report?

If you select "No" the Details section of your report will not display when the report is run. I will leave it to you to guess what happens when "Yes" is selected.

The formula can be used on sections, like in the example, or individual fields. For use on fields, right click on the targeted field and select "Format Field" from the list that appears, navigate to the "Common" tab, here you will find the Suppress Check box for the field, directly to the right of the check box you will see the formula button that launches the Formula Workshop.

I hope this helps with your reporting challenges.

Thanks for reading and have a great day

Geo

SalesLogix Crystal Reporting within a Daily Period of Time.

2010-03-04T14:00:00Z

Today I worked with a reader of this blog to help with a challenge. The company wanted a Crystal Report filtered to only display data with the current date with a time equal to or after 10:00 PM and include records for next day with the time being equal to or before 7:00 AM.

The final Report Selection formula looked similar to this:

(DateDiff ("d",{ACTIVITY.STARTDATE} ,CurrentDate ) = 0 and Time({HISTORY.CREATEDATE}) >= TimeSerial (22, 00,00))
or
(DateDiff ("d",{ACTIVITY.STARTDATE} ,CurrentDate ) = 1 and Time({HISTORY.CREATEDATE}) <= TimeSerial (07, 00,00))

If you want to play with this you could use your Sage SalesLogix Activity table.

It was kind of a fun challenge and I thought some others would benefit from seeing the result. If you have similar challenges please feel to write me directly at george.jensen@customerfx.com.

Thanks for reading.

Geo

More to do with the SalesLogix Crystal Report - Side By Side Bar Graph

2010-03-02T17:54:00Z

SAP Business Objects Crystal Reports (Crystal Reports) is the report writer for Sage SalesLogix (SalesLogix). I am a developer that works for the best CRM Consulting and Service Provider in the world and we primarily work with SalesLogix. This blog focuses on writing Crystal Reports for SalesLogix and helping you, the reader, learn and get more from both tools. I am currently blogging about the graphs that come with Crystal Reports. In a previous post I discussed the simple side by side bar graph that Crystal Reports provides standard. Today we are going to take that graph to the next level and show you more things you can do to make your graph look a little better with a little more data.

The graph we are working with currently provides the number of accounts by type. I am going to change this graph to also show me the same detail but include the status of the Accounts also. To do this I open the report with my bar graph, I right click on the upper corner of the graph and select "Chart Expert" from the list that appears. This will open the Chart Expert dialog defaulted to the "Type" tab with the "Side By Side Bar Graph" selected. To add a little dimension to this chart select the "Use Depth effect" check box on the bottom of this tab. You can also selct if you want the graph to display Horizontally or Vertically.

Next, navigate to the "Data" tab and add in the Account.Status field under the Account.Type in the upper right hand corner of the tab.

On the "Text" tab, change the Title of the Graph to "# Accounts by Type and Status", and the group title to Status. Just so you can see you do have control over the fonts on your graph, go down to the format section and set size of the Title font to 9.

Select "OK" which closes the dialog and allows you to again see your graph.

This is a little messy, to clean up our data to really just focus on those items that are important to us like, I really do not want to see any accounts that have a status of "Purge" and I want to remove accounts with a type of Competitor, Partner, or Vendor. To limit the data for this report, I will go up to the Report Menu, find Selection Formulas, and then select Record form the list. When the formula editor opens I created this formula:

{ACCOUNT.ACCOUNT}>"" // This one was already in the formula

and
{ACCOUNT.STATUS} <> "Purge"
and
Not({ACCOUNT.TYPE} in MakeArray("Competitor","Partner","Vendor"))

Now when I view my report things are cleaned up a little.

FYI, there are more chart options available to you by right clicking on the chart and selecting the "Chart Options" menu item. This will open up a dialog that allows you to play around with the chart properties.

Next time we will get into the Stacked Bar Chart.

Thanks for reading!

Geo

The Simple Bar Graph

2010-02-24T15:10:00Z

I have been very derelict in my blogging responsibilities for Customer FX. Lately, during my commute, I have been trying to figure out why. I have come up with two reasons for my hesitation in writing blogs:

First - We at Customer FX have been extremely busy.

Second - The need for a solid topic to write about.

Being busy is not really an excuse for us at CFX so I ignored the first reason and concentrated thinking about what a good topic would be, finally I came up with graphs. I have done very little on graphs. Honestly, I have not really worked with them all that much so blogging on this topic will provide me a reason to really get to know and use the different graphs that come with Business Objects Crystal Report.

We will start with the simple bar graph.

The goal today will be to build a small little bar graph that provides the total number of Accounts base on the ACCOUNT.TYPE field. To start, build a report using report wizard that contains the SalesLogix Account table. We will need the Account and Type fields from that table. Also, we will want to build a report group using ACCOUNT.TYPE.

For starters, let's suppress the Group1 Header and Footer along with the Details sections of the report. We are doing this just to avoid confusion. This is a group based chart so we need to display this graph in the layer above the group, in this case the header or footer. Doing so allows the chart to roll up the data from the group.

In the Report Footer section of the report, right click and select the "Insert Chart" value on the list that appears. Immediately an orange box has attached itself to the cursor, to release just click on the location where you want the chart to display, in this case just click on the report footer again. This will bring up Crystal's Chart Expert. You will already be on the Data tab because the action will automatically default you to the "Side By Side Bar Chart". The tabs along the top change based on the type of chart. With this bar chart you have the Data, Axes, Options, Color Highlight, and Text.

For this chart instance, on the Data tab, set the field below the "On Change of" value to ACCOUNT.TYPE and the field below the Show Value(s) label to ACCOUNT.ACCOUNT.

On the Options tab, check the box with the label "Show Value" located in the Data Points section.

On the Text tab, uncheck the Title Checkbox and change the Title to read "# Accounts by Type". Then uncheck the Group Title and change to "Type" and the Data Title to "# Accounts".

Now select the OK button and view your chart.

To bring the Chart Expert back simply right click on the chart and select "Chart Expert" from the list that appears. I encourage you to play around with the different setting on the tabs to see the effect on the graph

On Friday we will play some more with the Bar type of chart.

Thanks for reading, Geo

Working with Crystal Alerts

2010-02-12T18:55:00Z

I have often wondered about what the alerts functionality in Crystal Reports was all about so I though I would take a little time to figure it out today. The scenario I will use gives me an alert that identifies when and if a certain Account record qualifies for a report I have written.

To get help with this functionality you can use Crystal Help, use the search word "Alert". This will provide you all of the detail you need to be successful.

To start I will create the alert by selecting the Report>Alerts menu item, the Create Alerts dialog comes into view. Here you can select to either add a new or edit an existing alert. We will select the "Add" button and a new alert dialog will open. I labeled my alert "Test". You can insert a simple message in the Message field or you can use the formula editor to create a rule based alert. I entered the following into the message field and added:

"The Account Customer FX is contained within this report."

Next we need a formula to call the alert, this is done using the "Condition" button. This opens the Alert Condition Formula Editor in which I added the following formula.

If {ACCOUNT.ACCOUNT} = "Customer FX Corporation" .

When the Formula Editor closes, ensure the alert is enabled and select the OK button. You are returned to the Create Alerts dialog close and your all set!

When I refreshed the report by selecting the F5 key. I received the following dialog.

Click in the View Records button and the report will filter down to just the record that qualified for the alert.

Thats it. I think this would be very useful in many instance to call records out. Maybe with History records of certain type or Opportunities that meet a certain probability or complete a step in a sales process.

Pretty cool way of identifying critical information with very little change to an existing report.

Thanks for reading

George

Shading the the Background Color of a Section in Crystal

2010-02-10T16:42:00Z

I have done this in one or two of the SalesLogix Crystal reports I worked on in previous posts to help separate out records. I recently had a client ask me how to do this and when I looked back through the posts I discovered that the information is buried deep in one of the many blogs I have written. So the easy way to shade every other row in a report is as follows.

For this task I use the RecordNumber function that comes with Crystal. This function basically returns the record number in the order it is displayed in your report. I want to shade every other record so I will use a formula to identify odd number records. This is the formula I came up with:

if right(Cstr(RecordNumber),4) in ["1.00","3.00","5.00","7.00","9.00"] then crSilver

else crWhite

In this formula I convert the RecordNumber function from a float to a string. I then take the forth number from the right side of the value, and see if it is in an Array of odd numbers formated as string for the comparison. If the number does match then color Silver will be given to the back ground. If not the color white.

This formula is added through the Section Expert in the details section of my report. Remember that you can easily open the Section Expert by right clicking in the section you want to apply the formula to and selecting the "Section Expert" value from the list that appears. When the expert opens you will see two tabs on the right side of the screen ("Common" and "Color"). The "Color" provides you a place to add the formula.

Quick and easy.

Remember to get all of our Free SalesLogix Reports here...

Thanks for reading my blog. Until next time - Be well! Do good things! Most of all - Keep Smiling

Using Crystal Reports "Next" and "Previous" Functions

2010-01-22T18:40:00Z

Today I am going to write about the "Next" and "Previous" Functions available with Crystal Reports. As their names describe the intent of these functions is to provide you a value of your selected field for either the Next or the Previous record as shown in your report.

For example if I wanted to determine the number of days it has been since a History record has been written against an Account in SalesLogix, I could use these two functions along with the DateDiff function to organize our data. Let me give you an idea of how!

First, create a SalesLogix report with the History table. Make sure to create two groups, group 1 is for the Account Name - sorted ascending, group 2 is by History Complete Date - sorted ascending by second. The group level fields are used in the report. Use your Section Expert to have group1 overlay group2. Set the fields in the two groups so they would line up into columns if they were next to each other. Also, I suggest suppressing all group footers and the detail section in your report.

Now create a formula field called "Next" and add the following formula:

IF {HISTORY.ACCOUNTNAME} = Next ({HISTORY.ACCOUNTNAME}) then CSTR(Next ({HISTORY.COMPLETEDDATE})) else ""

This formula states if the current records Account Name does not equal the next records Account name then do not show the next records Complete date. If you do not include the account comparison in the formula then "Next" will display a value for the next record even if it is not from the same account.

Add this field anywhere in History Group 2. If you put this field by the complete date you will see that the field displays the date of the next records complete date. The last record under the group for the Account will be blank. What I want is this last History record that was added for this Account and none of the others so I will use this blank field to my advantage. My group 2 suppression formula looks something like this.

{@Next}<>""

This should bring you down to just one record per account. Since none of the data showing has a "Next" value so remove that field from the report.

Let's go ahead and create another field called "Previous" using the following formula:

IF {HISTORY.ACCOUNTNAME} = Previous ({HISTORY.ACCOUNTNAME}) then CSTR(CDATE(Previous ({HISTORY.COMPLETEDDATE}))) else ""

This field is very similar to the "Next". Add this field to the report and you will see that you now have the date of the previous History record. If the value is blank then there is not a previous record in the system

So you now know the Complete date of the last History record along with the Complete Date of record prior to the last History record, but how long has it been since the last History record? Create one final formula field called "Days Since" using this formula:

DateDiff ("d",{HISTORY.COMPLETEDDATE},CurrentDate)

Add this to your group 2 and you will have all the information you need to see what Accounts are in need of some attention.

Thanks

Geo

Displaying when a Crystal Report Was Last Modified

2010-01-15T21:33:00Z

There are two functions that come with SAP Business Objects Crystal Reports that can help you display on your reports when the report format was last modified. These functions are found under Functions>Document Properties and are labeled ModificationDate and ModificationTime.

For use, I created a new formula called ModificationDateTime and added the following syntax:

"This Report Format Last Modified on "+Cstr(ModificationDate)+" at "+Cstr(ModificationTime)

I put my formula field in light gray letters in the page footer, light enough for someone to just barely see it.

This way when you get complaints about one users report does not look like another users version of the same report you can peek at the ModificationDate/Time displayed on the bottom and tell them they have an outdated report.

If you play around with some of the functions in the Functions>Document Properties of the tree, you will find several functions that could come in handy. I will show you some of those in the next few days.

Have a great weekend!

Geo

Using Functions to Look into the Future

2010-01-13T14:41:00Z

If you find yourself in a position where you need to report into the future, Crystal provides a couple of functions that allow you control what data is displaying.

First lets look at the AllDatesFromToday and AllDatesFromTomorrow functions. These functions are found under the DateRanges group of functions in the Functions tree found in Crystal Formula Editor.

AllDatesFromToday, specifies a range of Date values that includes any date from the present day to any future Date value that may appear in a field. AllDatesFromToday includes the present day.

AllDatesFromTomorrow, specifies a range of Date values that fall after the present day. AllDatesFromTomorrow does not include the present day, but does include any future date.

The use of these functions are simple enough, the syntax looks something like this.

{ACTIVITY.STARTDATE} in AllDatesFromToday

The functions will provide all records that qualify no matter how far out.

For this instances where you want to limit how far out you could use functions like one called "Next30Days". There are actually a couple additional functions along the same lines to include Next31To60Days, Next61To90Days, and Next91To365Days. The syntax looks like this:

{ACTIVITY.STARTDATE} in Next30Days

Have fun reporting.

The DateRange Functions

2009-12-29T20:06:00Z

Business Objects Crystal Reports comes with some really nice standard functions that make working with your data very easy. I have been working on series of blog posts that introduces some of these functions to help SalesLogix report writers with their efforts. Today I will focus on the start writing about function under the category of "Date Ranges" The first set we will look at are the aging, more specifically Aged0to30Days, Aged31to60Days, and Aged61to90Days.

The function looks at the current date and depending on the function being used will return records that fall within the date range.

The syntax for the formula is very basic:

{Table.DateField} in Aged0to30Day

So in SalesLogix, if I wanted to create a quick report that list all of Accounts created within the last 30 days I could write this Record Selection formula:

{ACCOUNT.CREATEDATE} in Aged0To30Days

To expand the functionality for my users, I could add a parameter with some choices like "0 - 30 Days" which will allow my users to select which of the three functions they want to use, my Record Selection Formula will then look like this.

if {?DateRange} = "0 - 30 Days" then {ACCOUNT.CREATEDATE} in Aged0To30Days
else
if {?DateRange} = "31 - 60 Days" then {ACCOUNT.CREATEDATE} in Aged31To60Days
else
if {?DateRange} = "61 - 90 Days" then {ACCOUNT.CREATEDATE} in Aged61To90Days

We could even go a little bit farther and add to our parameter some values like "Last 60 Days" and "Last 90 Days". Then again edit our Record Selection formula and add:

else
if {?DateRange} = "Last 60 Days" then {ACCOUNT.CREATEDATE} in Aged0To30Days or {ACCOUNT.CREATEDATE} in Aged31To60Days
else
if {?DateRange} = "Last 90 Days" then {ACCOUNT.CREATEDATE} in Aged0To30Days or {ACCOUNT.CREATEDATE} in Aged31To60Days or {ACCOUNT.CREATEDATE} in Aged61To90Days

Nothing real fancy here. I am sure there are other ways to get this same thing accomplished but I hope this gives some of the new users out there an idea of how to use these functions.

Happy New Year!

Please have a plan and Stay Safe

Using Microsoft Excel as a delivery method for reporting

2009-12-22T14:00:00Z

There are many ways to get your data into a Microsoft Excel spread sheet for reporting purposes. Business Objects Crystal and Sage SalesLogix both offer an option to kick out report data to an Excel spread sheet. Last week I worked with a client who wanted exactly that, a set of data exported to Excel so the users could manipulate it as they needed. She came to me under the assumption that this could only be done through Crystal. Now the benefit for going through Crystal is the parameters established in the report are honored. But it is possible to export your data from the SalesLogix database directly into Excel and with a little help from SQL Views you are able to filter and organize your data for the export. The best part is the data is able to be refreshed as often as required in excel and the users do not need to be in SalesLogix to run the report.

The first step in this process was to think about all of the data you would like to see in the final export. In this instance my client had several version of the sheet she wanted to produce so the first set of views brought all of the data together that could be used in any of the Excel files, we did this without using any filters. This allowed her to then customize additional SQL views to support each version of the file she wanted. Creating the views was really the toughest part, we ended up rewriting our views a couple of times to support additional fields requested after user testing of a sample file. So I encourage you to really plan things out with your end users before sitting down to write the SQL views, hopefully planning up front will result in less overall work effort.

For our example today we will use a view I created for the Crystal Report writing class. This view is a Contact based and provides the number of History, Activity, Opportunities, and Tickets related to each Contact in your system. This view is nothing Fancy but will help prove our point.

Create View sysdba.VContactCounts

SELECT LastName, FirstName,

(SELECT COUNT(HISTORYID) AS Expr1 FROM sysdba.HISTORY WHERE (CONTACTID = c.CONTACTID)) AS HistoryCount,

(SELECT COUNT(ACTIVITYID) AS Expr1 FROM sysdba.ACTIVITY WHERE (CONTACTID = c.CONTACTID)) AS ActivityCount,

(SELECT COUNT(OPPORTUNITYID) AS Expr1 FROM sysdba.OPPORTUNITY_CONTACT WHERE (CONTACTID = c.CONTACTID)) AS OpportunityCount,

(SELECT COUNT(TICKETID) AS Expr1 FROM sysdba.TICKET WHERE (CONTACTID = c.CONTACTID)) AS TicketCount

FROM sysdba.CONTACT AS c

GROUP BY ContactID,LastName, FirstName

Once your view is giving you the data the way you want to see it the easy stuff begins. I am working with MS Office 2003 so please forgive me MS Office 2007 people. The client I worked with had Office 2007 so this will work there also, If I get access to a 2007 machine I will post the steps for you.

The items required to be successful at this are the name of your SQL server, the sysdba password for SQL Server. The name of your SalesLogix database and of course the name of your SQL view.

Open a new MS Office Excel file.

Select the Data>Import External Data>Import Data menu item

The following screen will appear:

Here we are going to select New SQL Server Connection which brings up the following screen.

This is where you list out the SQL Server Name and sysdba password. On selection of the "Next" button the following screen appears:

Here we select the database name and then search for the SQL View. This is a little tricky, the views are listed here first then tables.

You see here the check box for Save Password. If you do not want the users to have access to this sysdba password then I suggest setting this to true. They will have access only to the data released via this connection.

On this screen you see the cell where the export will start building the export. You can click on the appropriate cell in the back ground to reset. Then click Finish and the export should run immediately. IF there are more then one views providing you the data this process can take a few seconds. Moving forward, all the users has to do is hit the button, or Data Refresh Menu Item to update the file

Now the data is in excel you can do anything you want with it to include sorting or building formulas or graphs.

FYI, I played around with other methods of creating the connection to the database but this was the most straight forward. If I discover a better method I will update this post.

Hope this helps someone. Thanks for reading. Geo

Using Multiple Value Parameters to Select Records for your Crystal Reports

2009-12-08T14:51:00Z

I have often been asked to provide the ability to allow users a multi select parameter, such as User Name, and then have that parameter limit the data in their Crystal report. I have to admit this one has always been one of the items I always have struggled through, coming up with one alternative or another to meet the client needs. Recently I was asked this same question by a support client of ours. Together, we can up with a pretty lengthy Record Selection formula to achieve the desired results. Not happy with how hard it was to achieve this I decided to dig into this issue again this morning and my results were surprisingly a lot easier then I ever imagined.

To prove my point follow along by creating a new Crystal report connected to your SalesLogix database, The only table you need to add to this report is UserInfo and we only need one field, UserName.

There are three pieces to this puzzle. The first is the parameter.

The parameter I created was labeled UserName, I changed the "List of Values" field to Dynamic, "Prompt with group text" I typed in UserNameList and then selected the UserName from the field drop down under the word Value. Other settings are "Prompt Text" = Select the Users you want to display in this Report, "Prompt with Description Only" is set to True, and the most important is "Allow multiple values" is set to True.

Next let's Create a formula field to see our results, Name the field anything you would like and add the following as the formula.

Join({?UserName},",")

Now drag your formula field onto your report. You should now be prompted by the report to select some users to display on your report. Select two or three to prove the point. When you close the parameter you will find you formula field is now populated with the names of the individuals you selected. Each value is separated by a comma.

The final piece is the record selection criteria. Remember the record selection criteria is set using the Report>Selections Formulas>Record menu item. When the formula editor opens, add the following formula:

{USERINFO.USERNAME} in {@Test}

For your version of the formula, "{@Test} should be replaced with the your formula field. When you close the editor you immediately see your list of users drop to the two or thee you had selected.

Embarrassingly easy stuff. I hope this helps someone down the road.

Thanks Geo

Creating Lists in Merge froms

2009-11-24T19:58:00Z

We just had a client ask or support team how to display one to many data (or a list) in merge forms. This is very achievable, the process is pretty straight forward and can really expand the use of your merge forms.

As is the Customer FX way we want to share with the SLX user community so I though I could take a break from Crystal Functions and write up the how to for you. The scenario I will use today is you want to create a Contact Merge form that list all of the Opportunities a contact has been involved in. I am assuming that you already have the Contact side of things developed in your merge form and are ready to add the list of opportunities.

Open your merge form for editing by using the SalesLogix Write/Template menu item.

Find the merge form in the list and select the Edit button at the top of the Manage Templates dialog. Note, if the merge form is in the public folder the system will make save a copy for editing. Find that merge form down in the Private Templates section of the tree control.

With the Merge Form open find the location in the document where you want to add the table for your Opportunity information and place your cursor.

Select the "Insert Special Field" button, highlight the SQL menu item and then select table from the list that appears.

You should see a "New SalesLogix SQL Field" added to your merge form and the SalesLogix Mail Merge Editor should appear. If it does not you can select the Edit field located at the top of the Letter Editor.

In the list you will find an icon that looks like a little table, the type will equal SQL. Highlight the value in the list on the left and the properties will appear in the dialog on the right.

The Field Identifier - allows you to name this control on your form.

The SQL field is where you add your SQL Statement that will collect the one to many opportunity data for your Contact. You select the button on the right side of the SQL field to open a dialog that allows you to enter your SQL Statement. You can use SQL to help develop your statement. If you use SQL, log in as sysdba, then you should be able to copy your statement directly into this dialog. My SQL Statement looks like this:

SELECT OPPORTUNITY.DESCRIPTION
, OPPORTUNITY.ACTUALAMOUNT
, OPPORTUNITY.ACTUALCLOSE
FROM
CONTACT left join OPPORTUNITY_CONTACT on CONTACT.CONTACTID = OPPORTUNITY_CONTACT.CONTACTID
inner join OPPORTUNITY on OPPORTUNITY_CONTACT.OPPORTUNITYID = OPPORTUNITY.OPPORTUNITYID

Where CONTACT.CONTACTID = :ContactID

Order By OPPORTUNITY.DESCRIPTION

Notice the where statement contains the = :ContactId. This sets your SQL up to run in the merge form. The ContactID is passed through to the form when you run the merge process.

When Your SQL statement is entered select the OK button.

Options: This sets up the look of your list. From top to bottom I usually keep the 1st item checked, I unchecked the 2nd and 3rd, the 4th and 5th stay checked, the rest are all unchecked. Play around to format the table the way you like.

Now for each column in your SQL Statement you need to format the column in the merged table. This is done by selecting the Edit table Columns button.

If you look at my select statement I used Description (which is a Tex field), ActualAmount (a Currency field), and ActualClose (a DateTime). So when the Edit Columns dialog opens I added the formatting for each in the same order as they are displaying through my SQL Statement.

For Description - I added word “Description” in as the Caption, I set the alignment to the left, and I had none for a type.

For the ActualAmount - I added Actual Amount for the caption, right for the alignment, and currency for the type.

For the ActualClose - I added Close date for the caption, Center for Alignment, and DateTime for Type.

Now exit out of the Mail Merge Editor using the OK buttons and test your merge form.

Working with the DatePart Function in Crystal

2009-11-19T14:57:00Z

This function is very popular one that allows you to organize your data based on Dates.

The basic syntax for the function looks like this:

DATEPART( Interval Type, InputDateTime)

A more realistic example:

DatePart ("m",{ACTIVITY.STARTDATE})

This will return the month of each Activity is scheduled in. Please note the interval type is surrounded by double quotes.

Here is a list of the Interval Types from Crystal Report On line Help (Highlight the function and select the ? from the Formula Workshop Tool bar)

Interval type value	Description
yyyy	Extracts the year
q	Quarter (the result is 1, 2, 3 or 4)
m	Month (the result is from 1 to 12)
y	Day of year (1 to 365 or 366 in a leap year)
d	Day part of the date (1 to 31)
w	Day of week (1 to 7 with the result depending on firstDayOfWeek)
ww	Week of year (1 to 53 with firstDayOfWeek and firstWeekOfYear determining the exact days of the first calendar week of the year)
h	Extracts the hour part of the given DateTime (0 to 23)
n	Minute part (0 to 59)
s	Second part (0 to 59)

So if I wanted to create a record for all of the Activities scheduled for this week and the next week, I could write the following as a select Criteria.

DatePart("yyyy",{ACTIVITY.STARTDATE}) = datepart("yyyy",CurrentDate)
and
(DatePart("ww",{ACTIVITY.STARTDATE}) = datepart("ww",CurrentDate)
or
DatePart("ww",{ACTIVITY.STARTDATE}) = datepart("ww",CurrentDate)+1)

Cool stuff, More next week. Thanks for reading.

See a list of SalesLogix Reports updated by Customer FX, follow this link.

Working with Crystals DateDiff Function

2009-11-06T13:25:00Z

I have been spending some time writing about the Functions that come with Business Objects Crystal Report. Today I thought I would start some posts on the Date and Time Functions. These functions are mostly used for helping the user dissect any DateTime fields in your database. The first one I will work with is the DateDiff.

The DateDiff is used to provide us a value that represents the difference in time between to dates. Crystal has help available for this function by navigating to the Function Tree, find the Date and Time group, and DateDiff group, then highlight one of the options for DateDiff and highlight and select the ? in the Formula Workshop Toolbar.

An example of this function being used would be if wanted to know how many months an Account has been with your company, for this you might use:

DateDiff ("m",{ACCOUNT.CREATEDATE},CurrentDateTime)

Obviously the "m" is the argument for month. Notice the formula required double parenthesis around the argument.

Below is a list of other arguments that can be used with this functions:

yyyy	Year
q	Quarter
m	Month
y	Day of year
d	Day (both "y" and "d" find the difference in days)
w	Number of weeks between startDateTime and endDateTime
ww	Number of firstDayOfWeek's between startDateTime and endDateTime
h	Hour
n	Minute
s	Second

You can see this functions used in many of the standard SalesLogix Reports, most commonly in History and Tickets. If you want to see a list of updated SalesLogix Reports provided by Customer FX follow this link.

In Minnesota, it is time to go out do the deed of helping our over populated deer herd survive the cold hard winter by removing the weak and feeble minded. It is rough duty but I am up to the task. The best thing about this weekend is I have the privilege of hunting with both of my daughters and my lovely wife. Until next week be safe and have fun.

geo

Conversion Functions in Crystal

2009-11-04T14:33:00Z

In all forms of development there are times when you need to convert data types for use in formulas. In Business Objects Crystal Reports, I often use data with labels control to display totals in my report footers. To do this in my SalesLogix Reports, I often have to convert the total from a double to a string. For our benefit Crystal Reports offers many conversions functions.

I found most of the information for this post in the Crystal help. To view Help on these functions all you have to do is highlight the function of interest in the function tree control and select the ? button in the Formula Workshop tool bar.

The Boolean - Labeled CBool - is only used with numbers or currency, This action returns a true if the referenced field is anything but 0, if it is 0 then it returns a false. The syntax looks something like this:

CBool ({OPPORTUNITY.SALESPOTENTIAL}) would return True or False

Currency - Labeled CCur - converts number, currency or string values to a currency. This action returns a currency value. So instead of formatting a field to display a currency you could just use this function through a formula field. The syntax looks something like this:

CCur({OPPORTUNITY.SALESPOTENTIAL}) returns $327,323.00

Date and Time - Labeled CDateTime, CDate, or CTime - all three will convert DateTime, number or string values to and return a Date and/or Time. Here is some example syntax:

CDate({OPPORTUNITY.CREATEDATE}) returns 2/26/2009

CTime({OPPORTUNITY.CREATEDATE}) returns 11:47:21 PM

CDateTime (2009,10 ,18, 13, 55, 21) returns 10/18/2009 1:55:21PM

String - Labeled CStr, Basically converts any other data type to a string value. Basic syntax looks something like this.

"The Total Sales Potential for this Opportunity is $" + CStr ({OPPORTUNITY.SALESPOTENTIAL})

Would return

The Total Sales Potential for this Opportunity is $327,323.00

I hope this blog is of help to someone.

You can benefit from this blog by downloading our many re-worked SalesLogix Crystal Reports, see a list by following this link and thanks for reading.

Geo

Crystals Length Function

2009-10-30T12:32:00Z

I have seen the Length function that comes with Business Objects Crystal Reports used a lot in the standard Sage SalesLogix reports. Mostly you see this function used in the formula fields created for displaying phone numbers in true false statements.

The first rule for this function is it works only with string fields so be prepared to convert if neccessary. The syntax for this function is simple:

If Length {field} = number then formula.

Here is an example of how the function is used from one of the many Customer FX Improved SalesLogix Reports.

If Length ({ACCOUNT.MAINPHONE}) = 10 and {@Telephone_Country} = "Standard"
    Then
    "(" + {ACCOUNT.MAINPHONE}[1 to 3] + ") " + {ACCOUNT.MAINPHONE}[4 to 6] + "-" + {ACCOUNT.MAINPHONE}[7 to 10]
    Else
    If Length ({ACCOUNT.MAINPHONE}) > 14 or Length ({ACCOUNT.MAINPHONE}) < 10
    Then
    {ACCOUNT.MAINPHONE}
    Else
    if Length ({ACCOUNT.MAINPHONE}) = 14 and "(" in {ACCOUNT.MAINPHONE}
    then
    "(" + {ACCOUNT.MAINPHONE}[2 to 4] + ") " + {ACCOUNT.MAINPHONE}[7 to 9] + "-" + {ACCOUNT.MAINPHONE}[11 to 14]
    else
    if Length ({ACCOUNT.MAINPHONE}) = 14
    then
    "(" + {ACCOUNT.MAINPHONE}[1 to 3] + ") " + {ACCOUNT.MAINPHONE}[4 to 6] + "-" + {ACCOUNT.MAINPHONE}[7 to 10] + " " + {ACCOUNT.MAINPHONE}[11 to 14]
    else
    {ACCOUNT.MAINPHONE}

FYI, this formula fields requires the use of another formula field called Telephone_Country which simply is looking at the Address.Country field to help determine the formatting for the phone number.

I hope everyone has great weekend.

Working with the Like Function

2009-10-27T19:32:00Z

The next topic in our write- up of the Functions that come with Business Objects Crystal Report is the Like function. I use the "Like" function a lot in my record selection criteria. Double parenthesis and the star character is the key to this function.

The basic syntax looks like this:

{Field} like ("*a*")

This will return any value in the field that contains an "a".

{Field} like ("a*")

This will return any value in the field that starts with an "a".

{Field} like ("*a")

This will return any value in the field that ends with an "a".

If there is character set that the middle character you can use a ? in the syntax.

{Field} like ("*s?n")

This will provide us all names that the third to the last characters contain the "s", the second to the last character contains anything, and the last character is an "n".

Simple but useful little function.

Remember our list of free SalesLogix Reports.

Working with Left and Right String Functions

2009-10-20T13:28:00Z

Continuing on working with the functions in Business Objects Crystal Reports I thought we could look at the Left and Right functions. I know to some of the readers this may be some simple stuff but remember I try to write to new users of Crystal and SalesLogix reporting.

When I think of working with these function the first thing that comes to my mind is the military. While serving in the National Guard I have had the opportunity to use these functions in script in creating reports. By regulation a Soldiers social security number is used as a unique identifier. The military is trying to be smart and not throw this important id number on the many lists they deal with everyday so they often use what is called a laundry mark.

A laundry mark is a combination of the soldiers first initial of the last name and the last four of the SSN. To programmable call the laundry mark out of data we can use the Left and Right String functions. For our example we will use the Sage SalesLogix Contact tables Last Name and Work Phone. To display this data in Crystal we would create a formula field called Laundry Mark.

When the Field Formula Workshop opens look for the Function Tree. If the Function Tree is not displaying select the Alt and U keys. Navigate to the String Value in the Function Tree and click on the plus sign next to it.

You can see the Left value in the list, the right is a little further down but the process is the same for each.

Left (str, length)

There are two parameters for these function, the first is the string. If you are using a integer or double field type you would have to convert the field to a string. Both of our fields are string fields.

The second parameter is the number of characters from the left you want to collect. For lastname we only need first character but for the workphone we will use the last four characters.

Our formula for LaundryMark field would look something like.

Left(lastname, 1) & Right(workphone, 4)

The data will look something like this when inserted into your report.

J0214

We will go into the Conversion next.

Remember our list of free SalesLogix Reports

Working with Null or Empty Characters in Crystal

2009-10-13T14:28:00Z

This is the start of a series of post working with the many functions used in the Formula Workshop of Business Objects Crystal Reports. These functions are often used while reporting out of Sage SalesLogix database. Today I thought I would start out with a Null values.

What is a "Null" character? www.iwebtool.com defines a null character as:

A null character is a character with all its bits set to zero. Therefore, it has a numeric value of zero and can be used to represent the end of a string of characters, such as a word or phrase. This helps programmers determine the length of strings. In practical applications, such as database and spreadsheet programs, null characters are used as fillers for spaces.

Put simply and in reference to Crystal Reporting, it is when nothing is displaying in your report field.

Most of the time this occurs when records, such as an Account record, is created in a table.

For example, the Insert New Account/Contact screen collects the data your company has chosen to be required for the creating of an Account in your system.

The data fields that are not populated or not included on that screen are populated with a Null value. We can look at the raw data in SQL to see all of the null values that are sitting in the database fields as place holders.

One other thing to look for are records were a value was added but then removed. These empty fields are basically the same thing as Null Characters but they are called out separately in formulas.

In my example today I will use the Account Division field.

When dealing with Null or Empty values in Crystal's Formula Workshop, this is the Syntax I often use where I want to call out records where the condition exists :

IsNull({ACCOUNT.DIVISION}) or {ACCOUNT.DIVISION} = ""

I also use this to help me suppress records from a group or the detail sections of my reports. In those times where a value is needed then you could use this formula in a Custom Formula field

If IsNull({ACCOUNT.DIVISION}) or {ACCOUNT.DIVISION} = "" then "Unknown" else {ACCOUNT.DIVISION}

Now if I want to go the opposite way where I am only looking for records where there is data in the field I often just use the following:

{ACCOUNT.DIVISION}>""

Some times you absolutely need to use the "is not null" and that is written.

Not(IsNull({ACCOUNT.DIVISION}) )

With this you would get the empty values in your list.

Well there it is, the first of many to come. I look forward to learning and writing more about functions in Crystal Reporting.

Hey, do not forget all of those free download of reports. These are great way of getting a good start on whatever report you are working on.

Take care - Geo

Business Object Crystal Formulas - The Formula Workshop

2009-10-06T20:08:00Z

During the SalesLogix Report Writing Class, I was asked where is a good place to find syntax for Crystal formulas. My answer is of course the web, or the many books that provide information about Business Objects Crystal Reports. While thinking of what I would like to write about in my next series of posts I realized that it might be a good idea to blog about writing formulas. As stated before the intent of my blogs are to teach to the lowest level so I thought the first post should be reviewing the Formula Workshop

The formulas written in Crystal Reports can be used for limiting the data that is being brought into a report, to actually do math, or can help make your data display the way you want it to. There are three main areas where the Formula Workshop is launched from.

The first is of course the "Formula Fields" listing in the Field Explorer window. Right click and select "New" to start a new formula. To Edit right click on the formula and select "Edit".

Next is the Selection Formulas which can be accessed through Report > Selection Formulas > Record (or Group)

And finally any where you see one of these icons . This icon is seen when you are formatting fields through the Format Editor or working with the Section Expert.

When you use any of the methods described above the Formula Workshop will open.

The section to the left is called the the Workshop Tree which provides you access to any formula that may already be written in your report.

The section center left is the Field Tree. This displays all of the fields that are either already selected in your report or are available through the tables selected for your report. To get a field to display in the formula

The section center right it the Function Tree. This lists any and all functions that are available to you in Crystal.

All the way to the right is the Operators Tree which of course list all of the Operators available to you.

Formula window is at the bottom center and is where we build our formulas. As we go through our post we will visit this view a lot.

First up is dealing with Null or empty sting values in our formulas.

The Absolute Best Way to Sync Views Out to Remotes

2009-09-29T18:20:00Z

Well this topic is dead, or at least it will be after this post.

I wrote about this topic just prior to The Crystal Report Writing class. During the class the question was asked

"What about using the Execute SQL tool in the SalesLogix Administrator for syncing out SQL views to remotes?"

As a development team we have looked at this before and always found that it just did not work. But we had not tested it in quite a while so we decided to try it again, and this time it worked.

So here for the final time as a topic on the Report Writing blog is the "Best way to Sync SQL views out to Remotes".

To accomplish this we need a SQL view. A SQL view for those who do not know is an excellent way of organizing data for a report. When your view is complete the view will act like a database table. The biggest issue with using SQL views in reports is the Remote database issue. Just creating a view in SQL does not synchronize it out to the SalesLogix Remote Databases.

Here is a simple SQL selected statement that counts records related to an Account.

SELECT     a.ACCOUNTID, a.ACCOUNT, COUNT(c.CONTACTID) AS ContactCount,
                (SELECT     COUNT(HISTORYID) AS Expr1  FROM sysdba.HISTORY WHERE (ACCOUNTID = a.ACCOUNTID)) AS HistoryCount,
                 (SELECT     COUNT(ACTIVITYID) AS Expr1  FROM sysdba.ACTIVITY WHERE (ACCOUNTID = a.ACCOUNTID)) AS ActivityCount,
                 (SELECT     COUNT(OPPORTUNITYID) AS Expr1 FROM  sysdba.OPPORTUNITY WHERE  (ACCOUNTID = a.ACCOUNTID)) AS OpportunityCount,
                 (SELECT     COUNT(TICKETID) AS Expr1 FROM sysdba.TICKET WHERE (ACCOUNTID = a.ACCOUNTID)) AS TicketCount
FROM         sysdba.ACCOUNT AS a LEFT OUTER JOIN
                      sysdba.CONTACT AS c ON a.ACCOUNTID = c.ACCOUNTID
GROUP BY a.ACCOUNTID, a.ACCOUNT

To make it a view we add the text of:

CREATE VIEW sysdba.vACCOUNCOUNTs

This is entered right above the select statement.

When you combine the view with other tables we can get some excellent roll up information.

This data can even be used with the group builder in SalesLogix

To get this view synchronized out to the remotes, log into the SalesLogix Administrator.

Once the interface is open use the Tools> Execute SQL menu selection to open the Execute SQL tool.

When the Execute SQL interface opens, right click and select Add from the list that appears.

When the Edit SQL view appears copy in the SQL View you wish to synchronize out.

When you are ready, select OK. You are returned to the Execute SQL interface.

Next we must identify the users who will receive this view. On the lower half of the Execute SQL interface we see a list of names on the Left, find and highlight the name of the people the view should be synchronized out to and select the right pointing arrow.

Once all of the target users are selected, click on the "Execute" button on the upper right side of the view.

You will receive the following message box. Simply click on the "Yes" button....

....and the SQL Script will run.

Select OK and close out of the SalesLogix Administrator.

The next time the Sync Serve Synchronizes it will pick up the view and ship it off to your selected users.

A lot easier then writing all of that script. God Bless.

Geo

Sync Views out To Remotes

2009-09-18T15:44:00Z

The scenario is you have created a SQL view for use in a Crystal Report. Your end users are excited about the report but then you started getting phone calls from your remote users that the report will not run. You realize that the view you used to help organize your data did not synchronize out to your remote user base. After some looking around on CustomerFX.com you realize that views do not Synchronize out Remote databases. Your options are Cut New Remote databases or some how get the views to sync out. Then you discovered Kris Halsrud's blog on this exact topic written on May 13th called Distributing SQL Views to Remote SalesLogix Databases.

Kris's post provided the answer to what I was looking for in preparation for the SalesLogix Reporting class I am preparing. This issue is something I have been trying to overcome throughout my SalesLogix reporting career. Since my goal is to write to the "Beginner" level of SalesLogix reporting I thought I would break down the process that Kris describes for the new Administrator or Report Writer.

The concept of what Kris writes about is that since changes to a detail view in SalesLogix synchronize out to remote databases, we can write a script that runs whenever a detail view opens. This script basically checks to make sure the view exists. If the view does not, the script creates the view. Once the change is saved and released, the script will synchronize out to the remotes and your report will run.

In order to accomplish what I am writing about today you must have access to the Sage SalesLogix Architect and the tables that support the view must be available in the remote database.

Our target detail view is the SLX Crystal Report Viewer. This is the screen that comes up when you select the report button on your nav bar.

You can open this view in the Architect using the File>Open Plugin menu item.

When the "Choose Plugin" screen opens, click on the System listing in the tree control on the left side of the view, and look for and select the the "SLX Crystal Report Viewer" from the list on the right.

When the form opens in the Architect look for and select the Script Tab.

There is some form information in gray text on the top of the script. Just below you see the word "Globals". Insert the following text under that word.

Const SLXPW = "your_sysdba_password"

The phrase in the script, "your_sysdba_password", will need to be changed to match your SQL server sysdba password.

Scroll down the script and search for the sub "AxFormOpen".

Directly under the "AXFormOpen" line enter the following text:

FXCheckViews
application.BasicFunctions.ProcessWindowMessages

Any existing text will come after these two lines. If more then one view is required to be created then you could use different versions of the FXCheckViews text. It could look something like this:

FXCheckViews
FXCheckViews2
application.BasicFunctions.ProcessWindowMessages

Now scroll all the way to the bottom of the script and insert the following code:

' CFX Begin Script
Sub FXCheckViews
dim objrs
    Set objrs = GetNewRecordset()
        objrs.Open "select id from sysobjects where name = 'NAMEOFVIEW' and type='V'"
        if(objrs.eof or objrs.bof) then ExecuteNativeSQL("CREATE view NAMEOFVIEW as SELECTSTATEMENT")
    Set objrs = nothing
End Sub

Function GetNewRecordset()
    Set GetNewRecordset = CreateObject("ADODB.Recordset")
    Set GetNewRecordset.ActiveConnection = GetNativeConnection()
    GetNewRecordset.CursorLocation = 3 'adUseClient
    GetNewRecordset.CursorType = 3 'adOpenStatic
    GetNewRecordset.LockType = 3 'adLockOptimistic
End Function

Function ExecuteNativeSQL(ByVal SQL)
Dim objConn
Dim lRecs
    On Error Resume Next
    lRecs = -1
    Set objConn = GetNativeConnection
    objConn.Execute SQL, lRecs, 128 'adExecuteNoRecords
    If Err.Number <> 0 Then RaiseError "executing SQL statement"
    On Error Goto 0
    Set objConn = Nothing
    ExecuteNativeSQL = lRecs
End Function

Function GetNativeConnection()
Dim objRS
Dim connstring
Dim cn
    Set objRS = Application.GetNewConnection.Execute("slx_getNativeConnInfo()")
    connstring = objRS.Fields(0).Value & ";Password=" & SLXPW
    objRS.Close
    Set objRS = Nothing
    Set cn = CreateObject("ADODB.Connection")
        With cn
            .ConnectionString = connstring
            .Open
        End With
    If Err.Number <> 0 Then RaiseError "opening ADO connection object"
    On Error Goto 0
    Set GetNativeConnection = cn
    Set cn = nothing
End Function

The first sub in the script you just inserted needs some adjustment.

Sub FXCheckViews
dim objrs
    Set objrs = GetNewRecordset()
        objrs.Open "select id from sysobjects where name = 'NAMEOFVIEW' and type='V'"
        if(objrs.eof or objrs.bof) then ExecuteNativeSQL("CREATE view NAMEOFVIEW as SELECTSTATEMENT")
    Set objrs = nothing
End Sub

The text 'NAMEOFVIEW' appears twice in the sub routine and should be replaced with name of the view as it appears in the SQL database.

The text SELECTSTATEMENT should be replaced with the actual SQL statement used in the view. This needs to be formatted to display on one line. I formated this by copying the SQL Statement into a text document and deleted out all of the carriage returns. Then I pasted the formatted text into the script making sure to just replace the SELECTSTATEMENT text. Here is an example:

Sub FXCheckViews
dim objrs
    Set objrs = GetNewRecordset()
        objrs.Open "select id from sysobjects where name = 'vAccountDescription' and type='V'"
        if(objrs.eof or objrs.bof) then ExecuteNativeSQL("CREATE view vAccountDescription as SELECT a.ACCOUNTID, a.DESCRIPTION from ACCOUNT")
    Set objrs = nothing
End Sub

Notice how the word SELECTSTATEMENT is gone, the SQL Statement is on one line only.

If you have more then one view being created you want to create a sub routine for each. So in the example of above where there is the FXCheckViews and FXCheckViews2, we want the sub FXCheckViews and another sub for FXCheckViews2. Together these would look something like this.

Sub FXCheckViews
dim objrs
    Set objrs = GetNewRecordset()
        objrs.Open "select id from sysobjects where name = 'vAccountDescription' and type='V'"
        if(objrs.eof or objrs.bof) then ExecuteNativeSQL("CREATE view vAccountDescription as SELECT ACCOUNTID, DESCRIPTION from ACCOUNT")
    Set objrs = nothing
End Sub

Sub FXCheckViews2
dim objrs
    Set objrs = GetNewRecordset()
        objrs.Open "select id from sysobjects where name = 'vContactType' and type='V'"
        if(objrs.eof or objrs.bof) then ExecuteNativeSQL("CREATE view vContactType as SELECT ContactID, Type from Contact")
    Set objrs = nothing
End Sub

Any good SQL script will work. This really opens up you ability to use SQL to organize your data for use in SalesLogix reporting or Group building.

Planning a SalesLogix Crystal Report

2009-09-09T20:23:00Z

One of the most common errors in developing Crystal Report in Sage SalesLogix is the failure to plan. A lot of times we just jump in without any fore thought and then spend a lot of time back tracking and making the report work after the fact.

I suggest the creation of a internal scope or blueprint document. Most consulting firms use scope documents to define projects, Why not use this practice internally? A scope document like I suggest is created by you for your end user. The document allows you and the end user a place to layout your ideas and agree upon them before you start on the report.

The bullet points below are from a document I developed for the Crystal Report Writing for SalesLogix class taking place at the end of this month. If you are familiar with the Business Object Crystal Reports - Report Creation Wizard, you will notice that this layout is set up so you can get a great start on your report.

Purpose - What is the Purpose of the Report? This might seem like a no brainer but this is really important to know and agree upon. This keeps you and your End User on the same page throughout the development of the report.

Report Name: - This is important for obvious reasons. There is three things come to mind when I think of a report name. The file name, the Report Header Label, and then name that appears in the SalesLogix Reports List.

Family - Does this report belong in the family of Account, Contact, Opportunity, History, Activity, Ticket, Contract, Personal. This is required for adding the report into SalesLogix. This is agreed upon by both so you know where to put the report and the end user will know how to find it. This is also a good time to think about Condition Filter fields for User and Date.

Fields - What are the fields that are important for this report. Start by using the interface names of the fields for the end user but be sure to look into the database and find the actual database name of the fields. You would be surprised how often a field is used for something it was not intended for especially in older systems that have been internally managed for years.

Tables - While you search for the field names keep a good list of the tables the fields are located in. Having a list of the exact tables you need really speeds up the process of creating a report.

Table Linking - How do the tables you have chosen above relate to one another? After you select the tables the next step is Linking. You need to know how to set up the links between tables before you can go any further in your Report Development. Then think about the Join Type (Inner, Left Outer, or Right Outer).

Layout - This is probably the most important one for the End User. They are your customers, if they do not like the layout, your development work will go on and on. Drawing out the layout in pencil will go a long way to making sure you are on track with your end users idea and set proper expectations of what the report will look like when it is done.

Grouping - How is your data organized? by Createdate? By first or last Name? By User? Or maybe all of these. Then you have to think about the order of the groups. Other things to think about, Does the group need to display or can you suppress it from view. If it is displaying then what fields do want visible supporting your grouping.

Summaries - Most Reports provide some type of roll ups. Whether it be counts, averages, or sums you need to really think and plan out summaries prior to building your report. The Crystal Report Writing Wizard can really make this easy for you if you are well prepared.

Record Selection Criteria - What data do you want to see? An example here is the History table, In SalesLogix, this table is filled with a lot of change to database records. Do you want to see those records in your report? If not what is the criteria you are going to use to eliminate those records from your report?

I hope you see my point, that a lot of planning with your end user goes along way. Taking the time to plan out the report to the smallest detail possible prior to sitting down and opening up Crystal will allow you to use the Crystal Report Creation Wizard to it's maximum efficiency leaving you with nothing to do but formulas and formating.

If you want to learn more about Developing Crystal Reports consider taking the Crystal Report Writing for SalesLogix class, registration is going on now.

I hope this post helps some one plan an upcoming report.

Geo

The Unknown SalesLogix Calendar Report

2009-09-03T14:12:00Z

I am going through and reviewing to make sure we have covered all of the standard Crystal Reports that come with Sage SalesLogix. I think I am almost done but I want to make sure I have covered them all. If I find any that are not covered I will make sure to write. Today we will discuss a standard SalesLogix capability that some people may not know about.

Recently a client, and a friend, contacted me about creating a Calendar Report for SalesLogix. He showed me an example of the report and I thought he was kidding me. I said, you know this report is already available for use in SalesLogix. He honestly did not know that the report existed so I thought I would write to try and ensure that others are aware of this valuable report.

To run the SalesLogix Calendar report, navigate to the SalesLogix "File" menu and select "Print Calendar Reports..." from the list.

This opens the "Print Calendar Reports" view, the fields selected in the picture above are set as the defaults. You have the ability to select Activities or History (or both) type of records. You can also multi select the Activity Types and the fields you want displayed in your report. In the center you have the ability to select a date range or use eleven standards such as "Today", "Year to Date" and the "Last Month". You also have the ability to report on other users calendars, but only the users that you are allowed calendar access to through the SalesLogix Administrator.

You can play around with what settings work best for you, the important thing is to know it is out there.

Here is an example:

FYI, this is not a Crystal Report that can be modified. Pretty cool capability thoung and valuable for that busy Sales or Service personal.

Remember to look for any of our updated reports for free download here under " Free SalesLogix Reports"

Also remember we have a class coming up. Crystal Report Writing for SalesLogix

Thanks for reading. Geo