May 23, 2006 at 4:19 am
i have to create a flatfile report ( it will be used in excel in a pivot table).
im having some trouble with this.
i realise that there will be a lot of repeating data.
the lowest level of granularity is program. each program has many customers, each customer has many goals.
that far im fine. next there are payments and rebate payments
for some customers there are more rebate payments than regular payments and vice versa for other customers.
the only thing that links rebate payments, and regular payments is the customer id and the program Id.
how would you work this out so as to create a flat file of it all?
May 23, 2006 at 4:29 am
Fintan
Not sure what you're asking here. If you're wondering how to save the results of a query to an Excel spreadsheet, then what I would do is create a view and then BCP or DTS to the flat file. If you're asking for help writing the query, you will need to provide CREATE TABLE statements, sample data and expected results.
John
May 23, 2006 at 4:52 am
its a design kind of question.
eg. in the flat file i each row must contain customer info, program info, goal Info. thats no prob as they all lead on from eachother ( program has many customers has many goals). we then come to payments and rebatePayments. there can be loads of each or none of each. they are not related. they are only related to the program and customer. how would you create a row with cust info, progInfo, goal info,payment Info, rebate Info? sorry its not well explained
May 23, 2006 at 5:01 am
If you want all of this data in a single Excel worksheet in a format that makes any kind of sense, you have to decide on a maximum number of payments and rebate payments per customer and then export in the format:
Customer, Program, PaymentDate1, Payment1, PaymentDate2, Payment2, ..., PaymentDateN, PaymentN, RebateDate1, etc etc
To get the data into this format will be the first challenge, but it can be done.
What is the spreadsheet going to be used for? Perhaps there's a better way.
Also, remember that you've got a max number of rows of 65535 in a single Excel sheet.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply