Calling the Viewer exe as a system command - majorsilence/Reporting GitHub Wiki

Calling RdlReader.exe directly from your program

For the simplest way to present a report to the user, it is also possible to call the viewer (RDLReader.exe) via a system/Shell call, passing it the filename of the required report - like any other command that can be issued from the command line (or batch file, Powershell, any other language etc) :

The Viewer starts up in its own maximised window, with a progress indicator while it generates the report. It then displays the results, with the option of printing or exporting to a file in a number of formats (PDF, XML, HTML, CSV, RTF, TIF, Excel xlsx, MHT).

This gives you the power of RDL based reports in just a line or two of code, with both the report designing stage and the viewing/printing of reports taking place outside of the programming environment or application. This is ideal as an introduction to RDL reporting, and as a bonus the knowledge gained in the process may one day be useful if you progress to the full blown MS Server based reports system.

Examples

DOS command line
C:\Fyi> RdlReader.exe C:\Fyi\myreport.rdl

VB.net

Dim ID As Integer  
ID = Shell("""C:\Fyi\RdlReader.exe"" C:\Fyi\myreport.rdl", , True, 100000)

Lazarus (Free Pascal)
(wiki documentation for Executing External Programs)

// uses : ShellApi
if ShellExecute(0,nil, PChar('"C:\Fyi\RdlReader.exe"'),PChar('"C:\Fyi\myreport.rdl"'),nil,1) =0 then;

Python

from subprocess import Popen
new_process_obj = Popen([r'path\RdlReader.exe', r'path with spaces ok\report.rdl'])

What do I need to install on my users' machines?

There are only a few files in the provided full ZIP file download of executables, not many are needed for report viewing. You could just distribute a MyFyiRpt folder containing everything, but remove all the .exe files except the RdlViewer.exe - leaving the viewer, the .dll and .xml config files. Or - on the downloads page you will also find a simpler package just for report viewing (e.g. 4.5.3-my-fyi-build-dot-net-4-viewer-x86.zip) which simply contains RdlViewerSC.exe and two XML files.

Simple (fixed) Queries

This works well if the data shown to the user of your application is the result of a simple SQL query. By calling the same Stored Procedure both your application and your report can be sure to show the user exactly the same results. Simply call EXEC usp_MyStoredProc from your application for displaying the data, and call a report from the Print button where the report also contains EXEC usp_MyStoredProc as the SQL Select.

The advantage of maintaining the SQL in the database is that you don't have to edit the report for simple changes to the query. You may regard this as more professional considering such issues as centralisation and non-duplication of code, backups and version control.

Queries with parameters

Recently (Jan 2013 - to be included in versions later than 4.5.3) the ability has been added to execute reports with parameters passed on the command line. The format is as follows, with the report parameters in one quoted string to allow spaces in individual text parameters (without Windows getting confused), note that parameters may be named as you require :
(NOTE : As this involves the characters " = &, it may be worth experimenting to see what needs to be sanitised if user input is passed as a parameter to a report)

DOS command line example
C:\Fyi> RdlReader.exe -r "path\report.rdl" -p "RowId=100&Match=The second parameter value"

Python

from subprocess import Popen    # now we can reference Popen
new_process_obj = Popen(['RdlReader.exe', '-r', r'path with spaces\report.rdl', '-p', r'RowId=43&Name=Mr Big'])

# NOTES
# Windows paths with \ can be troublesome because 
# \ is an escape character which may do odd things to your strings.
# You can use / instead of \ as Windows works with either, or
# import os    # and convert with normpath :
# filename = os.path.normpath('C:/Program Files/test.txt')  # if you must have \
# ... or you could try : filename = filename.replace('/','\\\\') 

# Or use r'rawstrings'
# filename = r'C:\Program Files\test.txt'
# BUT you can't have a \ at the end of one of those! 

# Anyway, once you have the path sorted...

# Notepad test - to show we don't need " quotes in paths with spaces
# as every string within ['','','',''...] is passed whole as one param regardless of spaces
import subprocess  # now we can reference subprocess.whatever
filename = 'C:/Program Files/test.txt'
new_process_obj = subprocess.Popen(['notepad', filename])

C# example

System.Diagnostics.Process.Start("C:\Fyi\RdlReader.exe", '-r "C:\Fyi\myreport.rdl" -p "param1=The parameter value&param2=The second parameter value"');

VB.NET example

System.Diagnostics.Process.Start("C:\Fyi\RdlReader.exe", "-r "C:\Fyi\myreport.rdl" -p """param1=The parameter value&param2=The second parameter value""")

This is useful if you require the use of selection criteria. Prior to this enhancement you would have had to investigate full integration of the viewer into your application, or use a work-around that queues reports for each user via a new table in the database, along with any required parameters. It is now very versatile to be able to present any report on screen and pass it any information required. And if you need to provide a report based upon temporary data that isn't in the database, you can even store it in a temporary table for the user and then call a report that accesses it.

A Simple Report Queue (MS SQL example)

Prior to being able to pass parameters to a report this way, we needed a way to communicate our wishes to the report. A work-around was to queue reports for each user via a new table in the database, along with any required parameters. The following is kept as an example of how this was done, in case it is still useful.

Your application calls a "Push" Stored Procedure, passing it a codename for the required report along with any parameters, and then calls the report which you designed earlier with the required layout and which uses a "Pop" SP to retrieve the data. The Push and Pop SPs store the SQL per user in a new table called ReportQueue :

CREATE TABLE [dbo].[ReportQueue](
	[RQ_User] [nvarchar](30) NOT NULL,
	[RQ_SQL] [nvarchar](max) NULL,
	[RQ_P1] [nvarchar](30) NULL,
	[RQ_P2] [nvarchar](30) NULL
)

The following is an example of a Push SP that you would call from your application before launching the report, using the injection-safe format :
EXEC usp_ReportQueue_Push @ReportCode='example with param', @P1='42'

NOTE : numeric values can be matched in your SQL even if passed in quotes; field=2 and field='2' both work. By wrapping safely in quotes, even if @P1 contains '42; some rogue SQL command' then the EXEC won't fail or respond to the hack attempt, the full extended value of P1 gets through to the SP usp_ReportQueue_Push with its risky payload. You must deal with it there, by using parameter passing there too, instead of dynamically constructed SQL containing vulnerable content!
The worst that can happen then is a SQL error as it tries to convert a hack-attempt string into a numeric value. So if you provide a text box for entering a value along the lines of "Select items with value more than : " then it's best to attempt to convert to a proper numeric value in your app code (within an error-trapped try..catch block) rather than blindly passing the entered text to a report and assuming it's a valid number.

CREATE PROCEDURE [dbo].[usp_ReportQueue_Push]
    @ReportCode nvarchar(30) = null,
    @P1 nvarchar(30) = null,
    @P2 nvarchar(30) = null
    -- add as many P2, P3 etc. as you will need, they are optional in any EXEC calls (will be null)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

-- Intended for use with param-ed reports.
-- To be called from VB app :  EXEC usp_ReportQueue_Push 'test rpt','1','','','' etc
--   !!! USE THE INJECTION-SAFE FORMAT : EXEC usp_ReportQueue_Push @ReportCode='test', @P1=42
-- and this will create the required cSQL in a case statement depending upon required report
-- and store the cSQL in table ReportQueue

-- First, build SQL query - to be executed later in usp_ReportQueue_Pop
DECLARE @cSQL NVarchar(MAX);
SET @cSQL = CASE @ReportCode
  WHEN 'table1report' THEN
   'SELECT * FROM table1'
  WHEN 'example with param' THEN --  Always embed the @P params inside the SQL, don't concatenate!
   'SELECT * FROM mytable WHERE '+ CASE WHEN @P1 IS NULL THEN '1=1' ELSE 'myfield=@P1' END
  ELSE '' END;

-- Store cSQL in ReportQueue for user  -- System_User = Suser_SName()
DELETE FROM ReportQueue WHERE RQ_User=System_User; -- remove any queued rpts for this user
INSERT INTO ReportQueue(RQ_User,RQ_SQL,RQ_P1,RQ_P2) VALUES (System_User,@cSQL,@P1,@P2);

-- Return one description of what has been stored, for info
SELECT System_User+': '+@cSQL+' (Params) 1='
  +Coalesce(@P1,'')
  +' 2='+Coalesce(@P2,'');
END

The Push SP above is where you will maintain your SQL queries. To execute the queries and retrieve the data, your RDL reports will call a Pop SP - with EXEC usp_ReportQueue_Pop as the 'SQL Select' stored in the report. Here is the SP :

CREATE PROCEDURE [dbo].[usp_ReportQueue_Pop]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

-- Intended for use with param-ed reports.
-- To be called from various My-FyiReporting reports
--   - Various reports with their own layouts are called from VB app after setting Queue with usp_ReportQueue_Push()
--     each report then just contains : EXEC usp_ReportQueue_Pop

DECLARE @cSQL nvarchar(MAX);
DECLARE @P1 nvarchar(30);
DECLARE @P2 nvarchar(30);

SELECT Top 1 
  @cSQL=RQ_SQL, 
  @P1=RQ_P1, 
  @P2=RQ_P2
FROM ReportQueue WHERE RQ_User=System_User; -- get user's last SQL

IF @cSQL IS NULL 
  SELECT 'No reports are queued for user: '+System_User
ELSE
  EXECUTE sp_executesql @cSQL, N'@P1 nvarchar(30), @P2 nvarchar(30)', @P1, @P2

END

IMPORTANT NOTE The above SP's could be insecurely used to contain dynamically constructed SQL which, as always, could be vulnerable to SQL injection attacks if passed parameters directly from user input without sanitisation. Even with sanitised parameters, some would argue that you probably still haven't thought of everything! It is more secure to build your SQL to contain @P1, @P2 directly within the SQL (e.g. '... and myfield=@P1 and my2nd=@P2' rather than adding the string contents to the SQL string like '... and myfield='+@P1+' and...'. The Pop SP will pass those parameter values safely into sp_executesql after @cSQL.