Updating a Variable via Record Data

Posted on 2006-06-14
Medium Priority
Last Modified: 2012-06-27
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.

Question by:medriza
  • 3
  • 3
LVL 77

Expert Comment

ID: 16904274
Hi medriza,

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


Author Comment

ID: 16904433
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.
LVL 42

Expert Comment

ID: 16905171
>>> 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.

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.


Author Comment

ID: 16906706

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?
LVL 42

Accepted Solution

frodoman earned 2000 total points
ID: 16907011
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.


Author Comment

ID: 16907680
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.
LVL 42

Expert Comment

ID: 16910527
Glad to be of help.  Good luck to you.


Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses
Course of the Month16 days, 5 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question