Thoughts from Convergence 2009

 

Just got back from New Orleans today. I’ve been there for the last week attending Convergence, Microsoft’s conference for its Dynamics family of business applications.

Most of the SL discussions were about SL 7 Feature Pack 1, Sharepoint/Business Portal integration, and SQL Server Reporting Services (SSRS).  I’ll have more about FP1 and Business Portal in future posts, but wanted to share some of the details about SL and SSRS.

If you’re not familiar with SSRS, Microsoft first released SSRS for SQL 2000.  It wasn’t in the box but was released later as a free download.  It was included (with many improvements) with SQL 2005 and is even better in SQL 2008. 

Starting with Visual Studio 2005, Microsoft included a ReportViewer control.  This lets a programmer use the same reporting technology, but is processed on the client and doesn’t require a server to process the reports.  From a technological point of view this is good news, but (in my opinion) Microsoft marketing has dropped the ball here because they haven’t given us a good name to refer to this by. ReportViewer is too generic a name, so people tend to still refer to this as “SQL reporting” even though it can be used in scenarios where there is no SQL Server in sight (e.g., a VB program accessing an Oracle database).

Starting with version 7, Dynamics has added support for SSRS.  Historically, all the reports run from Solomon/SL were written in Crystal. Users have always run reports by opening the ROI screen.

 

 

After the user clicks Print or Print Preview, ROI communicates with the Crystal Reports print engine to print or display the report.

Version 7 includes (in addition to ROI.exe) ROISRS.exe to allow SL to print SSRS reports.  SL 7 includes, in addition to the standard Trial Balance report that uses Crystal reports, an SSRS report labeled as “Trial Balance (SSRS)”.

 

When the user runs the SSRS version, it looks like this:

When I watched the demo of how to use SSRS with SL 7 at Convergence, something didn’t make sense to me.  The steps that were outlined there were:

1. Design an SSRS report using Business Intelligence Development Studio (BIDS – comes with SSRS).
2. Copy it to the Usr_Rpts folder of your SL installation.
3. Add it to the Screen table using the Screen Maintenance table. (Prior to version 7, the only “type” options in the screen table were Screen and Report.  Version 7 adds an SRS Report option.  When you set this up as an SRS Report – SL knows to load ROISRS, as opposed to ROI.)
4. Add it to the RPTControl table.
5. Add it to the Menu and restart SL to see the new menu item.
6. Pick the report from the menu and run it.

Simple enough, but that’s not typically how you add a report to SSRS.  Normally you need to deploy it to the Report Server.  Copying it to the Usr_Rpts folder isn’t the same as deploying it.

Turns out – SL isn’t actually using SSRS, but is actually using the ReportViewer control and calling it SSRS (see how confusing this is).

What this means is that the SQL Reporting service doesn’t need to be running anywhere to allow a user to run (for instance) the Trial Balance (SSRS) report.  So when you are told that you need to install SQL Reporting Services – you really just need it for BIDS to design the report.  Once you’ve created the RDL file (similar to an RPT file when using Crystal), SSRS isn’t used any more because SL actually uses the ReportViewer control which was installed with the SL 7 client.

Of course, you can also use SSRS to report on SL data without adding the reports to the SL menu.  Many people are doing this.  The good news about this is that you don’t have to be on SL 7 to do this.  In this scenario, SSRS has no knowledge of SL but is just reporting on data that happens to be in a SQL database.

One of the common demos that I saw at Convergence was where an SSRS report had been embedded in a SharePoint Report Viewer web part on a Business Portal page.  This is very cool.  One gotcha to watch out for is that the web part isn’t Business Portal aware so that if you are in a multi-company scenario and design the report to pull data from Company1 – when you deploy it, it will always pull from Company1.  If you select Company2 in the drop down box on the top right of the Business Portal page, it will have no effect on the report displayed in the Report Viewer.

SL Requisitions emailing feature – here’s what we came up with

We’ve been working for awhile on getting Requisitions (and Item Request via Business Portal) implemented for a customer.  There’s a lot of things about the Requisitions module that didn’t work the way I expected them to, but this article is going to focus on the emailing agent (Requisitions Information Agent or RQ.RIA).

There’s just really not much there.  Here’s a quick list of shortcomings/complaints:

• Uses the “Process Manager” approach of needing an unattended workstation, logged into an SL session instead of being implemented as a Windows service
• This is even worse if you are implementing across multiple companies in different application databases, because then you need 1 instance per database.
• Uses MAPI to send email so you need a configured MAPI (Outlook) profile which means you have to deal with Outlook saying “A program is trying to send an email on your behalf…”
• The content of the email isn’t customizable.
• The email doesn’t say which company you have pending approvals in, so that if you are running multiple companies – you have to log in to one company after another until you find the one waiting for you.

Our (Westglenn) first crack at dealing with all this was to write a Solomon Object Model program that would loop through each of this client’s 10 company databases.  For each company, the program would log in to SL, fire up RIA, let it run for a little while and then shut it down, log out of SL and move to the next company.  We had some typical Object Model issues getting this to run for more than a day or so, but we eventually got that to be pretty stable.

Then, we needed to use ClickYes to agree to letting Outlook send a message on our behalf.  To avoid having a dedicated computer tied up doing this, we tried to run this in a Terminal Server session that would stay disconnected most of the time.  It ran OK when we were connected to the session, but no messages were sent when the session was disconnected.  As soon as we reconnected to the TS session, we would see several dialog boxes and the messages would go. 

We finally figured out that Windows is smart enough to know when a session is disconnected and doesn’t bother to display a MsgBox in a disconnected session so there was nothing for ClickYes to click.  As soon as the session was reconnected, the MsgBox’s would appear, ClickYes would click OK and Outlook would send the messages.

At this point, I was tired of fooling with this, because all that RIA is doing is reading some database records, updating some database records and sending email messages.  All of this could easily happen in a Windows service (no real requirement for SWIMAPI or any of the usual SL client stuff) so – that’s what we did.

Early testing is completed and it works pretty well.  It runs as a service, can handle multiple companies from one instance and the message content can be customized.

We are going to see about turning this into a product if there is any demand for it.  If anyone has any interest in this and would like to be a beta tester, please contact me at jtrotman@westglenn.com.

Cash Manager gotchas

We just finished getting a customer issue with Cash Manager resolved and it was an educational process.

Here’s a simplified fictitious version of their issue to demonstrate the issues we uncovered:

They had been running SL for awhile prior to implementing Cash Manager so there were already AP checks and AR payments on an account-subaccount combination that was configured as a Cash Account when Cash Manager was implemented.

They wanted to “go live” with Cash Manager on January 1st so we were going to do a bank reconciliation as of Dec 31 to clean up all prior activity and to make the necessary adjustments to get the Cash Manager balance correct as of January 1st.

There was one outstanding check (as of Dec. 31) that was written on Dec 1 for $500.  The “checkbook balance” at the end of Dec 31 was $10,000.  The balance on the bank’s web site at the end of Dec 31 was $10,500 due to the outstanding check.

We set the Accept Transaction Start Date in Cash Manager Setup (CASetup) to be Dec 1 so that Cash Manager would “recognize” the Dec 1 outstanding check.  We went into the Bank Rec screen and entered a new line with a Reconciliation Date of 12/31 with a statement balance of $10,500.  We cleared all the other Dec checks and deposits (except the 1 $500 check) so we were left with an Adjusted Balance of $10,000.  We entered a Cash Transaction (type OD to not affect GL) for $10,000 dated 12/31 to get the Cash Manager balance to $10,000.  Since the Adjusted Balance now matched the Cash Manager balance, we checked the Reconciled checkbox.

The first observation we made was that the Dec checks and payments had no corresponding CashSumD records because they were entered and released prior to Cash Manager being installed.  The only CashSumD record at this point reflects the $10,000 OD transaction.  The Daily Cash Balance screen gets the current balance by adding up all the CashSumD records (select sum(receipts – disbursements) from CashSumD).  (Interestingly enough, the Cash Manager balance on the Bank Reconciliation screen isn’t calculated exactly the same way, but more on that later.)  So far, so good.

But if you run an Integrity Check back to 12/1, it will create CashSumD records reflecting the December AP checks and AR payments.  All of a sudden – the Daily Cash Balance is complety different than it was, since there are more CashSumD records.  Initially we needed a positive $10,000 adjustment to get the Cash Manager balance as of 12/31 correct. Now that there is December activity reflected in CashSumD, the necessary adjustment to get the beginning balance right is completely different.

Based on this, I would recommend making adjustments to get the beginning balance right as of the Accept Transaction Start Date as opposed to some time after that.  If you do adjust to get the balance right at a later period, you probably don’t want to run an Integrity Check that evaluates activity prior to your beginning balance point.

The other thing that we realized was that the Bank Reconciliation screen doesn’t calculate the Cash Manager balance as of a particular day the exact same way that the Daily Cash Balance screen does (when you click the Balance by Date button).  The Daily Cash Balance as of a particular date is basically select sum(receipts – disbursements) from CashSumD where trandate <= @Date where @Date is the date you want the balance for.

When the Bank Reconciliation screen calculates the Cash Manager balance (when there has been a previous Bank Rec), it goes to the previous Bank Rec record and calculates what the cash manager balance was at that point and then only considers CashSumD records between the previous recon date and the new recon date.

What this means is – if you “backdate” any transaction affecting cash (CA, AR, AP, GL) to a date earlier than the last completed Bank Reconciliation, that transaction’s affect on the Cash Manager balance won’t be recognized by the Bank Rec screen when calculating the Cash Manager balance as of the new Bank Rec.

If you have reconciled a Cash Account as of 11/30 and subsequently enter a transaction dated 11/15, when you try to do a Bank Rec for 12/31 – the Bank Rec screen’s calculation of the Cash Manager will be different than what you will see for that date in the Daily Cash Balances screen when you click the Balances by Date button.  Once you have this situation, it will be hard to get it resolved without a very good understanding of Cash Manager operation.

(Blatant commercial here – if you do have this situation and would like help getting it resolved – contact Westglenn‘s support department by emailing support@westglenn.com or calling (205) 307-6555.

Dynamics SL (Solomon) Reporting framework (ROI.exe) – Part 2

This article contains details relevant to versions up to 6.5. A future post will explain changes introduced by 7.0.

RPTRuntime record

How does SL communicate the choices you make on the ROI screen to Crystal?  By creating record in a table named RPTRuntime.  When you click Print or Print Preview in the ROI screen, ROI inserts a record into the RPTRuntime table and stores the values you entered into (or the default values from) the ROI screen.

Here’s a simple example.  In the General Ledger module, click the Trial Balance menu option in the Reports section and click the Print Preview button on the ROI screen (just accept all the default values).  While the Crystal Print Preview window is still open, run this query on your system database – select * from RptRuntime.  You should see at least one record. (If other users in your system are running reports at the same time, you will see more than one record. If there are multiple records, you should be able to find yours by looking for a record that’s UserID field value is the User Id that you’re logged on to SL with and the ReportNbr field value will be 01610.)

Notice that the values in the BegPerPost and EndPerPost fields are the values from the ROI screen.  Close the Print Preview window and re-run the query.  Notice that the RPTRuntime record is gone.  ROI deletes the RPTRuntime record it inserted either when the print job is finished or when the Print Preview window is closed.

Now enter different values for the Beginning and Ending Periods in the ROI screen and click Print Preview again.  If you re-run the query, you will see a new RPTRuntime record with the new values you just typed in the BegPerPost and EndPerPost fields.

This is how ROI communicates the SL users selections to Crystal – by storing the values in the RPTRuntime table.

 RI_ID value

I mentioned earlier that there can be multiple records in RPTRuntime at one time, if multiple users are running reports at the same time.  How does Crystal know which record is yours? 

The primary key of RptRuntime is an integer field named RI_ID.  When ROI inserts a record into RptRuntime, it specifies a unique RI_ID value.  It then passes that RI_ID value to Crystal. (Technically, it passes it to CRPEHLPR , but that’s beyond the scope of this post.)

User Function Libraries (UFLs) in Crystal

Crystal Reports includes support for User Function Libraries.  This always 3rd party developers that use Crystal (like Microsoft Dynamics) to write new functions that can then be used in formulas in a Crystal Report.  Dynamics SL ships with several of these.  One of the most-used UFL functions used by Dynamics SL is called RIPARAM.  RIPARAM takes 1 parameter – the name of a field in the RptRuntime table.

If you want to use the Beginning Period to Post value specified by a user (like we discussed earlier with the Trial Balance), you could call RIPARAM(“BegPerPost”).