Data Anaysis and Report generation depending upon user selection

10-23-2004, 02:05 AM
I developed a software for a client who recently asked me to add a feature for Data Analysis. He wants that Reports should be generated depending on his record selection criteria. He wants to select fields from tables and the Report should be generated depending upon his selection. I am puzzled as to how to add this feature in VB 6 so that a client can retrieve desired information from Access XP database. The problem is that client has no knowledge of SQL and I have to apply any technique with which even a novice can retrieve any information.
Is there anything to do with DataWarehouse?

Dennis DVR
10-23-2004, 03:47 AM
what you should do is to create a dynamic sql based on the user selected fields and criteria and use this dynamic sql string as your SQL queries for your report.

check out this image, it is an example of a nice screen of user interface in making a dynamic queries based on the users selection and criteria.

10-23-2004, 09:15 AM
Thanks a lot, the image says a lot. Can you supply some more related links?

Dennis DVR
10-23-2004, 09:32 AM
Thanks a lot, the image says a lot. Can you supply some more related links?

I don't know what related links are you referring to, are you referring on how to create a dynamic queries? This forum has a lot of good example about creating a query at runtime, and if you are looking for a dynamic queries using stored procedure (for SQL Server) then you might want to look at this link

10-23-2004, 10:12 AM
I am using SQL commands not stored procedures. I have decided to transfer the user selected data to MS Excel so that user can add/delete/calculate desirably.

With related links I meant similar screens or sample projects.

10-25-2004, 11:06 AM
That screen shot is of a search form that I'm working on, but which got put on the back burner for a bit. (i.e. - it's not finished, and given my current workload, it may not be for a while, LOL)

Essentially, you would add a series of Select Case statements to an event, (or, use the index number when you click the option button) and evaluate the option button values, in order to build a SQL statement string containing criteria that matches your requirements.

I like to use tags in a SQL string, so for a simple statement (without the and/or options) I would do something like this:

' Base SQL String
Const strBaseSQL as String = "Select * from TableName Where ~FIELD~ ~COMP~ ~VALUE~ "
Dim strSQL as String
' ...

Private Sub optSearch_Click(Index as Integer)
' Adjust for Search Field
strSQL = strBaseSQL ' Each time you select a search field, start over
strSQL = Replace (strSQL, "~FIELD~", optSearch(Index).Tag
End Sub

Begin Sub optComparison_Click
Select Case opt_Comparison.Index
Case 0 ' equals
txtRange2.Visible = False ' Don't need the second textbox if not a range
strSQL = Replace(strSQL, "~COMP~", " = ")
Case 1 ' Contains
txtRange2.Visible = False
strSQL = Replace(strSQL, "~COMP~", " LIKE ")
strSQL = Replace(strSQL, "~VALUE~", " ~%VALUE%~" )
Case 2 ' Begins With
txtRange2.Visible = False
strSQL = Replace(strSQL, "~COMP~", " LIKE ")
strSQL = Replace(strSQL, "~VALUE~", " ~VALUE%~" )
Case 3 ' Range
txtRange2.Visible = True
strSQL = Replace(strSQL, "~COMP~", " BETWEEN " )
strSQL = Replace(strSQL, "~VALUE~", " ~VALUE1~ AND ~VALUE2~" )
End Select
End Sub

Note: I use the .Caption or .Tag property of a control as much as possible, so I won't have to type as much. In the case above, the control's caption holds the field description, while the Tag holds the field name.

Also, when you get to the field value, you'll need to look at the field type, to add appropriate delimiting characters. If an Access DB, use "#" for dates, " ' " for strings, and NOTHING for numeric field types. Maybe, for a fixed application, add an array of delimiting characters to use one element for each field. In a general purpose routine, you would need to look at the table definition to determine the type.

10-25-2004, 01:32 PM
Thanks a lot.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum