Use cases

Examples of User Stories and ways to solve them with TheRDL

• A new set of data (simple query) should be exposed to a user

• Multi-join (multi-tables) query needs to be presented as a report

• Existing report requires a new column

• Multiple reports (different sets of data) all at once require font/colour/logo change (rebranding)

• Internal team needs to see data from a table and be able to filter output

• ...

Please feel free to propose your example of an User Story and we'll try to solve it with TheRDL

New set of data to be exposed to a user

Let's imagine there is table in our database called "dbo.Customer", and one of a team member needs to see this data in a SSRS report, just list of customers basically. Columns required: ID, Active flag and customer Name.

Current Customer table schema:

CREATE TABLE [dbo].[Customer](
    [ID] [int] NOT NULL IDENTITY(1, 1),
    [Active] [bit] NOT NULL CONSTRAINT [DF_Customer_Active] DEFAULT ((1)),
    [Name] [nvarchar] (255) COLLATE Latin1_General_CI_AS NOT NULL,
    [Address] [varchar] (4000) COLLATE Latin1_General_CI_AS NULL,
    [ContactName] [varchar] (255) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]

With TheRDL all we need to do is just add a setting to dbo.TherdlSetting table:

INSERT INTO dbo.TherdlSetting(Code, Description, OrderID, DBName, SchemaName, ObjectName, ShowOnlyColumnsArrayListJSON)
VALUES('CustomerInfo', 'Expose customer information', 10, DB_NAME(), 'dbo', 'Customer', '["ID", "Active", "Name"]')
;

That's it. Report is ready. Run it and choose this new "CustomerInfo" code. Please note only required columns will be presented as per "ShowOnlyColumnsArrayListJSON" setting.

On top of this user can use Filters (JSON format) to filter data, for example to show only Active customers pass this as a filter:

{"Active":true}

Run the report:

Filter can be used with multiple conditions on any columns if required.

Multi-join query needs to be presented as a report

For the complex query let's create a view which shows what's in a storage and which customers/representatives to be responsible for a product

CREATE OR ALTER VIEW dbo.vwStorage AS 
SELECT a.Name, a.Quantity, a.Price, c.Name AS [MadeBy],c.ContactName AS [ComplainTo], a.SortingGroup
FROM (VALUES
	 (NULL,'Shelf','Cartbox',23, 0.01)
	,(NULL,'Bucket','Junk',34, 0)
	,('Amazon','Shelf','Alexa Echo',1, 30)
	,('Future Motion','Garage','Onewheel XR',1, 2500)
)a(CustomerName,SortingGroup,Name,Quantity,Price)
LEFT JOIN dbo.Customer c ON c.Name COLLATE DATABASE_DEFAULT = a.CustomerName COLLATE DATABASE_DEFAULT
;

Then again just simply add literally one line into dbo.TherdlSetting table passing NULL for the columns list meaning to show all columns for the view

INSERT INTO dbo.TherdlSetting(Code, Description, OrderID, DBName, SchemaName, ObjectName, ShowOnlyColumnsArrayListJSON)
VALUES('Storage', 'What''s in my storage', 20, DB_NAME(), 'dbo', 'vwStorage', NULL)
;

Run the report choosing new "Storage" code. Easy as

Existing report requires a new column

... to-do...