Summarizing or merging duplicate lines with cumulative "Quantity" value
Summarizing or merging duplicate lines with cumulative "Quantity" value
Hello,
I'm very new to Stimulsoft and I have a reporting need that I can't figure out. I often look up the solution to figure it out on my own, but either my wording is wrong, or I just haven't found the solution yet.
I am building a report that shows transactions by employee and i'm looking to summarize duplicates. I've set the GroupHeader to sort by employee so it groups them accordingly. Below is similar to how my Databand is set up to give some visual perspective.
ID -- Employee Name -- Item ID -- Customer Item ID -- Item Description -- (Issue) Type -- Quantity
So what I am looking to do is instead of it showing multiple instances of the same employee issuing the same item, is to merge the lines and have the "Quantity" increase to match the total value. So if "Tim" issued item "12345" 5 times, it would only show this instance once with the Quantity value of 5.
Please also note, some items may issue more than 1 quantity per instance, so I need to summarize those values.
I'm very new to Stimulsoft and I have a reporting need that I can't figure out. I often look up the solution to figure it out on my own, but either my wording is wrong, or I just haven't found the solution yet.
I am building a report that shows transactions by employee and i'm looking to summarize duplicates. I've set the GroupHeader to sort by employee so it groups them accordingly. Below is similar to how my Databand is set up to give some visual perspective.
ID -- Employee Name -- Item ID -- Customer Item ID -- Item Description -- (Issue) Type -- Quantity
So what I am looking to do is instead of it showing multiple instances of the same employee issuing the same item, is to merge the lines and have the "Quantity" increase to match the total value. So if "Tim" issued item "12345" 5 times, it would only show this instance once with the Quantity value of 5.
Please also note, some items may issue more than 1 quantity per instance, so I need to summarize those values.
-
- Posts: 6271
- Joined: Tue Mar 20, 2018 5:34 am
Re: Summarizing or merging duplicate lines with cumulative "Quantity" value
Hello,
Please send us a sample report with test data which reproduces the issue for analysis, we will try to find a solution for you.
Thank you.
Please send us a sample report with test data which reproduces the issue for analysis, we will try to find a solution for you.
Thank you.
Re: Summarizing or merging duplicate lines with cumulative "Quantity" value
Thank you
Attached is the report i'm working with and a test copy of the data as an example.
Attached is the report i'm working with and a test copy of the data as an example.
- Attachments
-
- Test Data.xls
- (52 KiB) Downloaded 140 times
-
- Moosehead Employee Monthly Usage Report.mrt
- (40.99 KiB) Downloaded 137 times
-
- Posts: 6271
- Joined: Tue Mar 20, 2018 5:34 am
Re: Summarizing or merging duplicate lines with cumulative "Quantity" value
Hello,
We couldn't run your report, there is a different data structure. Please modify your report and send us for analysis.
Thank you.
We couldn't run your report, there is a different data structure. Please modify your report and send us for analysis.
Thank you.
- Attachments
-
- Capture.PNG (69.64 KiB) Viewed 2219 times
Re: Summarizing or merging duplicate lines with cumulative "Quantity" value
My apologies, please see the attached revised documents. I've linked it to a CSV this time and named the data source "DataSource2"
- Attachments
-
- Test Data.csv
- (10.39 KiB) Downloaded 135 times
-
- Moosehead Employee Monthly Usage Report.mrt
- (41.9 KiB) Downloaded 195 times
-
- Posts: 6271
- Joined: Tue Mar 20, 2018 5:34 am
Re: Summarizing or merging duplicate lines with cumulative "Quantity" value
Hello,
You can add an additional grouping. Please check the modified report.
Thank you.
You can add an additional grouping. Please check the modified report.
Thank you.
- Attachments
-
- Moosehead Employee Monthly Usage Report_modified.mrt
- (18.74 KiB) Downloaded 286 times
Re: Summarizing or merging duplicate lines with cumulative "Quantity" value
This is perfect! Thank you very much for your support
-
- Posts: 6271
- Joined: Tue Mar 20, 2018 5:34 am
Re: Summarizing or merging duplicate lines with cumulative "Quantity" value
Hello
We are always glad to help you!
Please let us know if you need any additional help.
Thank you.
We are always glad to help you!
Please let us know if you need any additional help.
Thank you.
Re: Summarizing or merging duplicate lines with cumulative "Quantity" value
Edit:
I figured out the answer to my own question.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
There is one additional thing I would like to inquire. If you would like this to be moved to a separate post please let me know. I need to have two types of this report: a weekly and a monthly. On the weekly, I need it to show the date range and the monthly, I just need it to show the last months name.
For the weekly, I tried creating a variable with the expression {Today.AddDays(-7)} and {Today.AddDays(-1)}. My goal was that it would essentially show the start-end date of the report but it gave me an error when trying to load the report. Is there a different way I should be approaching this, or is there a "RemoveDays" expression that would work?
For the monthly, I wasn't sure how to approach this. Essentially, the report would be generated February 1st for the previous month (January), so I would need it to just say January 2019.
I figured out the answer to my own question.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
There is one additional thing I would like to inquire. If you would like this to be moved to a separate post please let me know. I need to have two types of this report: a weekly and a monthly. On the weekly, I need it to show the date range and the monthly, I just need it to show the last months name.
For the weekly, I tried creating a variable with the expression {Today.AddDays(-7)} and {Today.AddDays(-1)}. My goal was that it would essentially show the start-end date of the report but it gave me an error when trying to load the report. Is there a different way I should be approaching this, or is there a "RemoveDays" expression that would work?
For the monthly, I wasn't sure how to approach this. Essentially, the report would be generated February 1st for the previous month (January), so I would need it to just say January 2019.
-
- Posts: 6271
- Joined: Tue Mar 20, 2018 5:34 am
Re: Summarizing or merging duplicate lines with cumulative "Quantity" value
Hello,
Please send us your report with test data which reproduces the issue for analysis.
Thank you.
Please send us your report with test data which reproduces the issue for analysis.
Thank you.