-
How do I use “Grouping” in “report designer” programmatically ?
I get the error “Report sections do not match data source” when I try.
In my code I have :
Set repReport.DataSource = rsRecordSet
repReport.Show
I have it this way because I add fields to the recordset after I have fetched it from the database. The added fields are the division of two other columns. I do it this way to handle the problem division with zero or null.
I think I could handle the grouping problem in the DataEnviroment, but I don’t know how to handle the division problem there.
/AKA
-
try looking at the SHAPE command for your select statement for the recordset. Let me know if you need more help.
-
Thanks Nathan,
I now think that I understand how to do it for a modern database but I use Oracle RDB and I do not think that they know of SHAPE. I get this error :
[Oracle][ODBC][Rdb]%SQL-F-SYNTAX_ERR, Syntax error
Having this SQL :
SHAPE ( SELECT Company, Campaign_Grp_Id, Campaign_Group, IArtName, Stock_Quant, CAST(Normal_Price AS FLOAT)/100 AS Normal_Price, HMCNR, CAST(Campaign_Price AS FLOAT)/100 AS Campaign_Price, Start_Week, CAST((Normal_Price-Campaign_Price) AS FLOAT)/100 AS DIFF, Sold_Quant_Before, Sold_Quant_During, Sold_Quant_After,Avg_Sold_Quant_During, Sold_Amount_Before/100 AS Sold_Amount_Before, Sold_Amount_During/100 AS Sold_Amount_During, Sold_Amount_After/100 AS Sold_Amount_After, Avg_Sold_Amount_During/100 AS Avg_Sold_Amount_During FROM V_CCR_02_03 WHERE Company = '01' AND Start_Week = 199918 AND End_Week = 199918 AND Start_Date = 19990503) COMPUTE( SUM( Sold_Amount_Before) AS Detail BY Company, Campaign_Grp_Id, Campaign_Group
/AKA
-
I understand a little more now, I have changed the connection sting to
Provider=MSDataShape.1;Extended Properties="Shape Provider=MSDASQL;DRIVER={Oracle ODBC Driver for Rdb};UID=UUU;PWD=YYY;DATABASE=ATTACH 'FILENAME CO$DB:DB_COS';CLS=COS;XPT=2;DBA=W;CSO=2;SVR=ZZZ";Persist Security Info=True;Connect Timeout=30;Data Provider=MSDASQL
But I still get an error, but this time it is
The data shape command contains a syntax error at or near position 14 in the command. The command text near the error is: "SHAPE ( SELECT Company, Campaign_Grp_Id,".
Any ideas that to do ?
-
I noticed a couple of things... take a look at a couple that I'm using...
SHAPE {SELECT * FROM VBApps.dbo.CUSTCOST WHERE (RefPart LIKE 'B01%') ORDER BY Customer} As ReportChild COMPUTE ReportChild By Customer
here is a more difficult one for using a second table as a details table.
"SHAPE {SELECT RefNo, RefPart, Date, StyleID, " & _
"Author, Labor, LaborHrs, Description, " & _
"Customer, SubTotal, Discount, DscAmount, " & _
"sOrdered = CASE Ordered WHEN '0' THEN 'No' ELSE 'Yes' END, " & _
"sOrderedQty = CASE Ordered WHEN '0' THEN '' ELSE CAST(OrderedQty AS CHAR) END, " & _
"Comment, GrandTotal, LaborTTL FROM " & C1_DBName & ".dbo.CUSTCOST " & _
"WHERE RefNo = '" & txtRefNo.Text & "' AND StyleID = '" & _
txtStyle.Text & "'} AS PrintMain " & _
"APPEND ({SELECT Price, Qty, CustDesc, Total, " & _
"RefNo, StyleID FROM " & C1_DBName & ".dbo.CCITEMS WHERE RefNo = '" & _
txtRefNo.Text & "' AND StyleID = '" & _
txtStyle.Text & "'} AS PrintChild " & _
"RELATE 'RefNo' TO PARAMETER 0,'StyleID' TO PARAMETER 1) " & _
"AS PrintChild"
sorry about the formatting but I just copied it straight out of my code.
-
I am comming nearer but can you also post one there you use a function like count or sum ?
Regards, Anders
-
not completely sure what you are asking... could you give an example?
-
I mean using "Aggregate Functions" like count found under "Shape Compute Command" in the VB help.
-
'setup sql statement
strSQL = "SHAPE {SELECT RefNo, RefPart, CONVERT(Char,Date,101) AS Date, " & _
"Author, Description, StyleID, Customer, GrandTotal, " & _
"sOrderedQty = CASE Ordered WHEN '0' THEN '' ELSE " & _
"CAST(OrderedQty AS CHAR) END, " & _
"sOrdered = CASE Ordered WHEN '0' THEN '' ELSE 'Yes' END " & _
"FROM " & C1_DBName & ".dbo.CUSTCOST " & strWhere & strOrder & "} " & _
"AS ReportChild COMPUTE ReportChild BY " & strBy
you mean like this? In this one I'm setting up a lot of stuff with strings before adding to this string so some is missing. strBy would be a field to group by and strWhere is a where clause and strOrder is an orderby clause...
-
No :(
I want a report that looks like this
Code:
'Group Header
Article Group Article
'Details
AA A
AA B
'Group Footer
Group AA has 2 Articles
and I think that using COUNT( Article ) would give me the number 2. I think that the select should look like somthing like this:
SHAPE { SELECT ArticleGroup, Article from Articles } AS ReportDetail COMPUTE COUNT( Article ) BY ArticleGroup
But I never get it right :( But I have trust in that you can help me :)
-
SHAPE { SELECT ArticleGroup, Article COUNT( Article ) As ArtCount from Articles } AS ReportDetail COMPUTE ReportChild BY ArtCount
you might also want to put in an order by on the articlegroup field.
try it and let me know if it works...
-
WAIT...
Should be:
SHAPE {SELECT ArticleGroup, Article, COUNT(Article) AS ArtCount FROM Articles ORDER BY ArticleGroup} AS ReportDetail COMPUTE ReportDetail BY ArtCount
the recordset would be the parent(headers) and reportdetail would be the report detail section. I also threw in the order by.
