Link to home
Start Free TrialLog in
Avatar of ROB MCCONNELL
ROB MCCONNELL

asked on

Trial Balance VB.NET Code reporting issues

I am trying to create a Trial Balance output report (Maybe crystal reports) in VB.NET desktop application.

There are 5 different tables to retrieve the data from.  I have attached the VB.net Code.

My thought is to create a view within the SQL database that will do everything that the code attached will do.

My questions are:

Is this possible?

Can someone please help me?
TrialBalCode.docx
Avatar of Mike McCracken
Mike McCracken

Sure.  You just need to figure out the SQL the report is using to produce the data and replicate it in the database as a view.

Another way to do this is to use the recordset the program produces and pass  it to the Crystal Report

See the example provided by Eric Moreau in hos blog
https://www.emoreau.com/Entries/Articles/2016/10/Crystal-Reports-for-Net-revisited-10-years-later.aspx

mlmcc
I believe Crystal Reports only allows one result-set, produced by either a stored-procedure, or select-statement (either can use database-views).  The problem is combining/denormalizing the five queries into one.  The first two tables/queries may produce zero, one, or many rows, and have a common field, NomAccNo, joining those will be simple.  The next three tables/queries may produce zero, or one row, and will be challenging to join if they do not have NomAccNo, I suspect you could use a cross-join.

I prefer using a stored-procedure because you can perform a small miracle, pass thru your data many times, use temporary-tables, and advanced SQL techniques.

A database-view is basically a name given to a select-statement.  After the database-view is created, it can be treated in your SQL as if it were a single-table.  You can add where-clause, group-by, having-clause, order-by, and may participate in a common-table-expression.
Actually you can use multiple queries, SAP/Crystal just doesn't support it though it does work.

mlmcc
ASKER CERTIFIED SOLUTION
Avatar of ROB MCCONNELL
ROB MCCONNELL

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial