How to used Running Totals based on dynamic category list

Crystal Reports 2016
SQL 2017

I am using individual running totals that are based on a value called "Categories" that are used to count all matching records for the category.  The issue I have is a customer can have any number of categories and that means each of them would need a customized report just for them.  What I want to do is have the running totals dynamically change based on the number of "Categories"


Example Categories found in

Customer A:  Database                     Customer B: Database

{ListFruit} = 'Apples'                           {ListFruit} = 'Apples'
{ListFruit} = 'Oranges'                        {ListFruit} = 'Oranges'
{ListFruit} = 'Cherries'                        {ListFruit} = 'Grapes'
{ListFruit} = 'Grapes'

So the display would look like this.

Apples    100                                        Apples       20
Oranges   40                                        Oranges      2
Cherries    20                                       Grapes    100
Grapes        6

As you can see above Customer B does not display any running total for the category Cherries because that category does not exist in their database.
MikeM670Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raghavendra HullurSoftware DeveloperCommented:
Hi,

The running totals as such will be dynamic and should be based on the field used.
Can you explain on the issue you are facing with this? A screenshot of the issue or attaching the report file with some sample data saved would do.

Also, are you creating report for each customer or is it a consolidated one?
0
MikeM670Author Commented:
The report would need to be run for each customer and dynamically adjust the running totals based on the category value falls into.  I should note that I'm using stand-in information as the actual data is sensitive.    Let me see if this would help you.  Query would be run against year 2017.

Customer A

Database ProductSales
Table  Sales
Fields: Year  int
             Amount int
             Code int

Table ListFruit
Fields:   Code
              Category      

Table Sales Values

Year          Amount          Code
2017               1                    1
2017              10                   2
2017               100                 1

Table ListFruit Values

Code         Category
1                  Apples
2                 Oranges  

ect


so the running total for Customer A would return the following values

Apples         101
Oranges        10


Customer B

Database ProductSales
Table  Sales
Fields: Year  int
             Amount int
             Code int

Table ListFruit
Fields:   Code
              Category      

Table Sales Values

Year          Amount          Code
2017               1                    1
2017              10                   2
2017               100                 1
2017                12                   3

Table ListFruit Values

Code         Category
1                  Apples
2                 Oranges
3                  Cherries

ect

so the running total for Customer B would return the following values

Apples         101
Oranges        10
Cherries         12
0
Raghavendra HullurSoftware DeveloperCommented:
Yes, irrespective of difference in categories, the running total should work fine dynamically.

I just created the data as per your scenario and I see the details returned. Check the attached report sample for you.

I have 2 groups on Customer and Category and I placed a running total with changes: Field to Summarize = Amount, Evaluate = For Each Record and Reset = On Change of Field --> Category.

Now, when I place this running total into group footer 2 (Category), I can see the summary as required.
RunningTotal.rpt
0
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

mlmccCommented:
HOw about using a cross tab at the bottom of the report.
It will dynamically change based the categories available.

Another way to do this would be to use formulas and arrays to gather the category list and the totals then display it at the bottom of the report.

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeM670Author Commented:
mlmcc,

That is exactly what I did do after posting the question  Much cleaner and simpler to accomplish what I needed.    Tho  I still am interested in the other method for future reference.

Raghavendra Hullur,

Thank you for the example.  With just a few changes I could use that to display the data the way I wanted.
0
MikeM670Author Commented:
The cross-tab was the best solution but I can see where the other method would be useful.

Thanks to both of you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.