Show credit and debit report in ASP.Net Crystal Report

jmpratee...

SOLVED CLOSED ACTIVE ANSWERED
User: jmprateek
Posted: on Nov 07, 2022 04:26 AM
Forum: Crystal Reports with ASP.Net
Answer: 1
Views: 1670

Hi,

I'm Prateek.

I'm storing credit, debit, opening balance, closing balance, and date in one table to generate account statements.

Below is my table script with sample data for your reference:

USE [testDB]
GO
/****** Object:  Table [dbo].[ledgers]    Script Date: 07-11-2022 16:38:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ledgers](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[cdate] [varchar](50) NULL,
	[ctime] [varchar](50) NULL,
	[voucher_no] [int] NULL,
	[account_id] [varchar](100) NULL,
	[opening_balance] [decimal](18, 2) NULL,
	[debit] [decimal](18, 2) NULL,
	[debit_particulars] [nvarchar](max) NULL,
	[credit] [decimal](18, 2) NULL,
	[credit_particulars] [nvarchar](max) NULL,
	[closing_balance] [decimal](18, 2) NULL,
	[remarks] [varchar](200) NULL,
 CONSTRAINT [PK_account_ledgers] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ledgers] ON 
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (1, N'11-06-2022
', NULL, 4, N'8310403169', CAST(211450.00 AS Decimal(18, 2)), NULL, NULL, CAST(84000.00 AS Decimal(18, 2)), NULL, CAST(295450.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (2, N'30-07-2022
', NULL, 21, N'8310403169', CAST(295450.00 AS Decimal(18, 2)), NULL, NULL, CAST(182050.00 AS Decimal(18, 2)), NULL, CAST(477500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (3, N'30-07-2022
', NULL, NULL, N'8310403169', CAST(477500.00 AS Decimal(18, 2)), CAST(150000.00 AS Decimal(18, 2)), N'EMPLOYEE SALARY', NULL, NULL, CAST(327500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (4, N'30-07-2022
', NULL, NULL, N'8310403169', CAST(327500.00 AS Decimal(18, 2)), CAST(50000.00 AS Decimal(18, 2)), N'KASINATH.M
', NULL, NULL, CAST(277500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (5, N'05-05-2022
', NULL, NULL, N'8310403169', CAST(277500.00 AS Decimal(18, 2)), CAST(35000.00 AS Decimal(18, 2)), N'KASINATH.M
', NULL, NULL, CAST(242500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (6, N'08-08-2022
', NULL, 36, N'8310403169', CAST(242500.00 AS Decimal(18, 2)), NULL, NULL, CAST(238000.00 AS Decimal(18, 2)), NULL, CAST(480500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (7, N'10-08-2022
', NULL, NULL, N'8310403169', CAST(480500.00 AS Decimal(18, 2)), CAST(100000.00 AS Decimal(18, 2)), N'MAHIBOOB.W
', NULL, NULL, CAST(380500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (8, N'10-08-2022
', NULL, NULL, N'8310403169', CAST(380500.00 AS Decimal(18, 2)), CAST(50000.00 AS Decimal(18, 2)), N'KASINATH.M
', NULL, NULL, CAST(330500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (9, N'19-08-2022
', NULL, NULL, N'8310403169', CAST(330500.00 AS Decimal(18, 2)), CAST(50000.00 AS Decimal(18, 2)), N'MAHIBOOB.W
', NULL, NULL, CAST(280500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (10, N'30-08-2022
', NULL, 54, N'8310403169', CAST(280500.00 AS Decimal(18, 2)), NULL, NULL, CAST(44750.00 AS Decimal(18, 2)), NULL, CAST(325250.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (11, N'03-09-2022
', NULL, 61, N'8310403169', CAST(325250.00 AS Decimal(18, 2)), NULL, NULL, CAST(55250.00 AS Decimal(18, 2)), NULL, CAST(380500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (12, N'06-09-2022
', NULL, 63, N'8310403169', CAST(380500.00 AS Decimal(18, 2)), NULL, NULL, CAST(44000.00 AS Decimal(18, 2)), NULL, CAST(424500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (13, N'07-09-2022
', NULL, 64, N'8310403169', CAST(424500.00 AS Decimal(18, 2)), NULL, NULL, CAST(44000.00 AS Decimal(18, 2)), NULL, CAST(468500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (14, N'08-09-2022
', NULL, 65, N'8310403169', CAST(468500.00 AS Decimal(18, 2)), NULL, NULL, CAST(43000.00 AS Decimal(18, 2)), NULL, CAST(511500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (15, N'19-09-2022
', NULL, NULL, N'8310403169', CAST(511500.00 AS Decimal(18, 2)), CAST(50000.00 AS Decimal(18, 2)), N'MAHIBOOB.W
', NULL, NULL, CAST(461500.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (16, N'28-09-2022
', NULL, 93, N'8310403169', CAST(461500.00 AS Decimal(18, 2)), NULL, NULL, CAST(36750.00 AS Decimal(18, 2)), NULL, CAST(498250.00 AS Decimal(18, 2)), NULL)
GO
INSERT [dbo].[ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) VALUES (17, N'10-10-2022
', NULL, 106, N'8310403169', CAST(498250.00 AS Decimal(18, 2)), NULL, NULL, CAST(24750.00 AS Decimal(18, 2)), NULL, CAST(523000.00 AS Decimal(18, 2)), NULL)
GO
SET IDENTITY_INSERT [dbo].[ledgers] OFF
GO

Now I want to show data in the crystal reports but it is showing nulls when there are nulls in either debit or credit columns like the

I want it to show in a uniform manner without any nulls.

Please help me achieve this. Thanks in advance.

Download FREE API for Word, Excel and PDF in ASP.Net

PrinceG

0 5

Reply
ANSWER
User: PrinceG
Replied: on Nov 08, 2022 12:23 AM
Modified: on Nov 08, 2022 12:24 AM
Report

Hi jmprateek,

You can check with ISNULL Condition for Credit and Debit column.

Please refer below SQL Query.

SQL

CREATE TABLE [ledgers](
    [id] INT IDENTITY(1,1) NOT NULL,
    [cdate] VARCHAR(50) NULL,
    [ctime] VARCHAR(50) NULL,
    [voucher_no] INT NULL,
    [account_id] VARCHAR(100) NULL,
    [opening_balance] DECIMAL(18, 2) NULL,
    [debit] DECIMAL(18, 2) NULL,
    [debit_particulars] VARCHAR(max) NULL,
    [credit] DECIMAL(18, 2) NULL,
    [credit_particulars] VARCHAR(max) NULL,
    [closing_balance] DECIMAL(18, 2) NULL,
    [remarks] VARCHAR(200) NULL,
)
SET IDENTITY_INSERT [ledgers] ON
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (1, '11-06-2022', NULL, 4, '8310403169', CAST(211450.00 AS Decimal(18, 2)), NULL, NULL, CAST(84000.00 AS Decimal(18, 2)), NULL, CAST(295450.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (2, '30-07-2022', NULL, 21, '8310403169', CAST(295450.00 AS Decimal(18, 2)), NULL, NULL, CAST(182050.00 AS Decimal(18, 2)), NULL, CAST(477500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (3, '30-07-2022', NULL, NULL, '8310403169', CAST(477500.00 AS Decimal(18, 2)), CAST(150000.00 AS Decimal(18, 2)), 'EMPLOYEE SALARY', NULL, NULL, CAST(327500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) 
VALUES		  (4, '30-07-2022', NULL, NULL, '8310403169', CAST(327500.00 AS Decimal(18, 2)), CAST(50000.00 AS Decimal(18, 2)), 'KASINATH.M', NULL, NULL, CAST(277500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (5, '05-05-2022', NULL, NULL, '8310403169', CAST(277500.00 AS Decimal(18, 2)), CAST(35000.00 AS Decimal(18, 2)), 'KASINATH.M', NULL, NULL, CAST(242500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (6, '08-08-2022', NULL, 36, '8310403169', CAST(242500.00 AS Decimal(18, 2)), NULL, NULL, CAST(238000.00 AS Decimal(18, 2)), NULL, CAST(480500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (7, '10-08-2022', NULL, NULL, '8310403169', CAST(480500.00 AS Decimal(18, 2)), CAST(100000.00 AS Decimal(18, 2)), 'MAHIBOOB.W', NULL, NULL, CAST(380500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (8, '10-08-2022', NULL, NULL, '8310403169', CAST(380500.00 AS Decimal(18, 2)), CAST(50000.00 AS Decimal(18, 2)), 'KASINATH.M', NULL, NULL, CAST(330500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (9, '19-08-2022', NULL, NULL, '8310403169', CAST(330500.00 AS Decimal(18, 2)), CAST(50000.00 AS Decimal(18, 2)), 'MAHIBOOB.W', NULL, NULL, CAST(280500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (10, '30-08-2022', NULL, 54, '8310403169', CAST(280500.00 AS Decimal(18, 2)), NULL, NULL, CAST(44750.00 AS Decimal(18, 2)), NULL, CAST(325250.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (11, '03-09-2022', NULL, 61, '8310403169', CAST(325250.00 AS Decimal(18, 2)), NULL, NULL, CAST(55250.00 AS Decimal(18, 2)), NULL, CAST(380500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (12, '06-09-2022', NULL, 63, '8310403169', CAST(380500.00 AS Decimal(18, 2)), NULL, NULL, CAST(44000.00 AS Decimal(18, 2)), NULL, CAST(424500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (13, '07-09-2022', NULL, 64, '8310403169', CAST(424500.00 AS Decimal(18, 2)), NULL, NULL, CAST(44000.00 AS Decimal(18, 2)), NULL, CAST(468500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (14, '08-09-2022', NULL, 65, '8310403169', CAST(468500.00 AS Decimal(18, 2)), NULL, NULL, CAST(43000.00 AS Decimal(18, 2)), NULL, CAST(511500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) 
VALUES		  (15, '19-09-2022', NULL, NULL, '8310403169', CAST(511500.00 AS Decimal(18, 2)), CAST(50000.00 AS Decimal(18, 2)), 'MAHIBOOB.W', NULL, NULL, CAST(461500.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks]) 
VALUES		  (16, '28-09-2022', NULL, 93, '8310403169', CAST(461500.00 AS Decimal(18, 2)), NULL, NULL, CAST(36750.00 AS Decimal(18, 2)), NULL, CAST(498250.00 AS Decimal(18, 2)), NULL)
INSERT [ledgers] ([id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], [debit], [debit_particulars], [credit], [credit_particulars], [closing_balance], [remarks])
VALUES		  (17, '10-10-2022', NULL, 106, '8310403169', CAST(498250.00 AS Decimal(18, 2)), NULL, NULL, CAST(24750.00 AS Decimal(18, 2)), NULL, CAST(523000.00 AS Decimal(18, 2)), NULL)

SELECT [id], [cdate], [ctime], [voucher_no], [account_id], [opening_balance], ISNULL(CAST([debit] as varchar(50)),'') [debit], [debit_particulars],ISNULL(CAST([credit] as varchar(50)),'') [credit], [credit_particulars], [closing_balance], [remarks] FROM [ledgers]
DROP TABLE [ledgers]

Output

id	cdate	ctime	voucher_no	account_id	opening_balance	debit	debit_particulars	credit	credit_particulars	closing_balance	remarks
1	11/6/2022	NULL	4	8310403169	211450		NULL	84000	NULL	295450	NULL
2	30-07-2022	NULL	21	8310403169	295450		NULL	182050	NULL	477500	NULL
3	30-07-2022	NULL	NULL	8310403169	477500	150000	EMPLOYEE SALARY		NULL	327500	NULL
4	30-07-2022	NULL	NULL	8310403169	327500	50000	KASINATH.M		NULL	277500	NULL
5	5/5/2022	NULL	NULL	8310403169	277500	35000	KASINATH.M		NULL	242500	NULL
6	8/8/2022	NULL	36	8310403169	242500		NULL	238000	NULL	480500	NULL
7	10/8/2022	NULL	NULL	8310403169	480500	100000	MAHIBOOB.W		NULL	380500	NULL
8	10/8/2022	NULL	NULL	8310403169	380500	50000	KASINATH.M		NULL	330500	NULL
9	19-08-2022	NULL	NULL	8310403169	330500	50000	MAHIBOOB.W		NULL	280500	NULL
10	30-08-2022	NULL	54	8310403169	280500		NULL	44750	NULL	325250	NULL
11	3/9/2022	NULL	61	8310403169	325250		NULL	55250	NULL	380500	NULL
12	6/9/2022	NULL	63	8310403169	380500		NULL	44000	NULL	424500	NULL
13	7/9/2022	NULL	64	8310403169	424500		NULL	44000	NULL	468500	NULL
14	8/9/2022	NULL	65	8310403169	468500		NULL	43000	NULL	511500	NULL
15	19-09-2022	NULL	NULL	8310403169	511500	50000	MAHIBOOB.W		NULL	461500	NULL
16	28-09-2022	NULL	93	8310403169	461500		NULL	36750	NULL	498250	NULL
17	10/10/2022	NULL	106	8310403169	498250		NULL	24750	NULL	523000	NULL

For displaying the record in Crystal Report refer the article.

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Show credit and debit report in ASP.Net Crystal Report

Crystal Report ASP.Net Example using DataSet or DataTable in C# VB.Net and Visual Studio 2010

Company

Explore

Follow us

Contact Us