Solved

Updating a Variable via Record Data

Posted on 2006-06-14
7
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 16904274
Hi medriza,

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

Pete
0
 

Author Comment

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

Expert Comment

by:frodoman
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);
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
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 

Author Comment

by:medriza
ID: 16906706
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
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.

frodoman
0
 

Author Comment

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

Expert Comment

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

frodoman
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

726 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