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 data by x-axis labels

Hey guys,

I have thousands of records in the data source(i.e. Cube), which includes datetime information. I want to aggregate and present the data on a bar chart on monthly basis. That means the interval between the dates should be one month. Assume that the dates value will be labeled on the x-axis of the bar chart.

I tried to find out the solution for many days. I will really appreciate if anybody give me some idea.

Sincerely,

Amde

Try creating a category group with two expressions, using the following grouping expressions.

=Fields!DateField.Value.Year
=Fields!DateField.Value.Month

For the label you could use something like, =Fields!DateField.Value.ToShortDateString()

You could also use two category groups, if you wanted an inner set of labels for the month and another outer set for the year.|||

Hi,

Thank youy for your feedback, however that doesn't solve my problem: here is the thing;

I created a bar chart report. The x-axis value of this chart is a timestamp(datetime) field of a dimension. Thousands of records are inserted in to this field everyday, as a result, I will have the timestamp information every second or minute. So here is the thing, If for instance, I want to present last 5 months data in the bar chart(x-axis), the chart can not accomodate all the data and it doesn't look good to present a data which occured every minute or hours. So I want to present the data on monthly basis based on the StartDate and EndDate parameters value provided by the user.

Assume the user wants to preview 5 months record from 2006-03-04 to 2006-07-04, the data should be aggregated and presented on monthly basis as shown below, instead of directly displaying all the data as they appear in the dataset.

---

2006-03-04 2006-04-04 2006-05-04 2006-06-04 2006-07-04

Please let me know if you need more clarification.

Sincerely,

Amde

|||Adding the category fields with the groupings mentioned above should produce the grouping structure you are looking for. When you tried it what happened that was incorrect?

The bounds provided by the StartDate and EndDate parameters can either be used in the the sql query. Or, if it can't be done there, then you can set a filter for the category group. Also, the reason there is a group expression for Year is that the data may span multiple years and I'm assuming that you don't want the data for the same month in multiple years to be aggregated together.

Here is a sample report that uses the northwind database to show the number of orders placed for each month. It contains a bar chart and two parameters, which are used in the sql query.

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="Northwind">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=localhost; Initial Catalog=Northwind</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>40232364-d6a5-4917-bcad-13308e3a8f62</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="StartDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>7/1/1996</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Start Date</Prompt>
</ReportParameter>
<ReportParameter Name="EndDate">
<DataType>DateTime</DataType>
<DefaultValue>
<Values>
<Value>11/30/1996</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>End Date</Prompt>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Plain</Subtype>
<Title />
<Height>5.125in</Height>
<CategoryAxis>
<Axis>
<Title />
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.375in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>Northwind</DataSetName>
<Top>0.125in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>LightGrey</BackgroundColor>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Bar</Type>
<Width>6.5in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value.Year</GroupExpression>
<GroupExpression>=Fields!OrderDate.Value.Month</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=MonthName(Fields!OrderDate.Value.Month)</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Count(Fields!OrderID.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>5.75in</Height>
</Body>
<rd:ReportID>6323408e-15e1-4a7f-8151-ac96e7ebf862</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="Northwind">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>="SELECT OrderDate, OrderID FROM Orders WHERE Orders.OrderDate BETWEEN '" & Parameters!StartDate.Value & "' AND '" & Parameters!EndDate.Value & "'"</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="OrderDate">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>OrderDate</DataField>
</Field>
<Field Name="OrderID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>OrderID</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>9.375in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||

Yes, that is correct. The assumption, group expression for year is also correct.

Thank you for your cooperation.

sql

Group by with criteria

I have a Problem with my Select statement!
I want to Select every Record, where ChNr fits the pattern the User
chooses. And sum up the different Source Columns.
So far this one works out fine, but I want to have the sum over ALL ChNr
that fit the pattern given, not a result for every ChNr.
If that is of any interest for you, ChNr always looks the same: three
numbers-three numbers e.g. 481-102, 581-235.
SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY ChNr;
With this Statement I get one Result for every single ChNr. What I want is
ONE result for all ChNr that look like 491-... .
I thought I would have to make a criteria in the Group by part, but I just
can't make it work! I already tried to put another LIKE thing into Group
by, but it just won't do.
Unfortunately the DataBase I am working with is Access.
I am working with Visual Studio and C#.
I hope I made myself clear. It is really not so easy to explain my problem.
Thanks Julia
Message posted via http://www.webservertalk.comIf I understand you correctly, this should do it:
SELECT LEFT(a.ChNr,3) as [NAME], Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL
25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY LEFT(ChNr);
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:2050d4f1fa8c4293b71ff00dc45057d1@.SQ
webservertalk.com...
>I have a Problem with my Select statement!
> I want to Select every Record, where ChNr fits the pattern the User
> chooses. And sum up the different Source Columns.
> So far this one works out fine, but I want to have the sum over ALL ChNr
> that fit the pattern given, not a result for every ChNr.
> If that is of any interest for you, ChNr always looks the same: three
> numbers-three numbers e.g. 481-102, 581-235.
> SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
> BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
> [BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
> (1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
> FROM tbAuswert a
> WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
> GROUP BY ChNr;
>
> With this Statement I get one Result for every single ChNr. What I want is
> ONE result for all ChNr that look like 491-... .
> I thought I would have to make a criteria in the Group by part, but I just
> can't make it work! I already tried to put another LIKE thing into Group
> by, but it just won't do.
> Unfortunately the DataBase I am working with is Access.
> I am working with Visual Studio and C#.
> I hope I made myself clear. It is really not so easy to explain my
> problem.
> Thanks Julia
> --
> Message posted via http://www.webservertalk.com|||The Idea is fine! This is exactly what I need!
I tried it and first I got the message of a missing parameter, so I looked
up left() and now my Statement looks like this:
SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
[BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
(1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
FROM tbAuswert a
WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
GROUP BY LEFT (a.Auswert, 4);
But now I get the following Message:
"You tried to execute a query that does not include the specified
expression 'ChNr' as part of an aggregate function"
So, what could might be the Problem now?
Message posted via http://www.webservertalk.com|||You cannot be grouping by left(col,4) and not including it as part of your
select.
So, change your SELECT DISTINCT a.ChNr to SELECT left(a.ChNr,4).
DISTINCT is redundant here when you do grouping.
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:b7da74bb92c645fb868096d31d45aa70@.SQ
webservertalk.com...
> The Idea is fine! This is exactly what I need!
> I tried it and first I got the message of a missing parameter, so I looked
> up left() and now my Statement looks like this:
> SELECT DISTINCT a.ChNr, Sum(a.St?ckzahl) AS St?ck, Sum(a.[BHL 25?m]) AS
> BHL25, Sum(a.[BHL 32?m]) AS BHL32, Sum(a.[BMRH 25?m]) AS BMRH25, Sum(a.
> [BMRH 32?m]) AS BMRH32, ((1000000/(St?ck*120))*(BHL25+BMRH25)) AS ppm25, (
> (1000000/(St?ck*120))*(BHL32+BMRH32)) AS ppm32
> FROM tbAuswert a
> WHERE (ChNr LIKE "491-%" OR ChNr LIKE "482-%")
> GROUP BY LEFT (a.Auswert, 4);
> But now I get the following Message:
> "You tried to execute a query that does not include the specified
> expression 'ChNr' as part of an aggregate function"
> So, what could might be the Problem now?
> --
> Message posted via http://www.webservertalk.com|||How blind can one woman be'
Thank you so much!!! It Works!
You are a genius! ;-)
Just another small question concerning the DISTINCT.
It might come up, that I have a record double except from the Key (which
are consecutive numbers ), doesen't DISTINCT filter the double out before
summing up?
Message posted via http://www.webservertalk.com|||;-) you're welcome.
See if this example helps:
create table #tmp(i sysname, j int)
insert #tmp select '123-456',1
insert #tmp select '123-456',1
insert #tmp select '123-456',2
insert #tmp select '123-456',2
insert #tmp select '123-456',2
insert #tmp select '456-456',2
insert #tmp select '456-456',3
insert #tmp select '789-456',5
insert #tmp select '789-456',5
insert #tmp select '789-456',1
go
--only distinct j
select left(i,4) i, sum(distinct j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
--regular
select left(i,4) i, sum(j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
--redundant distinct
select distinct left(i,4) i, sum(j) s
from #tmp
where i like '123-%' or i like '789-%'
group by left(i,4)
go
drop table #tmp
go
-oj
"Julia H?rtfelder via webservertalk.com" <forum@.webservertalk.com> wrote in
message news:e2ed74fec66e45c7b222a42b4f653e82@.SQ
webservertalk.com...
> How blind can one woman be'
> Thank you so much!!! It Works!
> You are a genius! ;-)
> Just another small question concerning the DISTINCT.
> It might come up, that I have a record double except from the Key (which
> are consecutive numbers ), doesen't DISTINCT filter the double out before
> summing up?
> --
> Message posted via http://www.webservertalk.com

Gridview refresh after update

Hi All,

I am new to development of asp. I have an SQLDataSource set as the data source for a grid view. When I click on the edit link in the Gridview, change the data, and click update, the old data is still displayed in the row.

I found exact same issue as here --http://forums.asp.net/thread/1217014.aspx

Solution in the above thread is to add this

{
if (reader != null) reader.Close();
}
conn.Close();

How do I apply above solution in my situation ?

I am updating through stored procedure.and don't have code at background. My code is

Datasource :

<

asp:SqlDataSourceID="ds"runat="server"ConnectionString="<%$ ConnectionStrings:ds %>"

CancelSelectOnNullParameter="False"ProviderName="<%$ ConnectionStrings:ds.ProviderName%>"UpdateCommand="usp_save"UpdateCommandType="StoredProcedure"EnableCaching="False">

<UpdateParameters>

<asp:ParameterName="field1"Type="String"/><asp:ParameterName="field2"Type="String"/><asp:ParameterName="field3"Type="String"/><asp:ParameterName="field4"Type="String"/><asp:ControlParameterName="field5"Type="String"ControlID="label7"/></UpdateParameters>

Anyone Please ?? Help me with this . I am still not able to find the solution.

Thanks in advacne

|||Where is your select statement?|||

Thanks for the reply .

I do select also using stored procedure. I can post stored procedure code if needed.

Here is the full sqldatasource and function

<asp:SqlDataSource
ID="idpl"
runat="server"
ConnectionString="<%$ ConnectionStrings:idpl %>"
SelectCommand="sp_Mapping"
SelectCommandType="StoredProcedure"
CancelSelectOnNullParameter="False"
ProviderName="<%$ ConnectionStrings:idpl.ProviderName%>"
UpdateCommand="sp_SaveMapping"
UpdateCommandType="StoredProcedure"
OnUpdating="pnl_Updating" EnableCaching="False">
<SelectParameters>
<asp:ControlParameter ControlID="Txt1" Name="OriginalID" Type="String" PropertyName="Text" DefaultValue="" />
<asp:ControlParameter ControlID="Txt2" Name="Name" Type="String" PropertyName="Text" DefaultValue="" />
<asp:ControlParameter ControlID="DDL" Name="sName" Type="String" DefaultValue="None" PropertyName="SelectedValue" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="OriginalID" Type="String" />
<asp:Parameter Name="sName" Type="String" />
<asp:Parameter Name="PartNum" Type="String" />
<asp:Parameter Name="plantName" Type="String"/>
<asp:ControlParameter Name="userID" Type="String" ControlID = "label7" />
</UpdateParameters>
</asp:SqlDataSource>

protected void pnl_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
DbParameterCollection CmdParams = e.Command.Parameters;
ParameterCollection UpdParams = ((SqlDataSourceView)sender).UpdateParameters;

Hashtable ht = new Hashtable();
foreach (Parameter UpdParam in UpdParams)
ht.Add(UpdParam.Name, true);

for (int i = 0; i < CmdParams.Count; i++)
{
if (!ht.Contains(CmdParams[i].ParameterName.Substring(1)))
CmdParams.Remove(CmdParams[i--]);
}

}

|||

Does the database values change?

If no, then the update isn't happening correctly, use the sql profiler to see what is being generated, and why it is failing to update correctly.

If yes, then in the sqldatasource's Updated event, add a gridview.databind and see if that resolves your problem. If it does not, place a breakpoint in the sqldatasource's Selecting event, and make sure that it is getting called after an update.

|||

Yes .. Database value changes.So stored procedure is definitely working.

I'll try to follow your suggestions on updated event and update the post soon.

Thanks for your help.

|||

Hi Motley,

I followed your suggestion.

1. Added gridview.databind at "updated" event.

2. Applied the breakpoint and made sure that the even is getting fired.

Still having the same issue. Gridview still shows two rows. Old and newly updated.

Any more pointers will be greatly appreciated.

Thanks

|||

Not sure if your problem is fixed or not. Sounds like your update statement has truncated to an insert statement. Have you looked into make sure it is pulling the PK of the table that singularly references the field you are looking for?

Gridview & SQL data source

I have a few Stored procedures that return values, some need parameters passing and others don't.

Up to now to access that data in a Web App I have called this procedure using VB after seting up a data block etc.

However I notice that the Web Developer express edition has some tools that look like they should help. I have used the in-built tools to create a SQL data source linked to a details view. I then specified that this should connect to my database (sql2005 hosted) and then specified Custom SQL or Stored procedure.

From the drop down I can then select the SP that purely returns values. When I try to test this query I get an error to say that the query did not return any data tables.

Is there a way to get values returned from a SP in this way?

Regards

Clive

You can use events like (Updating, Updated, Selecting, Selected etc) to do your cutomization. For example, if your method returns an output parameter then:

Sub OnDSUpdatedHandler(ByVal sourceAs Object,ByVal eAs SqlDataSourceStatusEventArgs)If e.AffectedRows > 0Then' Perform any additional processing, such as setting a status label. Label1.Text = Request.LogonUserIdentity.Name & _" changed user information sucessfully!" dim outputvalue asString outputvalue = e.Command.Parameters("yourparamname").Value.ToString()Else Label1.Text ="No data updated!"End If End Sub'OnDSUpdatedHandler

As you can see, I haven't done any kind of error checking. Please ensure that your code does. If you need more help, reply to the post explaining the problem.

|||

I think my problem is earlier than this stage to be honest.

For instance I have a SQL 2005 Stored Procedure that returns values from a table I can run this in SQL server itself and it works fine.

When definign this SP as a datasource using the detailsview object in Visual Web Developer I get the error I mentioend above when runnign the test option while I am stil in the wizard. So it looks liek some problem with callign the SP, yet it works OK natively.

|||

DetailsView, FormView, Gridview etc controls are designed to show a record/records. So, if you are using a SqlDataSource that just returns a single value, you cannot bind that to abovementioned controls. Can you explain why do you need to have a Detailsview that shows only single value?

Whenever asking a question, please add as much info as possible. Otherwise, we have to guess other parts (as you can see from my previous post) which is obviously not what you are looking for. Provide more info about your problem/scenario.

|||

I have a stored procedure that takes no paramteres but returns some values which are calculated from a number of tables to returns some summary results. if I execute the SP from SQL manager I get 5 values returned which appear as output parameters.

I was hoping to be able to link these quickly and easily into a web page using a SQL data source linking to this SP as it's datasource.

I was hoping that the details view would then display the values returned.

There maybe an easier or better way to do this. I am a beginner with all this, and wanted to explore how to use a stored procedure as a datasource.

Regards

Clive

|||

If the stored procedure returns resultset (table with columns and rows) then it would show nicely in the DetailsView, GridView etc. I don't think these controls have builtin ability to look for output parameters and bind those values. I think you should do this programmatically.

Connect to db, run the storedproc, get the results. Then you can either manually populate the values in controls or you can create a collection of values and bind it to a dataaware control. If you need sample code, then reply to the post mentioning your preference.