Customised SQL query report

[dan_williams]

I am attempting to create a ASP.NET report whereby users can specify
which columns they wish to be able to view depending on the options
they select from a CheckBoxList.

I have 4 tables, ClientGroups, Clients, Bookings & Depts.

ClientGroups Clients Bookings
-------------- ------------------ -------------------
cgId PK int ClientId PK int BookingId PK int
cgName varchar ClientName varchar ClientId FK int
cgId FK int Hours money
deptId FK int

Depts
----------------
DeptId PK int
DeptName varchar

I'd like the report to display a list of all Client Groups and related
clients, and all other clients that don't belong to a group for a
particular department (dependant on which departments the user selects
to view).

Another column option to view is a Total Hours column, which is a sum
of all Bookings hours for that client group and/or client. Additional
column options include the department name, and others that i haven't
included in my table descriptions above.

Anyone got any suggestions how i can go about this?

I initially tried to create a dynamic SQL query based on the options
selected, but was struggerling on how to do a running total for the
Client Groups, and individual clients, and generally it was becoming
very messy.

I next tried to make use of a dataset and datatable but have run into
issues with using INNER JOINS with an SqlDataAdapter to populate a
dataset and using dataset relations. Errors i experienced was the
"These columns don't currently have unique values" as not all clients
belong to a client group, etc.

If anyone can point me in the right direction or has any advice, i'd be
much appreciated.

Thanks in advance

Dan Williams.

 

[W.G. Ryan - MVP]

Dan - if you grab the data you need - you can use Client side methods with
DataTables (ie .Select to filter your rows and .Compute to Aggregate them
based on conditions) http://www.knowdotnet.com/articles/expressions.html
This shoudl probably get you what you need.

HTH,

Bill