March 7, 2011 at 11:52 am
I don't know what format you guys would feel comfortable with. But I am giving out the details in a simple form.
Following are the tables and respective keys.
1. Retailer - RetailerCode, SalesmanCode
2. SalesVoucher - VoucherNo, RetailerCode
3. SalesDetail - ID, VoucherNo, ItemCode
4. ItemInfo - ItemCode, CategoryCode
5. Category - CategoryCode
It is evident that a Retailer is linked with a salesman (one salesman only). The Invoices (Sales Vouchers) are entered for each sale made to a retailer. Example of a Category can be "Ball". Whereas, the Items within this category can be "Blue Ball, Red Ball, Green Ball". Another Category can be "Doll". And Items within this category can be "Chinese Doll, Indian Doll, Barbie Doll".
Invoices (Sales Voucher), as evident from the tables above, are entered to record specific Items and not Categories.
Now, I wish to generate a report that gives Category-wise sales detail of each retailer.
Following is the format.
Column 1 Name - Retailer --> Data - (Name of the Retailer)
Column 2 Name - Ball -->Data - (Sum of Items Of this Category Sold to the Retailer)
Column 3 Name - Doll --> Data - (Sum of Items Of this Category Sold to the Retailer)
Can somebody Please help me with this.
March 7, 2011 at 12:02 pm
This would usually be done via T-SQL, or the reporting layer, not in SSIS. You could probably do it via a series of lookup functions and secondary queries, but you'd have to build the package with known items in mind, it won't work dynamically... at least not that I'm aware of without doing some seriously wild foreach looping.
It looks like you're going to need the PIVOT function, but that's going to run into a limitation eventually as well, depending on the # of your categories.
As to what we're comfortable with, if you check out the first link in my sig, it'll show you a good way to present your data so we can help you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 7, 2011 at 12:17 pm
I'm with Craig on that - can I ask what made you post this question in the SSIS forum? Do you have a certain solution or methodology in mind that requires SSIS?
If not, this seems like a fairly straightforward reporting task.
March 7, 2011 at 2:55 pm
If you're asking for help on the actual query you need to run the tsql forums would be a better place.
However depending on how the report is going to be consumed SSIS may or may not be a solution for you, are you planning to export the data to a file? If so that should be a simple task with SSIS assuming you have the query correct.
March 7, 2011 at 3:25 pm
I just took a poke around the SSIS interface and in Data Flows found a tool that my eyes have glided over repeatedly... amazingly enough, called Pivot. 🙂
I have no idea how to use it, but it's there... I'm sure google can help on that though.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply