"Group BY" using SQLQueryString for RDC component

[CrystalVisualBOracle]

Hi there. I have a VB application that creates a series of crystal reports dynamically and exports them. I'm setting the sql statements dynamically using the Report.SQLQueryString = strSelection1 command in the VB code.

My problem is that I'm trying to group-by about four different fields in my sql statement. My report on the other hand only displays one group selection area. My query keeps erroring out saying not a single-group by function. But when I run it in Oracle directly, it's fine. My sql statement is as follows:

strIndivSelection2 = "SELECT " & _
"R_PUBN_RPT_LOG.""R_TIMEPERIOD_ID"", R_PUBN_RPT_LOG.""DATESTAMP"", " & _
"M_PUBLICATION.""PUBNAME"", M_PUBLICATION.""DESCRIPTOR"", " & _
"R_PUBR_RPT_LOG.""M_PUBR_ID"", " & _
"SUM(R_PUBN_RPT_LOG.""UNIQ_USER"&quot, " & _
"SUM(R_PUBN_RPT_LOG.""BAND_RANGE1"&quot, " & _
"SUM(R_PUBN_RPT_LOG.""BAND_RANGE3"&quot, " & _
"SUM(R_PUBR_RPT_LOG.""UNIQ_USER"&quot " & _
"FROM " & _
"""D2ALLLOGS"".""R_PUBN_RPT_LOG"" R_PUBN_RPT_LOG, ""D2ALLLOGS"".""R_PUBR_RPT_LOG"" R_PUBR_RPT_LOG, " & _
"""D2MAIN"".""M_PUBLICATION"" M_PUBLICATION " & _
"WHERE " & _
"R_PUBN_RPT_LOG.""D_SRVR_ID"" = R_PUBR_RPT_LOG.""D_SRVR_ID"" AND " & _
"R_PUBN_RPT_LOG.""R_TIMEPERIOD_ID"" = R_PUBR_RPT_LOG.""R_TIMEPERIOD_ID"" AND " & _
"R_PUBN_RPT_LOG.""DATESTAMP"" = R_PUBR_RPT_LOG.""DATESTAMP"" AND " & _
"R_PUBN_RPT_LOG.""M_PUBN_ID"" = M_PUBLICATION.""ID"" AND " & _
"R_PUBR_RPT_LOG.""M_PUBR_ID"" = M_PUBLICATION.""D_PUBLISHER_ID"" AND " & _
"R_PUBN_RPT_LOG.""DATESTAMP"" = '" & strIndivRunDate & "' AND " & _
"R_PUBR_RPT_LOG.""M_PUBR_ID"" = " & strIndivPubrID & " " & _
"GROUP BY " & _
"R_PUBN_RPT_LOG.""R_TIMEPERIOD_ID"", R_PUBN_RPT_LOG.""DATESTAMP"", " & _
"M_PUBLICATION.""PUBNAME"", M_PUBLICATION.""DESCRIPTOR"", R_PUBR_RPT_LOG.""M_PUBR_ID"" " & Chr$(13) & Chr$(10) & _
"ORDER BY " & _
"R_PUBR_RPT_LOG.""M_PUBR_ID"" ASC, M_PUBLICATION.""DESCRIPTOR"" ASC"

'- Initialize database for this report and send sql query est above ---
IndivReport2.Database.Tables(1).SetLogOnInfo sDB1Server, sDB1Name, sDB1User, sDB1Pwd
IndivReport2.SQLQueryString = strIndivSelection2

My report is grouped by M_PUBLICATION.DESCRIPTOR only.

Does anyone have any suggestions?

THanks,
CrystalVisualBOracle

 

[MalcolmW]

This trick used to work in a similar situation - the first half gives the report what it needs (the structure), and the second half gives it data in a summarized format. I haven't tried with the RDC though - let me know if it works!
strIndivSelection2 =
"SELECT " & _
"R_PUBN_RPT_LOG.""R_TIMEPERIOD_ID"", R_PUBN_RPT_LOG.""DATESTAMP"", " & _
"M_PUBLICATION.""PUBNAME"", M_PUBLICATION.""DESCRIPTOR"", " & _
"R_PUBR_RPT_LOG.""M_PUBR_ID"", " & _
"R_PUBN_RPT_LOG.""UNIQ_USER"", " & _
"R_PUBN_RPT_LOG.""BAND_RANGE1"", " & _
"R_PUBN_RPT_LOG.""BAND_RANGE3"", " & _
"R_PUBR_RPT_LOG.""UNIQ_USER"" " & _
"FROM " & _
"""D2ALLLOGS"".""R_PUBN_RPT_LOG"" R_PUBN_RPT_LOG, ""D2ALLLOGS"".""R_PUBR_RPT_LOG"" R_PUBR_RPT_LOG, " & _
"""D2MAIN"".""M_PUBLICATION"" M_PUBLICATION " & _
[red]"WHERE " & _
"0 = 1" & _
"UNION" & _[/red]
"SELECT " & _
"R_PUBN_RPT_LOG.""R_TIMEPERIOD_ID"", R_PUBN_RPT_LOG.""DATESTAMP"", " & _
"M_PUBLICATION.""PUBNAME"", M_PUBLICATION.""DESCRIPTOR"", " & _
"R_PUBR_RPT_LOG.""M_PUBR_ID"", " & _
"SUM(R_PUBN_RPT_LOG.""UNIQ_USER"&quot, " & _
"SUM(R_PUBN_RPT_LOG.""BAND_RANGE1"&quot, " & _
"SUM(R_PUBN_RPT_LOG.""BAND_RANGE3"&quot, " & _
"SUM(R_PUBR_RPT_LOG.""UNIQ_USER"&quot " & _
"FROM " & _
"""D2ALLLOGS"".""R_PUBN_RPT_LOG"" R_PUBN_RPT_LOG, ""D2ALLLOGS"".""R_PUBR_RPT_LOG"" R_PUBR_RPT_LOG, " & _
"""D2MAIN"".""M_PUBLICATION"" M_PUBLICATION " & _
"WHERE " & _
"R_PUBN_RPT_LOG.""D_SRVR_ID"" = R_PUBR_RPT_LOG.""D_SRVR_ID"" AND " & _
"R_PUBN_RPT_LOG.""R_TIMEPERIOD_ID"" = R_PUBR_RPT_LOG.""R_TIMEPERIOD_ID"" AND " & _
"R_PUBN_RPT_LOG.""DATESTAMP"" = R_PUBR_RPT_LOG.""DATESTAMP"" AND " & _
"R_PUBN_RPT_LOG.""M_PUBN_ID"" = M_PUBLICATION.""ID"" AND " & _
"R_PUBR_RPT_LOG.""M_PUBR_ID"" = M_PUBLICATION.""D_PUBLISHER_ID"" AND " & _
"R_PUBN_RPT_LOG.""DATESTAMP"" = '" & strIndivRunDate & "' AND " & _
"R_PUBR_RPT_LOG.""M_PUBR_ID"" = " & strIndivPubrID & " " & _
"GROUP BY " & _
"R_PUBN_RPT_LOG.""R_TIMEPERIOD_ID"", R_PUBN_RPT_LOG.""DATESTAMP"", " & _
"M_PUBLICATION.""PUBNAME"", M_PUBLICATION.""DESCRIPTOR"", R_PUBR_RPT_LOG.""M_PUBR_ID"" " & Chr$(13) & Chr$(10) & _
"ORDER BY " & _
[red] "5 ASC, 4 ASC"[/red]
Malcolm Wynden
malcolm@wynden.net