October 27, 2001 at 1:30 am
Hi,
Can anyone suggest how to print of bills from data on SQLserver 7.0 with crystal or jetforms as a printing utility.the data is split over some multiple tables ,multiple rows.
October 27, 2001 at 7:10 am
Havent used Crystal in a while. I think when you start it up it offers some templates for different types of reports, you're looking for one that has a master/detail layout. If you can't find that (or cant get it to work), then you write query that "flattens" your hierarchy, then suppress printing of the duplicate values from the master record. Of the two, the first is easily the best way to go.
You should see if there isnt an independent web site or newsgroup that handles Crystal questions, more likely to find a good answer there.
Andy
October 28, 2001 at 10:57 pm
well andy thanks for your suggestions.Well what i needed to know was that how to supress or flatten the data or take multiple data from multiple rows and create a master record out of it using SQLserver itself and any strategy to do it.
I hope you understand now that the problem is not in using Crystal or Jetforms which we have plenty of expertise on.
October 29, 2001 at 5:54 am
Using a view is usually a good way to package something like this, but achieving it can be fairly difficult. If you have only one child record, then a simple inner join where you return all columns from both tables would work. If you have more than one child record, finding a set based solution is going to be harder (and possibly require a cursor, have not tried). The real problem is that depending on the number of child records and how many columns/what size, you may exceed the ability of SQL to represent them in a row since you're limited to 8k chars. Even harder than that, how will you report on something where you don't know how many columns you'll have at run time?
If you'll post the DDL for the tables and some info on what you're trying to do, we'll try to help.
Andy
October 29, 2001 at 11:14 am
Alternatively, if this is something you will do and not change, or something that is a snapshot of data and not subject to concyurrency issues, you can create a temp table or a "staging" table and move the data there.
Then print from this table and clear it when you finish printing before the next "print job".
BTW, I'd avoid Crystal. Not a great product for complex printing IMHO. Active Reports (http://www.hallogram.com/activereports/?source=goto) is a much better product.
Steve Jones
October 29, 2001 at 4:26 pm
What are you going to do with the output.
You will need a record of what is produced. If this is to be in the database then you should probably populate a billing table specifically for this purpose - possibly in a datbase that is backed up to keep a billing record rather than just for security. This could just be a flat table with the actual data that goes on the bill.
Depends on what your skills are as to what you should use - I've done this before using access, I'm pretty sure that crystal reports can do it but will probably take some time to get right unless are experienced, could also probably use word or excel - or there are probably lots of free to very expensive products you could get (there's always write your own in VB or generate the html from sql server).
Cursors never.
DTS - only when needed and never to control.
October 30, 2001 at 5:55 am
Well i was thinking of creating a temp table which contains all the data required for the billing.The data is spread over like 8-9 tables with something like 24 columns from all of these tables with the input needed coming of a number of records. Now I was planning to schedule a job which creates this temp table at the end of every day. then do the intelligent pagination required for the bill to be printed on the print utility(crystal etc.) . But the issue is to create a master record with all the data spread over diff. records. The challenge now is to take a decision on wether we need to write a program using either C,C++ or Java etc or can we do it directly out of the SQL server.
Does anyone have any ideas?
also thanks for all the responses.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply