[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now


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
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
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.

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.


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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 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