Hi,

I am using Self-Service

I have a Log item in the database is optionally linked to Product entity also stored in the database. I need to summarize the net volumes of Product entities involved these entities on a weekly basis.These net-volumes are calculated via business rules. I.e. not stored in the database. I decided to add some properties the Log entity, e.g ValueType1 and ValueType2 and retrieve these values from a Product entity. I also defined the properties Weeknumber and Year from a DateTime field stored in the database. E.g. now I have a log-entity with the following attributes

Year Weeknumber DateTimeOfStore ValueType1 ValueType2

I want to show a gridview of something like that only showing the year, week and the summarized values per week. I.e. rows showing

Year Weeknr Sum(ValueType1) Sum(ValueType2)

and an end row showing the accumulated data on a yearly basis.

Is this possible using an LLBLGenDataSource? Can you give me an example? If not, what would you suggest. Caculating the net volume of a Product entity in the database is not an option.

Thanks,

Jan

Is this easy to do in SelfServicing. Can I define this in predicates that I link to the LLBLGenDataSource?

You have to use a DynamicList with groupBy clause (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=2560&StartAtMessage=0&#14480) for the sum.

If you want to extract the Year from the datetime field, you should use a DBFuncionCall (Please read LLBLGenPro Help - Using the generated code - Calling a database function).

I don't see a better way (Adapter vs. SS). To point the differences between SelfServicing and Adapter templatesets please read LLBLGenPro Help - Concepts - Templates and Template groups.

If you need further explanation and examples, please let us know.

daelmo wrote:

If you want to extract the Year from the datetime field, you should use a DBFuncionCall

In general I agree with this remark but in this specific situation I do not agree.

I store a DateTime variable in the database and use an application function for datamanipulation such as determining the weeknumber or the year. Since it is complicated to determine the weeknumber in T-SQL I did it this way. It is very well possible that the 2nd of Januari of a certain year is part of week 53 of the previous year (Gregorian Date, ISO 8601).

JayBee wrote:

daelmo wrote:

If you want to extract the Year from the datetime field, you should use a DBFuncionCall

In general I agree with this remark but in this specific situation I do not agree.

I store a DateTime variable in the database and use an application function for datamanipulation such as determining the weeknumber or the year. Since it is complicated to determine the weeknumber in T-SQL I did it this way. It is very well possible that the 2nd of Januari of a certain year is part of week 53 of the previous year (Gregorian Date, ISO 8601).

Aren't Weeknumbers a standard? Why would sqlserver report a different weeknumber?

Anyway, I get the impression you want to summarize the columns on the client, using your own code?

I connect to a SQLServer 2000 database. As far as I know there is no standard weeknumber function available.

Depending on the start of the week the weeknumber can different. Some people, companies, cultures let the week start on Sunday, others on Monday.

If 4 days or more fall in December, the whole week belongs to the previous year.

I was hoping I could use LLBLGen aggregate functionality on user-added properties. In the mean time I have created an ISOWeek function in T-SQL, but would still prefer to use code. It seems so much more flexible.

if you already have the code in a tsql function you just need to refactor it to code. instead of temp tables use List<T> or Dictionary<T,K> to temporarly hold values. you will also need to hold company/cultural rules.

JayBee wrote:

I connect to a SQLServer 2000 database. As far as I know there is no standard weeknumber function available.

Depending on the start of the week the weeknumber can different. Some people, companies, cultures let the week start on Sunday, others on Monday.

If 4 days or more fall in December, the whole week belongs to the previous year.

I was hoping I could use LLBLGen aggregate functionality on user-added properties. In the mean time I have created an ISOWeek function in T-SQL, but would still prefer to use code. It seems so much more flexible.

To determine the weeknumber, use DATEPART("wk"... . To specify which day the week starts, use SET DATEFIRST. It defaults to sunday.

See books online of sqlserver 2000 I think with DATEPART you can get what you want, and with SET DATEFIRST, you can set that value with a small proc: - open connection - call proc to set DATEFIRST, if it isn't a sunday the weekday starts with - run query with DATEPART - close connection.

The year and weeknumber are not the only issue. I have calculations of this both in TSQL and in csharp.

I want to summarize over values that are also based on complex calculations of which I allready have the code in csharp.

In the database I have an entity Log with attributes

DateTime, Value1, Value2

In LLBLGen I have an object LogEntity with properties

DateTime, Value1, Value2, Year, Weeknr, Daynr, CalculatedValue1, CalculatedValue2

In a webapplication I want to show a table with the following

Year, Weeknr, Sum(CalculatedValue1), Sum(CalculatedValue2)

and a year total

Year,Sum(CalculatedValue1), Sum(CalculatedValue2)

Before using LLBLGen I used to build my own datatable and bind this to a datagrid. Now I want to use the 2.0 controls (gridview)and a LLBLGen datasource to achieve this.

Before using LLBLGen I used to build my own datatable and bind this to a datagrid. Now I want to use the 2.0 controls (gridview)and a LLBLGen datasource to achieve this.

I see no other way for client side grouping but what you previously did, using a datatable and some loops.

Perhaps I miss something, but I was under the impression that the problem was the calculation of the weeknumber in sqlserver. If that's not the problem, why can't you use a groupby on the date values with 2 fields which hold teh AggregateFunction.Sum and an expression which is the calculation to sum ?

Otis wrote:

Perhaps I miss something, but I was under the impression that the problem was the calculation of the weeknumber in sqlserver. If that's not the problem, why can't you use a groupby on the date values with 2 fields which hold teh AggregateFunction.Sum and an expression which is the calculation to sum ?

Hi Frans,

Perhaps the question should be: does the group by and summation work on non-database (i.e. manually added) properties of an entity so that I can use a LLBLGenDataSource as the datasource of the gridview displaying this all.

Using LLBLGen I stumble from issue to issue. Once I find out how things work, I reuse the approach. This one was taking to much time and I started wandering if it should work. If not, I would try something different such as the solution suggested by Daelmo.

That's not available out of the box, you'd have to do the grouping manually.

OK, so it does not support what I would like. I'm considering to create an xsd/xml file and use that as the source for the gridview or of a report component.

In-memory filters etc. don't support groupings, that's correct. (neither does the datatable, otherwise you could have fetched the query data into a dynamic / typed list and grouped the result in-memory)

A datasource control isn't really useful if the data is readonly like in this case. It then adds overhead to the page and you're better off by simply binding the data to a grid directly, as the page is read-only anyway: you're not editing that grouped data.

We needed to do the same thing...

We are using the SyncFusion Essential Grouping and it does what you are looking for...

Not free tho..

It is not that difficult to group objects in an arbitrary collection and summarize over individual attributes. It would be easier if I could do that on an EntityCollection.

I wrote some code to do what I want and found out it is to slow to be usefull. I will define a table containing the daily volumes and than I can use either CrystalReports or SQL Server reporting services to build a report showing what I want.

I still wonder why the grouping can't be done in the db query...

Otis wrote:

I still wonder why the grouping can't be done in the db query...

As mentioned a couple of mails earlier, I have to do some calculations on the fields and then summarize the result. Sure, I would probably be able to do this calculation using UDFs in T-SQL, but this is easier to do in Csharp.

Fair enough