SQL Reporter

  • SQL Reporter is a Reporting Tool for SQL Databases (view Schemas, generate Reports).
  • Choose an ADO.NET, OleDb or an ODBC Connect String.
  • SQL Reporter is tested with:
    - MsSQL (Microsoft SQL Server)
    - MySQL
    - Oracle
    - PostgreSQL
© Coopware 2023 (http://coopware.in2.info)


Installation

Manual Installation

Copy the followig files into a directiory of your choice e.g. "C:\Program Files\SQL Reporter"

    sqlrepo.exe | main executable
sqlrepo.chm | help file

Put the files on an USB stick to get a portable version of SQL Reporter or
copy them on a network share and link sqlrepo.exe to the start menu of multiple computers.

Manual Configuration

    Before you start choose a Connect String for each Database:

  1. For MySQL Databases use [image] in Toolbar to define an ODBC User DSN:
    - As Driver use MySQL ODBC 8.0 ANSI Driver.
    - Configure an User with read rights in your Database and a Password.

    - Specify the Connect String: DSN= in the SQL Reporter Settings.

    You also may change the Root Directory:
    Each Database uses a folder Root Directory\Database Name.

  2. For MsSQL Server you have 3 Options:
    • ADO ConnectString: Server=tcp:sqlsrv.org.net; Database=YourDB; Integrated Security=true
    • OLE ConnectString: Provider=sqloledb; Server=sqlsrv.org.net; Database=YourDB; Integrated Security=SSPI
    • ODBC: Use [image] in Toolbar to define an ODBC User DSN:
      - Enter a name for the DSN, a description and the name of your SQL Server.
      - Use Windows Authentication and set the Standard DB to the Name of your SQL Database.
      - Specify the Connect String: DSN= in the SQL Reporter Settings.

  3. For Oracle Server use [image] in Toolbar to define an ODBC User DSN:
    - Install Instant client and ODBC driver from Oracle Website.
    - As TNS Service Name use the name defined in network\admin\tnsnames.ora.
    - Configure an User with read rights in your Database and a Password: DSN=Oracle;UID=User;PWD=Pswd1234

  4. For PostgreSQL use [image] in Toolbar to define an ODBC User DSN:
    - Install PostgreSQL ODBC driver (e.g. psqlodbc-setup.exe /passive /norestart).
    - Configure an User with read rights in your Database and a Password: DSN=PostgreSQL30
    - It seems as if the Name PostgreSQL30 could not be changed so you can only include one PostgreSQL Database.


How to use SQL Reporter

  1. Show DB Properties
  2. Show DB Schema
  3. Add a Report
  4. Verbose Logging

1.Show DB Properties

  • In TreeView choose a DB Item
  • Left click to show Properties
  • Right click to edit Properties
  • Choose Database Type:
  • Use Database: checked allows reporting from Database
    Use Database: unchecked suspends the current Database
  • Rem Database: checked removes current Database from TreeView

2.Show DB Schema

  • In TreeView choose a DB Item
  • Under DB Item choose Schema
  • Double click on Schema or use one of the following ToolBar Buttons:
    Zoom in the Database Schema
    Zoom out the Database Schema
  • To Show/Hide a Grid in the Database Schema use:

    • Configure partial DB Schema:

  • Right click Schema [image] in TreeView
  • Use Edit query
  • You will see something like this:
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME
  • You may change it to view a partial schema with only selected tables:
    SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'TAB_%' ORDER BY TABLE_NAME

    • How it works:

  • Every Database Typ has its own rules for Constraints.
  • SQL Reporter lists Constraints and draws them in Schema.svg.
  • You may view Constraints if you click on Constraints and
    see Table Specifications if you click on Tables.

3.Add a Report

  • Right click Reports [image] in TreeView
  • Use Add query
  • Fill in the form beside:
    • Choose a Name for your Report.
    • Path will automatically be completet.
    • Fill in the SQL Query for your intention.
      This requieres a deeper understanding of SQL and the SQL schema.
    • Choose a Report View (sorted, unsorted or table) and press OK.

4.Verbose Logging

  • Standard Logfile: %TEMP%\sqlrepo.log
  • Run sqlrepo.exe /lvrb=1 to log more details (same path as sqlrepo.exe).
  • Run sqlrepo.exe /lvrb=2 to log schema details (same path as sqlrepo.exe).


Graphical user interface (GUI)

  1. Menu and Shortcuts
  2. Toolbar
  3. Configuration

1. Menu and Shortcuts

  • Export Report Configuration
  • Import Report Configuration

  • F1 ... Show Help
  • F2 ... Show Info

2. Toolbar

  • Create a new ODBC User DSN to SQL Database
  • Add new Database in SQL Reporter with Connect String
  • Refresh Report ...
  • Zoom in the Database Schema
  • Zoom out the Database Schema
  • Show/Hide a Grid in the Database Schema

3. Configuration

  • SQL Reporter stores Settings in the Windows Registry:
    HKEY_CURRENT_USER\Software\Coopware\SQL Reporter
  • Export and Import of this Registry Key is available in
    Menu and Shortcuts