Grouping Fields On Report Line...

[bluenoser337]

I am using VB6 Data Environment and Report Designer. In the DB I have separate columns for length, width, and depth of parts being manufactured. When I do a report at the end of the day, I want to have things grouped by part type, then have the LxWxD on the same line. In other words...
Red Parts QTY
6 x 6 x 12 500
6 x 6 x 24 93

Blue Parts
1 x 1 x 33 18
2 x 2 x 12 100

How can I get totals from this type of "grouping"? I'm lost. Wonder if I shouldn't just have one column in the DB to hold the "size" string? THANKS!

 

[Suraklyn]

Seems like all you would need to do is group by your part type. In your group header, you can throw in a field that displays the part type; in your details section, you can throw in your fields for length, width, depth and quantity.

You shouldn't need a field in your db to contain the overall dimensions. --
Jonathan

 

[bluenoser337]

Thanks Suraklyn...Maybe my problem is that I am not "grouping" properly in the DE. I have only set up relations and a lot of child commands. I will work more on actual grouping. (new to this DE and report business)

 

[bluenoser337]

Still stuck...as the example in the first post shows...I need totals for each "size combination". Not sure how to group to get counts from this combination of 3 size fields...and to get that count on the line with the 2 x 2 x 14 in the details section. Some day I'll look back at this and laugh.

 

[Suraklyn]

Here's what I've done. I'm not sure how your database is designed, so I used a pretty simple and straight forward database. I used an Access database with one table that had 5 fields: part_type, width, length, depth, quantity.

DE setup
----
1.) DE connected to Access db.
2.) Added Command (Command1)
3.) Command1's source of data was set to the table; no SQL statement needed.
4.) Command1's grouping was done by part_type.

DR setup
----
1.) DR's DataSource source set to the DE.
2.) DR's DataMember set to Command1_Grouping.
3.) Retrieved the structure of Command1_Grouping to give the report the correct section layout.
4.) 4 fields placed in details section. Each field's DataMember set to Command1 and each was set to a different DataField.
5.) 1 field placed in the Group Header for Command1_Grouping. Its DataMember is set to Command1_Grouping, and its DataField was set to the part_type.

When I open up that report from the program, everything is grouped by part types (I used three part types in my sample db: red, green, blue). Under each part type, it lists all of the length x width x depth - quantity records that were in the database that correspond to the aforementioned part type. When you do a grouping in your command, everything is then sorted in Ascending Order. I'm sure there is a way to leave it unsorted or have it sorted in another order, but I haven't found it.


Hopefully that will help point you in the right direction. If not, maybe we can try something else. --
Jonathan

 

[Suraklyn]

Just from the top of my head, you may need to do two levels of grouping. First you'd need to group by part type, then you'd need to group by the dimensions. Then you could use aggregates to accumlate the quantity and throw a field into your details section linked to the aggregate.

I haven't actually tried that, but let me give it a while. --
Jonathan

 

[Suraklyn]

That seems to do the trick. Assuming I'm following what you're saying...

I'm ignoring the quantity field in my database and taking a count of how many records there are of each part type of the same dimensions.

The output would be like your example above. It has a list of all part types. Under each part type, it lists all of the parts with unique dimensions and outputs the quantity of each dimension.

I just added a child command to the command I used before. In that child command, I added an aggregate command to keep count of the records. I also grouped that child command by length, width and depth.

The report structure will wind up being quite different. I retrieved the structure again to get the right section layout. The part type title would go where it was before. You would then add fields under your Child Command's grouping header for the length, width, depth and aggregate count.

There may be a better way of doing it, but this is what I came up with off hand. I hope this helps. --
Jonathan

 

[bluenoser337]

Jonathan...you are going above and beyond the call of duty...lucky for me! I wasn't sure, in the child command, what relation(s) to set, so I set length to length, width to width AND depth to depth. I think it may be working but I want to test it to death. I will let you know how it goes. Thanks for the help so far!!!!

 

[Suraklyn]

No problem. Just glad to help. --
Jonathan

 

[bluenoser337]

Suraklyn...So far, so good! Working great. Now I've been trying to take this a few steps further with varying degrees of success.
INSPECTOR NUMBER (0-9)
PART COLOR, PART QUALITY (#1,#2,ETC)
LxWxD, QUANTITY
I now want to group by inspector number and divide the parts not only by color...but by quality. The problem I keep having is with the "color/quality" grouping. When I call the report it says there is a problem with the shape instruction syntax near the color...quantity grouping. The resulting report should follow this format...
INSPECTOR 0
RED #1
1X2X3 12
1X18X22 10
RED #2
10X10X12 8
BLUE #1
24X24X66 37

INSPECTOR 1
RED #1
4X6X8 11
ETC ETC

I'm wondering if I am doing right by using Data Environment. I can get proper groupings, sorting and whatever I want with SQL in SQLServer...but this Data Environment thing is really causing me some grief.

 

[bluenoser337]

The error message is "Data shape command text contains a syntax error at or near position 520 in the command. The text near the error is: "quality_grouping COMPUTE Col by Color','quality')" HHHHEEEEELLLLPPPP

 

[elizachacko]

oh my!!! there is someone else too struggling like me!!!
isnt there anyone to help us out???
somebody pls look into this, pls!!!pls!!!!