Solved

Updating a Variable via Record Data

Posted on 2006-06-14
7
253 Views
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.

-Pedro
0
Comment
Question by:medriza
  • 3
  • 3
7 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
Hi medriza,

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

Pete
0
 

Author Comment

by:medriza
Comment Utility
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.
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
>>> 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);
CustomerName;

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.

frodoman
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:medriza
Comment Utility
Frodo,

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

Accepted Solution

by:
frodoman earned 500 total points
Comment Utility
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.

frodoman
0
 

Author Comment

by:medriza
Comment Utility
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.
0
 
LVL 42

Expert Comment

by:frodoman
Comment Utility
Glad to be of help.  Good luck to you.

frodoman
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now