DataReport and Grouping

[AKA]

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

[Nathan]

try looking at the SHAPE command for your select statement for the recordset. Let me know if you need more help.

[AKA]

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

[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 ?

[Nathan]

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.

[AKA]

I am comming nearer but can you also post one there you use a function like count or sum ?

Regards, Anders

[Nathan]

not completely sure what you are asking... could you give an example?

[AKA]

I mean using "Aggregate Functions" like count found under "Shape Compute Command" in the VB help.

[Nathan]

'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...

[AKA]

No :(

I want a report that looks like this


'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 :)

[Nathan]

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...

[Nathan]

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.