on 08-15-2013 3:36 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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!
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
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.
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
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.
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
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
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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?
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
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.