Updating a Variable via Record Data

Okay, I'm an intermediate Crystal Reports user, so I may need some hand holding on this. Here is what I'm trying to do.

I have an activity log that is being stored in a SQL database. I want to use CR to pull a report out of that data and group it. I have data that (simplified) looks something like:

01>Import file CUST1.MAP
02>Beginning data read
03>Verifying data
04>Creating transaction #001
05>Creating transaction #002
06>Document verification complete
07>Import file CUST2.MAP
08>Beginning data read
09>Verifying data
10>Creating transaction #001
11>Document verification complete
12>Import file CUST3.MAP
and so on...

The above is the message status record. There are other records I pull as well (process no., process type, time/date, etc.). I would also like a record on the report that lists the customer that the above fields apply to. By reading the right chars of the field, I've been able to get it to recognize the customer when it hits those lines that open the import file (CUST.MAP), but what I want to do is carry that down to all the rows beneath it until it hits the next customer. For instance, lines 01-06 should have a field that reads CUST1, lines 07-11 should have one that reads CUST2, etc.

I've tried using a global variable, and updating it when that change occurs, but that is not working for me. Is this even possible?

And let me know if I haven't explained this properly, and I'll go a bit more in depth.

Thanks for any help you can provide.

Who is Participating?
frodomanConnect With a Mentor Commented:
No, you can't group on it that way because this is a runtime formula and the grouping is already done before this formula is evaluated.

The idea solution here would be to write a stored procedure that does basically what the Crystal formula does, but since it's done on the database you can return a field with the customer name in it and then Crystal can group on that field.

If you cannot create a stored procedure then you may not be able to do it the way you want.  One workaround that might be okay would be to group on your data field which would give you one record per group.  Then you can use a formula to suppress all of the group headers that are not for a .MAP record and all of the group footers where the next record isn't a .MAP record.  This will give you a display that works like you want - a group header and footer for each customer with all of the details in the middle.  As long as it's only display you're concerned with this should work - if you need to do subtotals by groups then this isn't going to help you because even though the extra headers / footers are suppressed, they still are breaks in groups for calculation purposes.

Hi medriza,

Is this literally Cust1.MAP, Cust2.Map etc ?  How big does the number get?

medrizaAuthor Commented:
No, those are examples. They are actually listed as the "customer's name.MAP". So you would have COSTCO.MAP, SAFEWAY.MAP, etc. There will be anywhere from 0 to 12 of these customer maps being created in each log.

So that field I'm trying to create would read "COSTCO", "SAFEWAY", etc. The trick is that I need it to be populated with those names on the subsequent lines after it finds one of those .MAP lines.
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

>>> I've tried using a global variable, and updating it when that change occurs, but that is not working for me. Is this even possible?

Assuming you're reading each of the lines in this log as a separate record then you should be able to do this.  Create a formula like this:

// @ShowCustomerName
shared stringVar CustomerName;
if right({yourfield},4) = ".map" then
   CustomerName := right({yourfield},9);

This formula placed on each record will change the shared variable if appropriate, and then will show the contents of the variable so you'll always see the most recent one.  The line of code: CustomerName := right({yourfield},9);   should be changed to whatever method you're using to get the customer's name.

medrizaAuthor Commented:

Thanks for that formula. It fixed what I was doing wrong. Unfortunately, didn't get me to where I wanted to be.

I've now got the column that has the data that I was trying to build. As I mentioned, I'm trying to group on this customer classification. Now that I can see the data, it looks like I can't group on it because it is being determined at the record level, and not in place beforehand.

Using the example code from above, I don't have the option to create a group for @ShowCustomerName.

So maybe I'm going about this wrong. Is it possible to build a field using data in each record that can be used as a group heading? To further clarify, what I want to do with this log is to take entries for each customer and group those into separate sections of the report. Transactions are being automatically created in our system, and I need to get that data to the appropriate person that handles each customer.

Basically, what I'm after is this. When CR sees a line that has CUSTOMER.MAP in it, I want it to take all subsequent records and group them along with that beginning .MAP record, until it finds another .MAP record. Then group that one under a new grouping. The catch is the grouping. There are literally pages of transactions. So the guy that has to review the SAFEWAY data would have to page through the report until he finally got the page with SAFEWAY on it. What I wanted was to roll each of these up into its own section and then they could drill down to the data they needed, with only a quick one line entry on that main page.

What do you think? Is that do-able? And am I going about it the correct way?
medrizaAuthor Commented:
Writing a stored procedure sounds like the way to go to get this to work the way I want. Of course, I've never written one before, so it's time to hit the books.

But your second suggestion of suppressing the rows sounds like it'll be a good placeholder. You're absolutely correct that this is simply to display data, and no calculations are going to be occurring at all.

By the way, what you taught me about the shared variable is worth gold. Now that I got that figured out, I can see a hundred one uses for it already. A thousand thanks for that alone.
Glad to be of help.  Good luck to you.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.