Grouping From String

[tims]

I have a report that I need to create to take a variety of string values and turn them into a data source, then group like items within that datasource.

I've attached the sample data and the report as far as I've gotten it.
The original data comes from a string value with a ; delimiter that comes from a variety of lines in a datasource. I take all the string values and concatenate them together, then use a Substring formula to pull them out into individual lines. In the attached example, there are about 50 lines in the original datasource, each with a Quantity that defines how many subfields there will be in the string value on each line (Hardware.Comments), to a total of 202 individual subfields all together.
I have a couple of issues.
The first is how to set the count value for the second set of detail records so that it is equal to the total of the quantity from the first databand (Sum(Hardware.QTY))
The second is how to take all these subfields from the string broken out into individual records with the final data band and group them by like values and sum the Hardware.QTY for each like value.
The end result of this report should be a short list showing
223 - QTY ###
224 - QTY ##
etc.

https://dl.dropbox.com/u/99199395/310185-02.zip

Thanks for any help you can provide.

[Alex K.]

Hello,

Can you please show a sample image or screenshot how it should look like with sent data.

Thank you.

[tims]

Please find the image of the desired result here.

https://dl.dropbox.com/u/99199395/Key_Report_Result.png

Thanks.

[Alex K.]

Hello,

Most probably, but the once solution will be spliting this column for each value (without delimeters) and use grouping in query.

Thank you.

[tims]

Can you explain what you mean? I don't understand.

[Alex K.]

Hello,

For example:
now :
ID | Key | Value ...
1 | 212;213; 214 | 1
2 | 212;217 | 1
3 | 213 | 1
...

the once solution will be spliting this column for each value (without delimeters)

ID | Key | Value ...
1 | 212 | 1
1 | 213 | 1
1 | 214 | 1
2 | 212 | 1
2 | 217 | 1
3 | 213 | 1
...

And use the query with grouping:
select ID, Key, Sum(Value) as QTY from Table
group by ID, Key

Thank you.

[tims]

So unless I'm understanding, I think i have the splitting part working, on pages 2-8 of the current report, I just don't know how to get the grouping working.

[Alex K.]

Hello,

Please check the modified report in attachment.

Thank you.

[tims]

Thanks. That looks great, but there appears to be an issue. I created the summary in the image in excel from the report export, so I beleive the QTY's are correct in the image. When I add up your numbers, I only get a total of 184. The total should be 202.
Also, would it be possible to sort ascending by key number?

Thanks,
Tim S.

[Alex K.]

Hello,

Sorry, error in indexes. Please check the report in attachment.

Thank you.