T-SQL Statement to Add Report to Screen File

Here is the code that will add a report into the Screen Table in your system files.

You will need to change the information in the VALUES statement to your information.

I ran this in the Query Analyser for SQL Server 2000, and it ran fine. Just remember to select your Dynamics System Database from the drop down box on the menu bar.



INSERT INTO Screen(Module, Name, Number, ScreenType)
VALUES('PA', 'Equipment Rates', 'PA997', 'R')

Dynamics SL Reporting (where ROI is not ROI)

If you have a degree in Finance, get ready for the jolt of your life. In Dynamics SL, ROI (Return on Investment) has been changed to ROI (Report Options Interpreter). Microsoft (aka god of all software), has changed what the initials stand for, so don't fight it - just deal with it.

Since Dynamics rollout, I've been working on creating custom reports. In the process, you need to learn about the ROI and Crystal Reports. Personally, I've always hated Crystal, and for the past couple of years, I've avoided working with it; kind of like I avoid jumping off tall buildings.

And if you're like me and are used to a Microsoft Report writer like the one in MS Access, let me forewarn you, Crystal will have you crying like a baby and running to Momma for comfort. Crystal is just not as easy to use as the stuff from Microsoft, which is probably why Crystal is not the "god of all software."

Creating a Custom Report is difficult. Here are the steps:

1.) Create the Report in Crystal (try not to lose your religion by cussing the aggravating program).

2.) When you've got a warm fuzzy that all is good with your report, save it in the Usr_Rpts folder in the Solomon directory.

3.) When you save it, make sure that you follow the naming convention for the module you'll be working with. For instance, I've been creating reports for use in the Project Controller, so my reports begin with PA (i.e. PA994.)

4.) Edit the text file "Delta" in the Solomon folder. BE SURE TO BACK UP THE FILE FIRST before editing it. I don't know what happens if you corrupt the Delta file, but I'm sure it's NOT GOOD. So be safe, and back up first.

5.) When you add the line in the Delta folder, be sure to EXACTLY follow the format of the other lines in the folder. Here are some of the lines I added to my Delta file:

Equipment Rates, ROI.EXE ; PA996
Equip. Costs w/ Detail, ROI.EXE ; PA994
Total Equip Hours & Costs, ROI.EXE ; PA993
Project Costs, ROI.EXE ; PA995

Beware: the lines in Delta start with spaces and NOT TAB stops. Trust me, if you use Tabs instead of spaces, it won't be good for you (I know, I did try this one). Somewhere deep in the documentation, this fact is hidden; but I'm giving you this tip for free.

6.) Now you need to add a line into the Screen Table in the Dynamics System Files. For me, I have application data in a seperate database from the system database, and my databases are named: AlphaApp and AlphaSystem. I can't tell you what yours are named.

I added the following line in the Screen Table:

Module Name Number Screen Type
PA Equip Costs w/ Job Detail PA994 R

You can add this line to Screen Table by using T-SQL and the Query Analyser, or you can be lazy and just open Screen and add the information as a new record. However, you get extra cool points if add it through Query Analyzer.

7.) Now log into Dynamics as an administrator and add your new report to Utilities/Report Control Maintenance (98.300.00).

8.) And finally give users access to the report through Utilities/Access Rights.

John