Optimal technique and recommendations producing Crystal Report in VB2012

We would like EE recommendations and maybe some techniques in producing viable reports in vb2012.
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rayluvsAuthor Commented:
FYI:

The database is in Microsoft SQL 2005.

Also to give you some inside on the tables we want report on:

Minimum Sizes in SQL:
Detail Tables:             7,000 rows
header Tables:           1,500 rows
Master Tables:           7,000 rows


Maximum Sizes in SQL:
Detail Tables:            480,000 rows
header Tables:           96,000 rows
Master Tables:            3,500 rows
0
mlmccCommented:
Are you using the version that is for VB2012 or a standalone version of Crystal?

What are each of the table types used for?

Which tables link together?

mlmcc
0
rayluvsAuthor Commented:
Are you using the version that is for VB2012 or a standalone version of Crystal?

Yes, the one that comes in VB2012

What are each of the table types used for?

Detail Tables:              Item sold
header Tables:            Invoice Header and Footer info
Master Tables:            Vendors, Products, Shipping/Billing Address, etc.

Which tables link together?

Details >> Header (Items >> Invoice Header)
Details >> Master  (Items >> Products)
Header >> Master  (Invoice Header >> Vendors)
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

vastoCommented:
When you are working with server databases the best approach is to create stored procedures and use them to build your reports. This will allow you to have maximum performance and flexibility when you are retrieving the data.
0
mlmccCommented:
A lot will depend on what you want or need the report to look like and the information it needs to display.

A typical invoice will probably have the Shipping and billing address of the customer, a list of the products (details) and total cost.

If most of the orders are small in number of different products it may make sense to have a single query/stored procedure.  However if you typically have sales with many products you may find it is better to use a subreport to display the customer/vendor information since it is only needed once in the report rather than having each detail record include that information.

Or it may be easier to use a subreport for the detail lines of the invoice linked on the order number.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayluvsAuthor Commented:
Why use stored procedures? (Wouldn't it be he same if the query was in command in CR?)

Also, what do you mean by subreport for either to display customer/vendor for detail lines of the invoice?
0
mlmccCommented:
A stored procedure properly written will tend to be faster than even a command in Crystal since it is precompiled and the execution plan is already built.  In most cases though you probably won't notice much difference.

The problem with trying to do this in just a single (no subreports) report is that every record will have to include all the information (columns) needed for the shipping address, billing address, company header information (if it isn't hardcoded in the report), common order information, etc.

You could have the report just use the billing, shipping, order (no details) information and add a subreport that shows the order details.  It would link to the main report on the order number.

mlmcc
0
vastoCommented:
As mlmcc explained, it is very likely to have a better performance when using stored procedures. However the main advantage is that the stored procedure is in your database and is part of your backup and maintenance procedure. Another advantage is that if you need to change the structure of a table you can check dependent objects for the table and the stored procedure will be shown in the list ( so you will know to test it). You can also add the script for the stored procedure to the source safe.

The code in a stored procedure will allow you to prepare complex scripts , if you are using crystal reports you will be able to use just single clause query prepared by crystal. A command used in crystal will allow you to use complex scripts too , but it is just a workaround in case you have no permissions to create stored procedures in the database
0
rayluvsAuthor Commented:
Thanx all!
0
rayluvsAuthor Commented:
Thanx!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.