custormizable report using a crosstab query

[Peter Herath]

I want to create a dynamic report using a crosstab query...... pls someone look into my attached example database and help me out to do the report generation....

example is like dis...:

there are 4 combo boxes.. when u select an item from each combo box and click the ViewReport Button then those selected item values should go as parameters to the crosstab query in the vba code subroutine and from that the report should be generated.

pls someone out there help me quickly

[Rabbit]

You'll have to build the SQL string in code and then use that to create a query def that your report is based on. You'll also have to open the report in design view and delete or add fields that the user chose. You can do this using code.

You let them choose fields for the crosstab but you didn't mention anything about letting the user choose whether it will be a row heading, column heading, or value. And if it's a value, what kind of value.

[Denburt]

You didn't state the version of Access you are using but the following link explains things in detail.


Dynamic Report Using a Crosstab query

[Peter Herath]

I want to create custormizable report using a crosstab query. For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go to the crosstab query as parameters and execute the query and then generate the report... here's the code for the crosstab query :-
-----------------------------------------------------
(cboColumns/cboProjectTitle/cboRows/cboIndicator)
These are the combobox parameters pass to the crosstab query after button's being clikced.
------------------------------------------------------------

(sSql = "TRANSFORM Max(IndicatorData.nValue) AS MaxOfnValue " + _
"SELECT " + Trim(Replace(cboRows, "_", ".")) + " " + _
"FROM [Indicator], IndicatorData, Region, Project " + _
"WHERE Project.tProjTitle='" + Trim(cboProjectTitle) + "' AND Project.nProjId=Indicator.nProjId AND Indicator.tIndicatorName='" + cboIndicator + "' AND Indicator.nIndicatorId=IndicatorData.nIndicatorId AND Region.nRegionId=IndicatorData.nRegionId " + _
"GROUP BY IndicatorData.nRegionId, " + Trim(Replace(cboRows, "_", ".")) + " " + _
"PIVOT Format(" + Trim(Replace(cboColumns, "_", ".")) + ",'yyyy') "
)

pls someone help me out to do this report or tell me is there a way to do this kind of requirement(this is a real customer requirement).

[Rabbit]

Please do not double post. And do try to respond to the original thread when someone has offered some help.

MODERATOR

[Peter Herath]

I want to create custormizable report using a crosstab query.For an example, there's a form with four combo boxes and two of them having database tables columns/field names as values in the combo box(one for select row filed and other one for select column filed in the report) when u select items in that combo boxes and press a Button then selected items should go to the crosstab query as parameters and execute the query and then what i did was i created a temparary table and insert the values into that temp table using the crosstab query. now what i want is to generate the report using that temp table but every time user selects different row headings and column headings then the report will be changed. so i found a way to create a report using VBA code but at the moment im stuck with dat code.... here's the code for the crosstab query and the temp table and the code for generate the report. :-
-----------------------------------------------------
(cboColumns/cboProjectTitle/cboRows/cboIndicator)
These are the combobox parameters pass to the crosstab query after button's being clikced.
------------------------------------------------------------
Cross Tab Query

(sSql = "TRANSFORM Max(IndicatorData.nValue) AS MaxOfnValue " + _
"SELECT " + Trim(Replace(cboRows, "_", ".")) + " " + _
"FROM [Indicator], IndicatorData, Region, Project " + _
"WHERE Project.tProjTitle='" + Trim(cboProjectTitle) + "' AND Project.nProjId=Indicator.nProjId AND Indicator.tIndicatorName='" + cboIndicator + "' AND Indicator.nIndicatorId=IndicatorData.nIndicatorId AND Region.nRegionId=IndicatorData.nRegionId " + _
"GROUP BY IndicatorData.nRegionId, " + Trim(Replace(cboRows, "_", ".")) + " " + _
"PIVOT Format(" + Trim(Replace(cboColumns, "_", ".")) + ",'yyyy') "
)
-------------------------------------------------------------------------------------------------------------------
Temp Table

Set dbCurr = DBEngine.Workspaces(0).Databases(0)
Dim recCrossTab As Recordset
Dim sString, sFields, sValues As String
sString = "CREATE TABLE X ("
Set recCrossTab = dbCurr.OpenRecordset(CrossTabSql)
For i = 0 To recCrossTab.Fields.Count - 1
If i = recCrossTab.Fields.Count - 1 Then
sString = sString + recCrossTab.Fields(i).Name + " Text(50) "
sFields = sFields + recCrossTab.Fields(i).Name
Else
sString = sString + recCrossTab.Fields(i).Name + " Text(50), "
sFields = sFields + recCrossTab.Fields(i).Name + ", "

End If
Next

sString = sString + ") "
Debug.Print sString
DoCmd.RunSQL "Drop Table X"
DoCmd.RunSQL sString


Dim InsertStr As String
InsertStr = "INSERT INTO X (" + sFields + ") values ("

''InsertStr = Replace(InsertStr,"'",""")

recCrossTab.MoveFirst

While (recCrossTab.EOF = False And recCrossTab.BOF = False)
For b = 0 To recCrossTab.Fields.Count - 1
If b = recCrossTab.Fields.Count - 1 Then
sValues = sValues + "'" + CStr(recCrossTab.Fields(recCrossTab.Fields(b).Name )) + "' "
Else
sValues = sValues + "'" + CStr(recCrossTab.Fields(recCrossTab.Fields(b).Name )) + "', "
End If

Next
InsertStr = "INSERT INTO X (" + sFields + ") values (" + sValues + ") "
Debug.Print InsertStr

DoCmd.SetWarnings False
DoCmd.RunSQL InsertStr

InsertStr = "INSERT INTO X (" + sFields + ") values ("
sValues = ""

recCrossTab.MoveNext

Wend
-------------------------------------------------------------------------------------------------------------------

Creating the Report using the VBA Code. This Code is in the Code View of the Report called Custom Report. (Code is taken from:- http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part3/ch15.mspx)

Private Property Let CustomReport_Source(RHS As String)
' Create report based on specified data source.
Dim txtNew As Access.TextBox
Dim lblNew As Access.Label
Dim rstSource As ADODB.Recordset
Dim fldData As ADODB.Field
Dim lngTop As Long
Dim lngLeft As Long
lngLeft = 0
lngTop = 0
' Set report's RecordSource property.
Me.Report.RecordSource = RHS
' Open recordset on specified record source.
Set rstSource = New ADODB.Recordset
rstSource.Open "SELECT * FROM [" & RHS & "];", _
CurrentProject.Connection, adOpenForwardOnly
' Create corresponding label and text box controls for each field.
For Each fldData In rstSource.Fields
' Create new text box control and size to fit data.
Set txtNew = CreateReportControl(Me.Report.Name, acTextBox, _
acDetail, , fldData.Name, lngLeft + 1500, lngTop)
txtNew.SizeToFit
' Create new label control and size to fit data.
Set lblNew = CreateReportControl(Me.Report.Name, acLabel, acDetail, _
txtNew.Name, fldData.Name, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFit
' Increment top value for next control.
lngTop = lngTop + txtNew.Height + 25
Next
CustomReport_Source_End:
On Error Resume Next
rstSource.Close
Set rstSource = Nothing
Exit Property
End Property
-------------------------------------------------------------------------------------------------------------------
In here we can send the created temp table but I cant access the above property. so i created a sub-routine. and call that in the following Sub Report_Open() method. But It gives an error like this "U must be in design view to create or delete Controls".


Private Sub Report_Open(Cancel As Integer)
CustomReport_Source("X")
End Sub

pls someone help me out or please tell me is there any other way to do this kind of requirement.

[Rabbit]

Just like the error says, you have to open the report in design view to use the code that creates the report. acViewDesign instead of acViewPreview.