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(IndicatorDa ta.nValue) AS MaxOfnValue " + _
"SELECT " + Trim(Replace(cb oRows, "_", ".")) + " " + _
"FROM [Indicator], IndicatorData, Region, Project " + _
"WHERE Project.tProjTi tle='" + Trim(cboProject Title) + "' AND Project.nProjId =Indicator.nPro jId AND Indicator.tIndi catorName='" + cboIndicator + "' AND Indicator.nIndi catorId=Indicat orData.nIndicat orId AND Region.nRegionI d=IndicatorData .nRegionId " + _
"GROUP BY IndicatorData.n RegionId, " + Trim(Replace(cb oRows, "_", ".")) + " " + _
"PIVOT Format(" + Trim(Replace(cb oColumns, "_", ".")) + ",'yyyy') "
)

pls someone help me out to do this report or tell me is there a way to do this kind of requirement(thi s 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(IndicatorDa ta.nValue) AS MaxOfnValue " + _
"SELECT " + Trim(Replace(cb oRows, "_", ".")) + " " + _
"FROM [Indicator], IndicatorData, Region, Project " + _
"WHERE Project.tProjTi tle='" + Trim(cboProject Title) + "' AND Project.nProjId =Indicator.nPro jId AND Indicator.tIndi catorName='" + cboIndicator + "' AND Indicator.nIndi catorId=Indicat orData.nIndicat orId AND Region.nRegionI d=IndicatorData .nRegionId " + _
"GROUP BY IndicatorData.n RegionId, " + Trim(Replace(cb oRows, "_", ".")) + " " + _
"PIVOT Format(" + Trim(Replace(cb oColumns, "_", ".")) + ",'yyyy') "
)
-------------------------------------------------------------------------------------------------------------------
Temp Table

Set dbCurr = DBEngine.Worksp aces(0).Databas es(0)
Dim recCrossTab As Recordset
Dim sString, sFields, sValues As String
sString = "CREATE TABLE X ("
Set recCrossTab = dbCurr.OpenReco rdset(CrossTabS ql)
For i = 0 To recCrossTab.Fie lds.Count - 1
If i = recCrossTab.Fie lds.Count - 1 Then
sString = sString + recCrossTab.Fie lds(i).Name + " Text(50) "
sFields = sFields + recCrossTab.Fie lds(i).Name
Else
sString = sString + recCrossTab.Fie lds(i).Name + " Text(50), "
sFields = sFields + recCrossTab.Fie lds(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(InsertS tr,"'",""")

recCrossTab.Mov eFirst

While (recCrossTab.EO F = False And recCrossTab.BOF = False)
For b = 0 To recCrossTab.Fie lds.Count - 1
If b = recCrossTab.Fie lds.Count - 1 Then
sValues = sValues + "'" + CStr(recCrossTa b.Fields(recCro ssTab.Fields(b) .Name)) + "' "
Else
sValues = sValues + "'" + CStr(recCrossTa b.Fields(recCro ssTab.Fields(b) .Name)) + "', "
End If

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

DoCmd.SetWarnin gs False
DoCmd.RunSQL InsertStr

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

recCrossTab.Mov eNext

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.c om/technet/prodtechnol/office/office2000/proddocs/opg/part3/ch15.mspx)

Private Property Let CustomReport_So urce(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.Recor dSource = RHS
' Open recordset on specified record source.
Set rstSource = New ADODB.Recordset
rstSource.Open "SELECT * FROM [" & RHS & "];", _
CurrentProject. Connection, adOpenForwardOn ly
' Create corresponding label and text box controls for each field.
For Each fldData In rstSource.Field s
' Create new text box control and size to fit data.
Set txtNew = CreateReportCon trol(Me.Report. Name, acTextBox, _
acDetail, , fldData.Name, lngLeft + 1500, lngTop)
txtNew.SizeToFi t
' Create new label control and size to fit data.
Set lblNew = CreateReportCon trol(Me.Report. Name, acLabel, acDetail, _
txtNew.Name, fldData.Name, lngLeft, lngTop, 1400, txtNew.Height)
lblNew.SizeToFi t
' Increment top value for next control.
lngTop = lngTop + txtNew.Height + 25
Next
CustomReport_So urce_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(Can cel As Integer)
CustomReport_So urce("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.