This post is a technical post. I should warn you that if you do not do report writing or technical work with Dynamics SL you may fall asleep after the second sentence of the next paragraph. Please forward the link immediately to your more technical counterparts if you are in danger of a slumber episode. You should be able to make it beyond the bullets below regardless of your technical abilities or your attention span.
By and large, the process for adding an SSRS report to the Dynamics SL 2011 menu is the same as adding any other report to the SL menu.
- First you need to create the report, which gives you an RDL file in the case of the SSRS report instead of the RPT of a Crystal Report.
- Copy that file into the Usr_Rpts directory (in either case).
- Add the Screen record and the Report Control record for the new report (using those two screens).
- Add the report to the Menu exactly as a normal report, but change the program from ROI.EXE to ROISRS.EXE.
- Last, make sure to tab off the field before saving the change or the system will lose the alteration.
- After granting rights to the report, it will be available to run from the menu.
Report calls from within code
Running reports programmatically is a more interesting topic. This is done with a call to CallApplic, CallApplicWait, or Launch to run ROI, passing a string with the list of parameters. There are more options for what can be done with this command than is generally known. The parameter string consists of a series of elements separated by tab characters (PRMSEP is used to insert that value into the list).
The elements that make up the list allow for a number of options. Some of them can be stacked freely; others will be overridden by other options. The order of elements in the parameter string does not appear to matter, but the list is presented in the order they will generally be placed.
/RUN This element is necessary. It tells the system which report is to be run. Example: “01650/RUN” would be used to run the chart of accounts report.
/FORMAT This element will also almost always be included. It tells the system which format for the report should be run. When not filled in, the first format for the report will be used. Example “01650/FORMAT”
/WHERE This element allows additional filters to be added to the report. It will override the where clause for the /TEMPLATE being used (if any). One change for version 2011 is that the field used in the filter must be expressed as {<table>.<field>} (earlier versions did not need the curly braces). Date values now need to be either denoted as Date(<year>, <month>, <day>) or in the form #<month>/<day>/<year>#. In statements are similarly slightly altered, using brackets ([]) instead of the parenthesis used by T-SQL. Other differences also exist – the expressions must be in crystal format instead of standard SQL. Example “{Account.Acct} < ‘3000’/WHERE”
/PSCRN This element has no data component. It is used to make the report print to the screen. It will be overridden if /EXPFILENAME is used.
/DEBUG This command can be used to check certain information concerning the report being run. It will put into a debug file the RI_ID, indications that the preprocess is running, the option to print or not print the report (after all processing complete), the report query just after opening the report, report table names before and after being changed, and printer information. You must create a file Debug.tlb in the DynamicsSLApplications folder first.
/TEMPLATE This element allows the specification of a template to be used. Its selection element will be overridden by /WHERE in the parameter string or used if no such clause is included. Example: “SKIPASSETS/TEMPLATE”
/RPTDATE This element allows specification of a report date. When it is not included, the current business date is used. Example: “02/01/2013/RPTDATE”
/EXPFILENAME This element allows a file name to be specified for the report to be exported. No spaces are allowed in the name. This will override the /PSCRN element. This element is used in conjunction with the /EXPFORMAT element. Example: “C:V70TestRptPrgbinTest.pdf/EXPFILENAME”
/EXPFORMAT This element specifies the format for the file exported. If this element is not specified, a text format file is produced. A list of the available formats follows. Example: “0/EXPFORMAT”
/BATCH This element can only be used if the report calls for a batch number. It would be used to specify a batch for those reports that require that value. Example: “123456/BATCH”
/DOCNBR This element can only be used if the report calls for a document number. It is used to specify a document for those reports that require that value. Example: “123456/DOCNBR”
A full example to print the chart of accounts report for accounts less than 3000 to screen would be:
Call CallApplicWait(“ROI”, “01650/RUN” ‘+ PRMSEP + “01650/FORMAT” + PRMSEP + “{Account.Acct} < ‘3000’/WHERE” + PRMSEP + “/PSCRN”)
A full example to print the chart of accounts report to a file in pdf format would be:
Call CallApplicWait(“ROI”, “01650/RUN” ‘+ PRMSEP + “01650/FORMAT”+ PRMSEP + “C:V70TestRptPrgbinTest.pdf/EXPFILENAME” + PRMSEP + “0/EXPFORMAT”
Format codes for the /EXPFORMAT lines:
0 pdf (acrobat)
9 xml
A rich text
B comma separated
C crystal reports
T tab separated values
W word
S excel 97-2003
K excel 97-2003 (data only)
X text
Y tab separated text
U report definition
If you made it to the bottom of this post-you are one of the few, the proud and should take a bow. You are probably either working for a Dynamics SL partner or you are an in-house Dynamics SL blackbelt for a large Dynamics SL client. Congratulations.
