SeekTable
This documentation page assumes that you already have a SeekTable account. Create your free account by signing up.

Setup SQL Server connection for pivot table reports

SeekTable can integrate fairly easily with MS SQL Server using SQL-compatible database connector. SeekTable is great if you need to give non-technical users the ability to query data, but leave an ability to use full power of SQL behind the scene for cube's creators. Also SeekTable may be considered as a modern lightweight replacement for SSRS tabular reports.

Connection String should be a valid connection string for SqlClient driver; for example: 

Data Source=hostName;Database=db;User=user;Password=password;
Data Source
or Server		 this setting specifies the name of the server and the SQL Server instance on the server. The port number can be specified after the server name (optional): ServerName\Instancename,portNumber.
Do not use ".", "(local)", "localhost" or LAN name as a server name; use only public IP address or domain name of the server. If you omit the instance name, the default instance is assumed.
Database specifies the name of the database in the SQL Server instance.
User SQL Server login account. It is recommended to create special user with read-only rights.
Password The password for the SQL Server account logging on.

Note: do not specify "Integrated Security", this option is irrelevant in SeekTable environment.

Date-part dimensions calculated with SQL expressions

To define date-part dimensions (date's "year", "month", "day" etc) you may add dimensions defined with an appropriate SQL expression:

  1. add new dimension with Type=Field
  2. fill Name with some unique value: say, "date_column_year"
  3. add one Parameter which should be a date-part SQL Server expression:
    • For year: YEAR(date_column)
    • For month: MONTH(date_column) + Format={0:MMM} (Jan, Feb etc) or {0:MMMM} (January, February etc)
    • For day: DAY(date_column)
    • For day-of-week: DATEPART(dw , date_column)-1 + Format={0:ddd} (Mon, Tue etc) or {0:dddd} (Monday, Tuesday etc)
    • For quarter: DATEPART(q , date_column)
    • For week-of-year: DATEPART(iso_week, date_column)