473,320 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Customised SQL query report

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.

Oct 5 '05 #1
1 1910
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
<da**********@newcross-nursing.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...
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.

Oct 7 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Jay Best | last post by:
Hi there The story is as follows: I write up some pretty detailed reports on people, and what I am wanting to do is speed up the process as a proportion of the reports that I am writing are...
4
by: Richard Hollenbeck | last post by:
The following query takes about one second to execute with less than 1,000 records, but the report that's based on it takes from 15-30 seconds to format and display. That's frustrating for both me...
15
by: Richard Hollenbeck | last post by:
I tried to ask this question before on the 14th of January but I never got a reply. I'm still struggling with the problem. I'll try to rephrase the question: I have a crosstab query with rows...
1
by: dan_williams | last post by:
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,...
4
by: Jimmy | last post by:
I have a form with a command button on it that is supposed to open up a report and use a query to populate it. DoCmd.OpenReport "rptMailingList", acViewPreview, "qryMailingListChristmas" ...
4
by: Simon | last post by:
Dear reader, The syntax for Docmd.OpenReport is: OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs) Example The following example prints Sales Report while...
4
by: lorirobn | last post by:
Hi, I have a report displaying items that are missing from a room. I created 2 queries, the first getting the items IN the room, and the second being an "unmatched" query that references the...
6
by: Phil Stanton | last post by:
I am running a query that calls a function used to format addresses depending on the width of a control on a report that shows that address. The same query is used as the RecordSource of lots of...
3
by: panteraboy | last post by:
Hi there i have been deeply frustated as last week trying to figure out how to cycle through records on a form based on a query by using images instead of command buttons . I have a customised view...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.