cancel
Showing results for 
Search instead for 
Did you mean: 

Crystal Reports 2008 - Displaying and Grouping results based on different logical criteria

Former Member
0 Kudos

I don't know how else to word that title. Logically what I'm trying to do is simple. I just have no idea how to accomplish it in CR.


There is one table of information. I want to setup 3 different pieces of logic and 1) display the results grouped by each piece of logic and 2) give a count of the number of records in each group.

Here is the logic:

Results set 1: Display all results if Field A contains a certain text string. Give a count of those results.

Results set 2: Display all results if Field B contains a certain text string. Give a count of those results.


Results set 3: Display all results if Field B contains a different text string than Set 2. Give a count of those results.

It seems so simple. I just don't know how to do this in CR or really what to Google to specifically find info on this.

Any thoughts would be appreciated. I was thinking to use Formula fields for the logic, and I'm just stuck on how to end up using them properly, if that's even the route to take.

Ultimately I would also like to display the counts for each of these result sets in a table as well.

Thanks,


Brian

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

You don't really need to group the report to find the count. Try this:

1) Create a formula (@Count_Field A) with this code:

IF Instr({Field A}, "the_certain_string") > 0 then 1

2) Another formula (@Count_Field B_Set1) with this code:

IF Instr({Field B}, "the_certain_string") > 0 then 1

2) Another formula (@Count_Field B_Set2) with this code:

IF Instr({Field B}, "the_certain_string") > 0 then 1

Then, go to Insert > Summary > Choose the 1st formula and place it on the Report Footer. This is the Count of Field A.

Do the same for the other formulas too.

-Abhilash



ido_millet
Active Contributor
0 Kudos

In the solution above, for the Summary Type use a SUM rather than a COUNT.

Former Member
0 Kudos

Thank you, Gentlemen. This worked well to get me the counts of each type. Is it possible for me to get the details of the records for each type as well? I would like to show the records that fell under each Result Set.

And let's take this one step further: If each record in this table has a UserID field, how would I break it down by UserID? So show how many records in Result Set 1 were tied to UserID "A", how many in Result Set 1 were tied to UserID "B", how many records in Result Set 2 were tied to UserID "A", etc.

abhilash_kumar
Active Contributor
0 Kudos

Hi Brian,

To get the Details of each type you need Groups on the report. Could you post a snapshot of some sample data and the expected output?

This might be a little tricky as it involves two separate fields.

-Abhilash

Former Member
0 Kudos

No problem. Below is some sample data and my desired output:

Result Set 1 formula looking for "apples" in Record Text field

Result Set 2 formula looking for "oranges" in Record Text field

Result Set 3 formula looking for "bananas" in Record Text field

Record Text        UserID        Other field1           Other field2

apples                 user1           other info              some other info

bananas              user1           some other info    some other info

apples                 user2           other info              other info

oranges               user1           some other info    other info

bananas              user2           other info              other info

apples                 user1           some other info     some other info

So right now, I have a summary count that shows the number of times "apples", "oranges", and "bananas" show up individually, which is part of what I want. I would like that at the very end of the report.

Apples - 3

Oranges - 1

Bananas - 2

The step further is getting the details of these records to show:

Result Set 1:

apples                  user1              other info             some other info

apples                  user2              other info             other info

apples                  user1              some other info   some other info

Result Set 2:

oranges                user1             some other info     other info

Result Set 3:

bananas               user1            some other info      some other info

bananas               user2             other info                other info

And ALSO a count in each set by User:

Result Set 1 - User1 - 2

Result Set 1 - User2 - 1

Result Set 2 - User1 - 1

Result Set 3 - User1 - 1

Result Set 3 - User2 - 1

Easy enough to explain...having a heck of a time translating it to CR. Thanks again!

abhilash_kumar
Active Contributor
0 Kudos

Great that helps! Here's what you need to do:

1) Create a formula with this code:

If {Record Text} = "Apples" then "Result set 1"

else if {Record Text} = "Oranges" then "Result set 2"

else if {Record Text} = "Bananas" then "Result set 3"

2) Go to the Group Expert and add this formula to the Group List

3) Go to the Field Explorer > Right-click Running Total Fields > Choose 'UserID' as the Field to summarize > Operation would be 'Count' > Under Evaluate, select 'use a formula' > Click the formula button beside it and use this code:

{User ID} = "User1"

Under Reset, choose 'On change of Group' and choose Group # 1

4) Place the Running Total field on the Group Footer # 1 and create another Running Total in the same way for User 2. The Evaluate formula would be:

{User ID} = "User2"

You can place Text Objects beside each of these Running Total to read which Running Total belongs to which user.

Let me know how this goes.

-Abhilash

Former Member
0 Kudos

OK! This is getting so much closer. 1 thing not working quite right.

It still doesn't give me summary counts by User ID. But I'm guessing this is because I had to modify that Running Total a bit. I obviously simplified my sample data. I actually have a Parameter Field called UserID where several UserIDs can be chosen for who they want to run this report on. I modified it to {UserID} = {?UserID}. I'm guessing this has something to do with why it is not tallying for each ID selected in the criteria? As it is right now, it's still just summarizing the total number of records per Result Set.

abhilash_kumar
Active Contributor
0 Kudos

Even if you have a parameter you would still want to create a Running Total for 'each user'.

Instead of {UserID} = {?UserID}, you need {User ID} = "User Name 1" in the Evaluate formula.

And you would need a separate Running Total for each User.

While displaying each user info, you can suppress Running Totals that are equal to zero by conditionally suppressing them. (Right-click the field > Format field > Common tab > formula button for 'Suppress')

-Abhilash

Former Member
0 Kudos

How would I know what the UserIDs are though? I mean I could hard code some for testing purposes. But there could be a ton of them, and this would be run in several environments of the same application where UserIDs are completely different. There is no way to use the input that the user is selecting for the chosen UserIDs with the Parameter Field? If that's the case, that could be a huge problem for me as all of the reports I'm trying to create need to be broken out by userID in a similar fashion to this one.

abhilash_kumar
Active Contributor
0 Kudos

Ok. Try this:

1) Go to the Group Expert > Add the User ID as part of the Group List so that it is the 2nd group

2) Suppress Group Header #2 and Group Footer # 2

3) Create this formula and place it on the Group Header 2:

WhilePrintingRecords;

stringvar user;

stringvar cnt;

user := user + {User_field} + ">";

cnt := cnt + totext(count({User_field},{User_field}),0,"") + ">"

4) Create this formula and place it on the Group Header 1. This resets the values for each new Group 1:

WhilePrintingRecords;

stringvar user := "";

stringvar cnt := "";

"";

5) Create this formula and place it on Group Footer 1:

WhilePrintingRecords;

stringvar user;

Replace(user,">",chr(10));

Right-click the field > Format field > Common tab > Check 'Can Grow'

This formula shows the user names one below the other

6) Create this formula and place it beside the formula on Group Footer 1:

WhilePrintingRecords;

stringvar cnt;

Replace(cnt,">",chr(10))

Check the 'Can Grow' option for this field too.

This formula shows their counts.

For better formatting and spacing, you can place these two formula field inside a Text Object and turn ON the Can Grow option.

Let me know how this goes.

P.S: I hope you have a Record Selection Formula that filters user ids based on what's selected by the user?

-Abhilash

Former Member
0 Kudos

You are officially my hero. This was way outside of what I would have been able to come up with myself, but it will be helpful in other similar ones I'm trying to create. This worked like a charm. I've been poking around online at ways to show the totals at the top of the page, rather than at the bottom of each group's details, but it seems that is not possible. Is that what I'm gathering from everyone else asking about this same kind of thing? Because of how it evaluates the data to come up with those totals or something? What about being able to display these values in a chart or graph somehow? I'd love to show the detail AND be able to easily see the total values for each user for each type without having to cycle through the pages. Maybe I'm just being greedy now. haha

Thanks again for your help with this. I'm going to get back to that other one I posted about displaying the results from querying 2 different tables in one report soon. That's the other big pain I am working on.


Brian

abhilash_kumar
Active Contributor
0 Kudos

You can show the totals at the top of the page, however that would need a Subreport and a few more formulas. This because of the way CR evaluates stuff like you said,

For the Chart too you need a Subreport and a few other formulas. Have a look at this thread and see if you can make out something off it.

-Abhilash

Former Member
0 Kudos

Thank you, Abhilash Kuamar. It is Worked

Answers (2)

Answers (2)

Former Member
0 Kudos

hello

almost simmiller kind of problem i'm facing. i have a recordset of multiple branches and that is grouped by customer already. a customer  can buy goods from multiple branches so it is summery of customers with their goods. and i need the summery of branches with detail like i have attached an image

in this image it is the record of one customer with 4 goods which are from 2 branches and in footer it is summery of both branches. i'm having issue here while m showing summery of both branches.so how can i show this summary and it is not sure always that there is only 2 branches. it can be 1 or many.

Former Member
0 Kudos

Abhilash,

I was able to get the Chart to display what I wanted. Thank you. I have 2 more things on this one before I could call it perfect. One functional thing and one curious thing.

Curious Thing: Could you explain to me what those lines of code you gave me in the 4 formulas are actually doing? I was trying to do a couple fairly similar reports to this one and was going to try to build off what you showed me in this one, but I realized I have no idea what the code in those 4 formulas are actually doing.

Functional Thing: Is there any way to exclude the results of one Result Set from the other? For instance, Result Set 2 records meet the criteria from Result Set 3 also, but there are many records in Result Set 3 that do not meet the criteria from Result Set 2. I would like to exclude the records that exist in Result Set 2 from Result Set 3 so they are not counted twice.

Let me know if that doesn't make sense.

Brian

abhilash_kumar
Active Contributor
0 Kudos

Hi Brian,

The 1st formula on the Group Header 2 keeps accumulating the User ID/Names and adds '>' after each name; '>' is used just to separate each name. In the same formula we also count the number of names that appear in each group and separate those values with the '>' characters.

As an instance, when one whole group finishes execution, the variables might hold data like this:

User = Name1>Name2>Name3>Name4

cnt = 50>60>70>90

The formula on the Group Footers take the '>' character out of each variable and replace that with a 'new line feed', so the data transforms to:

Name1     50

Name2     60

Name3     70

Name4     90

And we need the reset formula on the Group Header so that the variables start fresh for each new group. Hope this makes sense?

Do you have a data sample of the last query?

-Abhilash

Former Member
0 Kudos

Ah. I can probably make sense of the lines of code now knowing that's what they are doing. Thanks.

As for the other query:

I have a formula that determines which type of order and it looks like this:

If Instr(UpperCase ({PatientsLog.Comment}), "LITERAL ORDER ADDED") > 0 then "ADD"

else if Instr(UPPERCASE({PatientsLog.Comment}), "LITERAL ORDER MODIFIED") > 0 then "CHANGE"

else if Instr(UPPERCASE({PatientsLog.Msg}), "LITERAL ORDER CUT") > 0 then "DC"

else if Instr(UPPERCASE({PatientsLog.Msg}), "ENDDT") > 0 then "DC"

else "NONE"

In my Select Expert, I'm excluding all records where that returns "NONE" and my Primary Grouping is by that Formula field. There is a large group of records that fall under the "CHANGE" condition, and the ones that fall under the "DC" conditions will also fall under the "CHANGE" condition, so they are basically a subset of that, but I don't want them counted or shown in the "CHANGE" record set. Although typing this out, I may have just answered my own question. In these Else IF formulas, it only looks for the first condition that is True and gives it that value for that record, right? So it can only ever actually take one of these....so I need to put the DC conditions above the CHANGE condition and it should total out correctly. Is that correct?

abhilash_kumar
Active Contributor
0 Kudos

Hi Brian,

Could you post that last question as a new Discussion?

Also, please post some data sample along with an expected output to make things more easier.

-Abhilash

Former Member
0 Kudos

Hi abhilash,

                   I have a similar query like the above. I have some data regarding payment. Every groupID has many payments.  need to print each payment separately. or want this data in in different pages separately.so that i can print each page individually for different payments separately.

Can you help me out.

Regards,

Akram

abhilash_kumar
Active Contributor
0 Kudos

Hi Akram,

This is a very old thread. Could you please create a new 'Dicsussion' with more details (screenshots, sample data, expected output etc) and everything that might help us understand the issue.

-Abhilash