Create the report - how to?

Question

User-1720436624 posted

Hi,

i have the following SQL DB Table structure, including the data:

CREATE TABLE [dbo].[T_BudgetCharts](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[BudgetId] [int] NOT NULL,
	[BudgetItemLevel] [int] NOT NULL,
	[BudgetItemCode] [nvarchar](20) NOT NULL,
	[BudgetItemName] [nvarchar](300) NOT NULL,
	[BudgetItemMasterID] [int] NULL,
	[BudgetItemBudegtValue] [decimal](18, 2) NULL,
	[BugedtIdOriginal] [int] NULL,
	[MasterIdOriginal] [int] NULL,
	[BugedtItemStatus] [int] NOT NULL,
	[sysDateTimeCreated] [datetime] NOT NULL,
	[sysDateTimeModified] [datetime] NOT NULL,
 CONSTRAINT [PK_T_BudgetCharts] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[T_BudgetCharts] ON
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (1, 1, 1, N'N/A', N'Undefined items', NULL, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD00928E83 AS DateTime), CAST(0x0000A5BD00928E83 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (2, 1, 2, N'N/A', N'Undefined items', 1, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD00928E83 AS DateTime), CAST(0x0000A5BD00928E83 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (3, 1, 3, N'N/A', N'Undefined items', 2, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD00928E84 AS DateTime), CAST(0x0000A5BD00928E84 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (4, 1, 1, N'1', N'Salaries', NULL, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD0092EB5C AS DateTime), CAST(0x0000A5BD0092EB5C AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (5, 1, 2, N'1.1', N'Pay Costs', 4, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD0092F8E1 AS DateTime), CAST(0x0000A5BD0092F8E1 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (6, 1, 2, N'1.2', N'Educational expenses', 4, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD00930833 AS DateTime), CAST(0x0000A5BD00930833 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (7, 1, 2, N'1.3', N'Other expenses', 4, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD009313F4 AS DateTime), CAST(0x0000A5BD009313F4 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (8, 1, 3, N'1.1.1', N'Employees Salaries', 5, CAST(2500.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD00932E30 AS DateTime), CAST(0x0000A5BD00932E30 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (9, 1, 3, N'1.1.2', N'Service contracts', 5, CAST(5000.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD00941555 AS DateTime), CAST(0x0000A5BD00941555 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (10, 1, 3, N'1.1.3', N'Other expenses', 6, CAST(2800.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD00942CF0 AS DateTime), CAST(0x0000A5BD0096BED3 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (18, 1, 3, N'1.2.1', N'Employees education', 6, CAST(2800.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD0096CE04 AS DateTime), CAST(0x0000A5BD0096CE04 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (19, 1, 3, N'1.3.1', N'Flight expenses', 7, CAST(3600.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD0096F4D1 AS DateTime), CAST(0x0000A5BD0096F4D1 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (13, 1, 1, N'2', N'Inocme', NULL, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD0094665C AS DateTime), CAST(0x0000A5BD0094665C AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (14, 1, 2, N'2.1', N'Courses', 13, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD009493B7 AS DateTime), CAST(0x0000A5BD009493B7 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (15, 1, 2, N'2.2', N'Donations', 13, CAST(0.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD0094B2A1 AS DateTime), CAST(0x0000A5BD0094B2A1 AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (16, 1, 3, N'2.1.1', N'English course', 14, CAST(5000.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD0094C8FB AS DateTime), CAST(0x0000A5BD0094C8FB AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (17, 1, 3, N'2.1.2', N'German course', 14, CAST(10000.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD0094E8CA AS DateTime), CAST(0x0000A5BD0097438F AS DateTime))
INSERT [dbo].[T_BudgetCharts] ([ID], [BudgetId], [BudgetItemLevel], [BudgetItemCode], [BudgetItemName], [BudgetItemMasterID], [BudgetItemBudegtValue], [BugedtIdOriginal], [MasterIdOriginal], [BugedtItemStatus], [sysDateTimeCreated], [sysDateTimeModified]) VALUES (20, 1, 3, N'2.2.1', N'New Donation', 15, CAST(25000.00 AS Decimal(18, 2)), NULL, NULL, 1, CAST(0x0000A5BD0097546E AS DateTime), CAST(0x0000A5BD0097546E AS DateTime))
SET IDENTITY_INSERT [dbo].[T_BudgetCharts] OFF

I need to create the Budget report that should look like the following image:

The main problem here is that the Budget can have N levels (BudgetItemLevel). That means that the Report should be expandable for each of this levels (except the last). I have no idea, really, how to create such report. As you can see, all the Item codes are in the same column - but this should be the "groupings" inside the report. Any ideas?

Thanks 

Answer 1

User-219423983 posted

Hi adnmeh,

The main problem here is that the Budget can have N levels (BudgetItemLevel). That means that the Report should be expandable for each of this levels (except the last).

Because of the depth is uncertain and the report need a certain columns, as a workaround, I think you could refer to the following link to build your report. In the following demo, it load the tree items in one column and set the sub total value to another column.

https://www.mssqltips.com/sqlservertip/1939/working-with-a-parent-child-hierarchy-in-sql-server-reporting-services-ssrs/

If the above is not what you want, you’d better first make sure the depth is fixed and then follow with the following link to learn how to build the tree structure result. Then, you could bind this data source to your Report Service.

http://sqlmag.com/t-sql/flattening-hierarchies

Or, you could first split the different columns into different results and make sure each column has the foreign value to connect to the parent column. Then, you could refer to the following link to learn how to bind multiple datasources in a same Tablix and loading related items by using Lookup method.

http://www.sqlcircuit.com/2012/03/ssrs-2008-r2-lookup-how-to-use-multiple.html

About adding the expandable action, you could have a look at the following article.

https://msdn.microsoft.com/en-us/library/dd220405.aspx

Best Regards,

Weibo Zhang

Answer 2

User-1720436624 posted

So far i got a really really simple solution: The key, if your data structure allows this, is to use recursive hierarchy in the report and everything works fine.

Feel free to share this solution if it fits your needs...