Getting Business Portal reports emailed in a format other than Word

When you submit a report request in Business Portal and ask for the processed report to be emailed to you, for some reason Application Server sends the report to you in Word (.doc) format.  It seems like PDF would have been a more appropriate choice.

Here’s a trigger you can add to your system database that will override the default setting of Word and send the processed reports as a PDF attachment instead.

CREATE TRIGGER dbo.trgUpdateDistRptType
ON  dbo.AsrDistList
for INSERT,UPDATE
AS
BEGIN

update AsrDistList
set DistRptType = ’0′ — this is a zero – this font makes it look like a lower case letter o.
from AsrDistList  inner join inserted i on AsrDistList.RequestID = i.RequestID
where i.DistRptType = ‘W’

END

Printing reports from multiple companies

In response to another post, I got asked about automating the printing of a report from multiple companies. The answer was long enough that I decided to answer it in another post.

ROI.exe is the Report Options Interpreter.  When you click on a Report in the SL menu – this is the screen you see.  When it’s called from the menu, ROI is passed a command line parameter that is the 5 digit ReportNbr. (01610 is the Trial Balance report in GL – I’ll use that for this example.)  The ReportNbr is the primary key to the RptControl record in the system database.  RptControl defines the “questions” that ROI asks you prior to printing a report.

When I say it defines the questions, here’s what I mean. You can configure RptControl to not prompt for a period, to prompt for 1 period, or to prompt for a period range (beginning and ending period).  This field value in the RptControl record controls what input controls are visible to the user when ROI is loaded with a particular ReportNbr.

Through the command line parameters to ROI.exe, you can control a lot (but not all) of what the user can do interactively with the ROI screen.  When you click a button on an SL screen and a report print preview window pops up (Solomon used to call this a Quick Print), the code behind the button click event called ROI with command line parameters.  The details were passed in via command line so that ROI didn’t need to become visible to prompt the end user, so it went straight to the Print Preview screen.

Before getting into the specifics of multi-company reporting, I need to mention some details about SL does multi-company.  You can define multiple companies in multiple databases or you can define multiple companies in one database (or a mixture of the two). The only multi-company reporting that ROI can do is when multiple companies are defined in the same database.

If Company ABC and Company DEF are both in the same application database, when you run a report using ROI, logged into either ABC or DEF, you will be prompted to run the report for company ABC, or DEF, or both on the Company Selection tab of the ROI screen.

You can’t supply Company Selection information via the command line arguments to ROI (at least it’s not documented and I don’t know how to do it), but you could add CpnyID = ‘ABC’ to the Where clause using a command line parameter.

The way that ROI communicates the “answers” that the end user provides to the “questions” that the RptControl record told ROI to ask the end user is by creating an RptRuntime record.  When you click Print or Print Preview, ROI saves your selections in a new RptRuntime record, gets the RI_ID value of the record it just added and passes that RI_ID value to the Crystal Reports print engine (via the RIPARAM(“RI_ID”) function).  After the print job has finished printing, or when the Print Preview window is closed, the RptRuntime record is deleted.

If you click Print Preview and minimize the Crystal Reports preview window, you can query RptRuntime and you will see the record (and some others if other people are running reports at the same time).  Notice the RI_WHERE field of RptRuntime. This is how ROI passes Select tab information to Crystal.  You can manipulate this value either through a database trigger or a pre-process and change what values are being sent to Crystal.  (The company selections are saved in another table – RptCompany, but it can also be manipulated via a database trigger or a preprocess.)

If you are needing to run the report for multiple companies that are defined in different application databases – you will need to log in and out of the different databases. This sounds like you want to use the SL Object Model, which turns the SL screens into COM servers that you can “drive” by using COM objects in your code.  This will let you login and out of companies and then you can call ROI programmatically from the correct SL session.

I will also mention that you may want to look at Application Server to automate reports.  It’s expensive, but if you are submitting the report requests from Business Portal, you can use Application Server without licensing it.  If you are on SL Professional (or Business Ready Licensing) – you already own Business Portal but are probably just not using it.  It’s is a SharePoint-based portal to let you view SL data and run SL reports through a web browser.  From Business Portal, you can submit recurring requests that will automatically re-run a report as often as you specify without having to re-submit requests everytime you want this to happen.

I hope this helps answer the question.

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.

Windows Authentication in SL 7 SP1

We have started to use the Windows Authentication feature of SL 7 and there has definitely been a learning curve.  This article is more of a “this is how I think it works” than “this is how I know it works”.

Historically, Solomon has always connected to SQL Server using SQL authentication (making it necessary to change from the default Windows authentication only that SQL Server defaults to during install, to mixed mode – so that both Sql and Windows authentication are visible).  For years the sql login used was “master” and, by default, it had a blank password.  (Most of the time when I’ve come in to a Solomon installation, it was still blank.)  With SL 6.0 SP1, Microsoft’s security team seemed to get more involved and some holes were plugged.  A new login was created – Master60sp and it wasn’t allowed to have a blank password.  That’s still the login that’s used, if you aren’t using Windows Authentication.

I think some changes were made to the Windows Authentication model with SL 7 SP1 but I can’t elaborate more than that because I don’t really know the details.  We first started using Windows Authentication about the time that SP1 came out.  We had a couple of installations on Windows Authentication for about a week before upgrading them to SP1 and it seemed to make some changes. I also saw several posts on Partner Forum about SP1 affecting Windows Authentication.  This article is about how it works with SP1. If it worked differently prior in version 7 with no SP, I don’t know those details.

After you have put the database in Windows Authentication mode, you have to assign SL users to their corresponding Windows user.  If needed a Windows login will be made on the SQL server for that Windows account and, when that person logs into SL, their database connections will be made via that Windows login.

However, no permission are given to that account and it does “inherit” permissions via group membership.  Instead, Solomon uses a SQL feature called an “application role”.  This lets the users have the permission they need while they’re logged into Solomon, but they won’t have the ability to delete records via a query in Management Studio, for instance.

The application role (MSDSL is the name of the role that SL has created) is connected to via the sp_setapprole stored procedure.  Now the connections get the permissions assigned to the role.  The SL screens connect this way, but the Crystal Reports don’t.

SL screens don’t use ODBC to talk to the database, but Crystal Reports does.  Crystal still connects using SQL authentication.  If you look at the Logins node in Enterprise Manager for your SQL Server, you will see this login – E7F575915A2E4897A517779C0DD7CE.  SL Installation creates this and that’s the login it tells Crystal to use to connect to the database to run reports.  If you look at most of the objects in the database and look at their permissions, rights have been granted to the MSDSL application role and to the E7F575915A2E4897A517779C0DD7CE login.

FRx presents another problem.  If you haven’t upgraded to FRx6.7 SP10, it doesn’t know about these changes and will still try to connect via “Master60sp”. I would upgrade to Service Pack 10 and then it can connect using Windows Authentication.  The problem is – FRx uses ODBC as well and now it’s expecting to connect via Windows Authentication and Crystal Reports is trying to connect using SQL authentication.  If you change the ODBC DSN’s to use windows Authentication, you will typically get an ROI error (usually something about CRPEHLPR) when you try to run the report.  This is because the windows login for the user doesn’t have any specific permissions on the tables, views and stored procedures, and Crystal isn’t connecting to the application role.

What I’ve done to get around this, is to create a 2nd DSN pointing to the same database and let FRx use that one.  Let the DSN whose name is the same as the database be configured as SQL authentication, because SL is going to use that one for Crystal Reports.  You can tell FRx to use the 2nd one (configured with Windows authentication) by editing the Company configuration in FRx.

For people customizing the SL system (creating new tables, stored procedures, views – even triggers) if you aren’t aware of the new authentication system – your users can get errors.  In the past Master60sp (and Master before that) was the database owner (dbo) so you didn’t really have to worry about SQL permissions, but now – you need to make sure that the MSDSL application role has permission to your object (as well as E7F575915A2E4897A517779C0DD7CE, if you need to access that object from Crystal).

Initialization Mode

I was talking with someone the other day and it occurred to me that Initialization Mode is kind of a fuzzy concept for some people, so I thought I’d post a quick explanation.

Initialization Mode means different things to different programs.  When an SL screen is launched after Initialization Mode is turned on, an indicator is turned on telling the program that it is operating in Initialization Mode.  It’s up to the programmer writing the screen to decide what to do with that.

In many cases, it probably doesn’t do anything. (It doesn’t do anything, by default.)  If I want my VB Tools (or SL SDK, since that’s the new name) program to behave differently in Initialization Mode, I have to do something like this in the code.

If bPes.InitMode = True Then
    ‘Do special initialization mode behavior
Else
    ‘Do regular behavior
End If

The bPES buffer (if anyone ever told me what PES stands for – I can’t remember) is filled up by SWIM when the program is launched and it contains lots of useful runtime information – current user, current cpnyid, sql server name, etc.

Just like any other buffer that has been registered with the kernel via SetAddr, bPES is available in Customization Manager code as well – you just have to access it via the GetBufferValue statement.

Adding an ID field’s description to a grid through VBA

Typically SL screens will “decode” a transaction’s ID value into a description in form view, but not in grid view.  Here’s what I mean:

In this screenshot (grid view), the account description isn’t shown.

But in form view (below), a label has been added to show that Acct #4030 is the Merchandise Purchases account.

 

One of the most frequent customizations that our clients would request was to add the Account Description to the grid, next to the Account column.  This column will show how to do that. (It will use the Voucher screen as an example, but this code pattern can be used to show any ID field’s description.

We are going to need to load the Account Type into our customization because we are going to call a SQL query (basically select * from Account where Acct = @parm1 where @parm1 is the Account ID on the current line in the grid).  To hold the results of the query – we need an Account buffer, so we’re going to load Account.DH into our customization.

Go into Customization Mode and open the Visual Basic editor. Right click on the Modules node in the Project Explorer and choose Import File.  Navigate to the DB\DHFiles\App subfolder under your SL folder and pick Account.DH.  (It won’t show up until you change Files of Type to All Files.)

Since the Voucher screen already has a bAccount buffer, the new one needs to be bAccount1.  At the bottom of the imported file – change bAccount to bAccount1 and nAccount to nAccount1.

In the Form_Load event, add this code:
    Call VBA_SetAddr(“bAccount”, bAccount, nAccount, LenB(bAccount))

Exit Customization Manager, save your changes, close the screen and re-open it. (This is required to get the code you just entered to run. You need it to run to get the bAccount buffer loaded into memory.)

Start Customization Mode again and open the Insert Object Wizard.  Select TextBox and name it xAcctDescr and click Next.  Pick the Account1 table from the list on the next page and click Next. On the next page, pick the Transaction level so that the textbox will go in the grid. Click Next and choose the Descr field and click Apply.  Then Close the wizard.

Drag the newly added TextBox to the grid area and re-size it and position it as desired.  This is where it will show up in Form view.  Set the TabIndex to be 1 higher than the TabIndex value of the Account text box. This will make it appear to the right of the Acct column in grid view.

Now go back into the Visual Basic editor and add this code at the very top of the window (above any Functions or Subs):
Dim iGridHandle As Integer
Dim iMaintFlg As Integer

In the Form_Display event, add this code:
    iGridHandle = GetGridHandle(“Spread1″)
    serr1 = VBA_Mextend(iGridHandle, bAccount1, LenB(bAccount1))

If you exit (and save) from Customization Mode and close and re-open the screen (to get the newly added Form_Load code to execute) – you will see the new column to the right of the Account column. (Notice the column heading – you probably want to change that.) At this point, you’ve added the column, but nothing is in it.  You have to get code to run the query to fill up the Account1 buffer with the appropriate record values.

In the crefnbrh_Chk event, paste this code:
    Dim strAcct As String
    serr1 = MFirst(iGridHandle, iMaintFlg)
    Do Until serr1 = NOTFOUND
        strAcct = GetObjectValue(“cAcct”)
        serr2 = SqlFetch1(c2, “Account_All” + SParm(strAcct), bAccount1, LenB(bAccount1))
        Call MUpdate(iGridHandle)
        serr1 = MNext(iGridHandle, iMaintFlg)
    Loop
    serr1 = MFirst(iGridHandle, iMaintFlg)
    Call MDisplay(iGridHandle)

When you navigate to a voucher, this code will fire and will loop through the grid, getting the loading the right Account record into the bAccount1 buffer, which will be shown in the new grid column.

To get the description to show up after the user edits the account on a grid line, paste this code into the cAcct_Chk event:
    serr2 = SqlFetch1(c2, “Account_All” + SParm(ChkStrg), bAccount1, LenB(bAccount1))
    Call DispFields(“Form1″, “xAcctDescr”)

Leave Customization Manager, save your changes and pull up a voucher batch.  You should see the account descriptions now.

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”).

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

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

When a user runs a Crystal report from Solomon, they are actually calling ROI.exe. It looks like this.  (You’ve seen it before but may have not known its name.)

In the picture, a user has clicked Trial Balance from the Reports menu of the GL module.  If you were to click the down arrow on the Report Format drop-down, there are actually 2 Trial Balance formats – Combined Totals and Debit and Credit Totals.  (This is how the Trial Balance comes out of the box.  If you get different results, your system has been customized, but the principles are the same.)

One of the things that can be confusing about SL reporting is that the word report doesn’t mean the same thing in Solomon as it does in Crystal.  From SL’s point of view, I would describe the Trial Balance as 1 report with 2 formats.  If you choose the Combined Totals format, the Crystal report file that will be run is 01610a.rpt.  If you choose the Debit and Credit Totals format, the Crystal report file that will be run is 01610b.rpt.  So 1 Crystal report (or more specifically 1 Crystal report file) doesn’t map to 1 SL report, but rather 1 format of an SL report.  Each SL report can have 1-8 formats defined by 1-8 Crystal report files.  (Solomon reports usually print the name of the Crystal report file in the top right corner of the page header with the caption Report.)

Report Control Maintenance screen

Report formats are defined by using the Report Control Maintenance screen, which can be found on SL’s Utility menu.  If you open this screen and type 01.610 in the Report Number field and TAB out of the field, you will see the Format names listed on the left and the corresponding Crystal report file names listed on the right.  The formats defined on the left side correspond with the values in the Report Format drop down on the ROI screen.   There are more options here, but we’ll cover more in the later posts.

If you look at the Delta.mnu file and search for the Trial Balance line, you’ll see it looks like this:

        Trial Balance, ROI.EXE ; 01610

There’s that 01610 number again.  That’s the same value you typed in the Report Control Maintenance screen to pull up the Trial Balance format information. It’s also shows up in the title bar of the ROI screen (image above).  What exactly is that number?

RPTControl table

There is a table in the System database named RptControl and this value represents the primary key of that table. (The field name is ReportNbr.)  If 1 Crystal report file represents 1 report format on a particular SL report, 1 record in RptControl represents 1 SL report.  In fact, it’s probably better to say that the record in RptControl defines the SL report.

Here’s the way it works.  When you click on the Trial Balance menu item in the SL menu, ROI.exe is called and passed a command line argument of “01610″.  When ROI loads it takes the value passed in and reads that RPTControl record (select * from RptControl where ReportNbr = ’01610′), and populates the Report Format drop down list based on the values it finds in the fields ReportFormat00 – ReportFormat07 and ReportName00 – ReportName07.  (It actually does a lot more here, but we’ll save that for a future article.)

You might expect report formats to be stored in another table with a 1 to many relationship between RptControl and the format table, but that’s not how it actually works.  Instead there are 8 pre-defined “slots” in the RptControl record and the format name (e.g., Combined Totals) is stored in the ReportFormatXX field and the Crystal report file name (without the “.rpt” is stored in the corresponding ReportNameXX field.

Usr_Rpts folder

There is no path stored in the ReportNameXX field.  ROI finds the Crystal report file by looking first, in the Usr_Rpts folder, and if it doesn’t find a matching file there – it looks in the appropriate module folder.

Let’s talk about the module folders first.  Every SL screen has a 7 digit screen number that is usually displayed like this (00.000.00) in the window title.  Every SL report has a 5 digit report number (01.610 is the one for Trial Balance).  Notice they are segmented into 3 and 2 sections respectively.  In both cases (screens and reports), the first 2 digit segment represents the module.  01 is General Ledger, 03 is Accounts Payable, 08 is Accounts Receivable, etc. 

In the folder on the file server where the SL programs are installed, there are many 2 character folders under the main folder. Most of these are module folders – “GL” is General Ledger, “AP” is Accounts Payable, etc.

Based on this, you would expect to find the Crystal reports for the Trial Balance (which is a GL report because its report number begins with 01) in the GL folder.  The “out of the box” versions are there.

Getting back to the Usr_Rpts folder – if you customized one or both of the Trial Balance Crystal report files and then did an SL upgrade, the upgrade process would overwrite your customized version of 01610a.rpt with the SL version.  That’s the purpose of the Usr_Rpts folder.  SL won’t ever put anything there.  If you customize 01610a.rpt (or any other report), save your changes in the Usr_Rpts folder and it will survive a customization.

When ROI is looking for the correct file, it looks first in Usr_Rpts to see if you have saved a customized version. If you have – it uses this file.  If not, it gets the “out of the box” version from the GL (in this case) folder.

Much more details to come, but that’s enough for Part 1.