grouping rows by customer

my source flat file has many rows per customer,
but I need to transfer it to database with only one row per customer and accumulated sales (and probably do other calculations and lookups).
I understand how to do stuff with derived columns, but how can I read source file first, calculate, group and then save to database?
As I understand, the script offers only processing row by row: Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Thanks

Vlad

won't A Flat file source and aggregation transform suffice your needs?

Rafael Salas

|||

I tried this, but I do not think it would help :-(

it is more complicated calculation, than just grouping.

I would rather do it in the script load into a Collection, loop, calculate, replace, substitute etc. and then save to database.

|||

Well you know your data...good luck with that!

Rafael Salas

|||

The Aggregate transformation does more than Group By. You don't want to do this in script. You can do SUM, AVG, MIN, MAX with the Aggregrate Transformation. If you need to then do something else combine the Derived Column Transformation with it.

http://msdn2.microsoft.com/en-US/library/ms138031.aspx

Grouping question

Hi guys,

on my report i have 3 separate matrices with monthly sales results, each gives a different view on the same data. This all works nicely, but the customer wants these matrices grouped by product (the product is a parameter of the report, they can choose several or all products to report on).

Exactly what would be the best way to achieve this grouping? It seems i can't house them within a table, and i can't put them in another matrix without running into scope errors.

Thanks!!!

sluggy

Sounds like those three matrics are using the same dataset. In that case, you should be able to remove the dataset set on these matrices respectively. Add a list that includes the three matrices, set the dataset on the list, and add a group on the list by the product.|||

Thanks Fang,

that did the trick nicely.

One further (possibly stupid) question: how could i then have a set of matrices at the end with the totals (ie the aggregations across all products)? Should i just have a copy of the original matrices and make them invisible if there was only one product selected? Or is there a more proper or cooler way to do it?

Many thanks,

sluggy

|||

There are multiple ways to do this:

1. Add a copy of the original matrices outside of the list.

2. Use a matrix instead of list to host the three matrices, add a matrix row group by product, and enable subtotal on the row group. This way you will automatically get the matrics across all products in the subtotal row.

|||

Excellent, thanks!!

sluggy

Grouping problem

I am working on a report for a small POS. The report should allow the user to choose the time interval to group sales records, e.g. 1 hour, 2 hours or 4 hours. I believe I can setup this by the DiscretizationMethod and DiscretizationBucketCount of the Hour attribute in my DimTime dimension. However, the problem that I am facing is this POS will support multiple branches. Each branch will have their particular opening and closing hour. So, how can I group all the transaction into groups, said "Before Shop Open" and "After Shop Closed"? This sounds strange but will happen quite often as overtime work is always expected in my living place.

If this is infeasible, is there any workaround? I think the business user certainly want to know how many transaction has been created in those extra time.

In the other report, it is required to generate a transaction count by amount. The user should be able to specify the amount interval and upper limit. e.g. if amount interval and upper limit are set to 50 and 150, then the transaction will be grouped into 4.
0<=amount<50
50<=amount<100
100<=amount<150
amount>=150

I have no idea to this. First, I don't know how can I get the amount for each sale order as my fact table is storing sales order item information only. Second, how can I make this customizable grouping just like the report stated above? Thanks!

Hi Alex:

You pose two difficult problems. I'll address the second problem because you provided the most detail and clearly stated the issues. To restate, the issues are:

(1) How can you get the amount for each sale order?

(2) How can you allow customizable grouping?

Addressing issue (1) about the amount for the sales order. If the sales amount for the sales order is not in your fact table then you will not be able to access the sales amount in your cube. You have to go back to the ETL process and bring in the sales amount as part of yur fact table.

Issue (2), customizable grouping, is best approached on the client side of your application. Alternatively you, as an administrator, could create a separate attribute hierarchy for each branch with it's own amount interval and upper limit. I think your choice of a solution (client side, or separate hierarchy per branch) depends upon how many branches you have, and how much management you want to put in as an administrator. Creating transaction count by amount on the client is simple if you have the transaction amount as a measure. Get the transaction count by using a calculated member with the MDX count() function. Within each query you can adjust the amount interval and upper limit for each user. Here's an example:

WITH MEMBER MEASURES.[Less than 50] AS 'COUNT(FILTER(Transaction.Transaction.[Leaf Level].Members, Measures.[Sales Amount] < 50)'

MEMBER MEASURES.[Between 50 and 100] AS 'COUNT(FILTER(Transaction.Transaction.[Leaf Level].Members, Measures.[Sales Amount] > 50 AND Measures.[Sales Amount] < 100)'

SELECT {MEASURES.[Less than 50] , MEASURES.[Between 50 and 100]} ON COLUMNS FROM [my cube]

Hope this helps.

PGoldy

|||Hi PGoldy,

First, thank you for your input to these difficult problems that I am facing right now. Actually, I have come up with sort of solution after the post but it still doesn't work very well.

For issue 1, I found out that even I don't have a total for the sales order stored in the fact table. I can get it by creating a "Named Query". In this query, I will group the fact table records by the transaction ID. In this way, I obtain the sales amount per transaction, not per item. It looks good.

For issue 2, I use the "Named Query" that just created a bit further. In that query, besides the total amount per transaction. I create another field which is a floored amount. I am using this function.

floor(convert(decimal, sum(ItemAmount)) / 50) * 50

By doing this, I am able to make those sales total into the starting value of their groups. e.g. 38 returns 0, 59 returns 50 and 160 returns 150.
It seems really good at first. However, I have another problem to make this perfect or really usable. In SSAS, if there's no data exists for a specific group. It won't get display. e.g. if I got 38, 59 and 160 in my sales order total. I will only get the groups 0~49, 50~99 and 150~149. The problem is the missing 100~149. For business user, I think it's not acceptable to have a gap in the report like this. So, how can I fill in this gap?

Moreover, is there any best practice for my situation? I think this is a very common scenario but I can't find any useful reference.

Regards,
Alex|||

Hi Alex:

Best practice is creation of a hierarchy which has the "bucket" ranges you want. Then link each fact table record to the appropriate bucket with a foreign key. It's a common practice and used in most implementations. Below is a link to a series of articles by Bill Pearson which articulate (very well) the functionality you're looking for and a lot more. Good luck.

PGoldy

|||Dear PGoldy,

Could you please check whether the links has been posted? Thanks!

Regards,
Alex|||

Hi Alex. Sorry about the delay. Below is the link. PaulG

http://www.databasejournal.com/article.php/1459531

sql

Grouping on multiple Datasets

is it possible to group 2 or more datasets?
i.e i have a row that returns count of sales, ATV etc from one view
and paidout sales from another view this has to come from 2 datasets
due to them having different selection criteria
i am grouping by region on dataset1 but need to group by region on
dataset2 aslo?
Any help much appreciatednot possible today.
An option is to create a subreport.
The first report group the objects from the first dataset, the subreport
display the data from the second dataset filtered by a paremeter which is
the region.
"blueboy" <matt_meech@.hotmail.com> wrote in message
news:1181571015.624028.95320@.c77g2000hse.googlegroups.com...
> is it possible to group 2 or more datasets?
> i.e i have a row that returns count of sales, ATV etc from one view
> and paidout sales from another view this has to come from 2 datasets
> due to them having different selection criteria
> i am grouping by region on dataset1 but need to group by region on
> dataset2 aslo?
> Any help much appreciated
>|||many thanks|||It depends on what you are doing.
Are the datasets just select commands?
If so a simple union query is all that is required.
It you have a table per dataset, then you group each one accordingly.
More information is required but there are always many
'workarounds'...
Regards,
Tom Bizannes
Reporting Services Designer
Sydney Australia

Grouping

I'm trying to write a report and need to group columns that are alike.
For example.

DeptID JobTitle

40344 Sales Clerk
1st Assistant
Store Manager

40666 Sales Clerk
2nd Assistant
Store Manager

Sorry, it will not output correctly, I hope you get what I'm trying to do.
I can get the results above but it puts the deptid for each job title. I do not want it. Any help will be appreciated. Thanks in advance.Why? This is just a display issue. Can't you handle it in the frontend?|||Not if its just a query. I was thinking about doing the whole cursor deal and looping through that to give me my desired results but i thought it would be an easier way.|||You could do it as two selects. Select into a table variable with an identity column. Then select from the table variable and show blank if it's not the max(identity) for the given DeptID. Make sense?|||Yes, thank you.|||What about appending a carriage return to the last field: + char(13)?|||What about appending a carriage return to the last field: + char(13)?If you pursue that route, you need to "crelf it"... Instead of just a carriage return, you need a carriage return followed by a line feed which is: + Char(13) + Char(10)

-PatP

Group By/sub query problems

I'm trying to list salesreps (if they have any sales for a particular
date) with their total sales amounts for a queried date, but when
running this sql string in QueryAnalyzer, it says there is an error
with syntax on Line 1 near "s" :
SELECT o.Rep_ID, o.ID, s.ID, SUM(b.orderamount) AS totalsales,
b.order_ID
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN
orders o ON o.Rep_ID = s.ID INNER JOIN
b ON o.ID = b.Deal_ID
GROUP BY d.Rep_ID, d.ID, s.ID, b.order_ID
HAVING (SUM(b.orderamount) > 0)
?
NetSportsYou can only give a subquery one alias, you are trying to give it two
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') SalesReps s
Should be:
FROM (SELECT b.Deal_ID
FROM btransactions b
WHERE b.BoardDate = '20050815') s
However, b is not in your outer query either.
The absolute best way to get a specific and useful answer is to post proper
specs (DDL, sample data, desired results). Check out
http://www.aspfaq.com/5006 which will help you get more constructive
responses.
"netsports" <ballz2wall@.cox-dot-net.no-spam.invalid> wrote in message
news:3bSdneDoYqzc2p_eRVn_vQ@.giganews.com...
> I'm trying to list salesreps (if they have any sales for a particular
> date) with their total sales amounts for a queried date, but when
> running this sql string in QueryAnalyzer, it says there is an error
> with syntax on Line 1 near "s" :
> SELECT o.Rep_ID, o.ID, s.ID, SUM(b.orderamount) AS totalsales,
> b.order_ID
> FROM (SELECT b.Deal_ID
> FROM btransactions b
> WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN
> orders o ON o.Rep_ID = s.ID INNER JOIN
> b ON o.ID = b.Deal_ID
> GROUP BY d.Rep_ID, d.ID, s.ID, b.order_ID
> HAVING (SUM(b.orderamount) > 0)
> ?
> NetSports
>

GROUP by clause, /sub query problems

I'm trying to list salesreps (if they have any sales for a particular date) with their total sales amounts for a queried date, but when running this sql string in QueryAnalyzer, it says there is an error with syntax on Line 1 near "s" :
SELECT o .Rep_ID, o .ID, s.ID, SUM(b.orderamount) AS totalsales, b.order_ID

FROM (SELECT b.Deal_ID

FROM btransactions b

WHERE b.BoardDate = '20050815') SalesReps s INNER JOIN

orders o ON o .Rep_ID = s.ID INNER JOIN

b ON o.ID = b.Deal_ID

GROUP BY d .Rep_ID, d .ID, s.ID, b.order_ID

HAVING (SUM(b.orderamount) > 0)
?
.NetSports

You have a space character following your "o" table alias throughoutyour query. Remove that extra space whereever it appears and seeif that clears up your problem.

group by clause

Hi all,
i have 2 tables in pubs database, first one is titles and second
one is sales,
i want to get sum of quantity which is in sales table along with title_id
and title.
i'm executing this query
select titles.title_id,title,sum(qty) as quantity from titles join sales on
sales.title_id=titles.title_id group by titles.title_id
This is giving some error,I'm not able to find, where this error comingWhat error?
Anyway, look up GROUP BY in Books Online. You're selecting two columns (not
counting the aggregate), but only grouping by one.
ML
http://milambda.blogspot.com/|||Manish ML is right...
SELECT
titles.title_id
,title
,SUM(qty) as quantity
FROM
titles join sales on
sales.title_id=titles.title_id
GROUP BY titles.title_id
is what you had!
you just need to add the title column to the GROUP BY like so
SELECT
titles.title_id
,title
,SUM(qty) as quantity
FROM
titles join sales on
sales.title_id=titles.title_id
GROUP BY titles.title_id, titles.title
The reason: if a column is not in an aggregate function in the SELECT list
of a GROUP BY query, it must be included in the GROUP BY clause, therefore
you don't have to put SUM(qty) (since it's in an aggregate function) in the
GROUP BY, and in fact can't.
Regards
CharlesA

GROUP BY and populating temp table, ideas?

Hello All,
I have the following table, and want to create a report as seen below.
CREATE TABLE [dbo].[Sales] (
[ACTIVITY_ID] [varchar] (16) NOT NULL ,
[CREATED_BY] [varchar] (10) NULL,
[YEAR] [varchar] (9) NULL ,
[PERIOD] [varchar] (2) NULL ,
[WEEK] [char] (1) NULL ,
[AMOUNT] [varchar] (3) NULL
) ON [PRIMARY]
GO
I need to see count(*) of each rep for each w and current preriod.
Something like this:
REP_NAME W1 W2 W3 W4 W5 Period(Month)
======== ===== ===== ===== ===== ===== =============
DAVID 5 10 5 20
WILLIAM 2 8 5 15
JANE 10 2 10 22
Do I need to run seperate group by's for each w and populate a temp table
?
Or there can be a simpler way to do that?
Thanks,
Ada
--
SQL Server DBAFirst of all, pivoting data for presentation purposes does not belong on the
data layer.
But if you really, really, really need to do it in T-SQL read this:
http://www.windowsitpro.com/Article...15608.html?Ad=1
ML