Forgot password? | Forgot username? | Register

Display of Single Row of Table Data Based on Field Value

Display of Single Row of Table Data Based on Field Value

Hi All,

I'm new to Crystal Reports and since we don't currently have funding for training, I'm trying to learn myself. I know the basics, but I have run into trouble with more complicated reports. Here is my current issue:

I have designed a report for the Requests module. The Report is designed to be an invoice given to scholars who request images from our institution. I'm trying to pull data from the costs group (an embedded) table from our Img Request 2 tab (see attached). I'd like to have the data from the Amount field from a single row of this embedded table appear on my report based on the data in the Explanation of Cost field. So, from the screenshot, I'd like to have "60.00" appear on my report if Explanation of Cost=Image Fee.

I tried to make a subreport for image fee and group the records based on the following formula:
If {ImgExpla_csv.ImgExplanationOfCost} = "Image Fee" then {ImgAmoun_csv.ImgAmount} else ""

With that method, however, all the rows of data show up on the report (see attached).

I'd like to get them to show up one row at a time, as on the attached template.

Any help is greatly appreciated!

Thanks,

Foy Scalf
Head, Research Archives
Oriental Institute
https://emu.kesoftware.com/images/agorapro/attachments/1773/Template.JPG

https://emu.kesoftware.com/images/agorapro/attachments/1773/Report.JPG

https://emu.kesoftware.com/images/agorapro/attachments/1773/Image-Request-2.JPG

Edited by: Foy Scalf - 22-Apr-15 04:12:29

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Display of Single Row of Table Data Based on Field Value

Hi Foy,

What you want to do is in the Subreport for the Costs you want to use the Select Expert (Report menu - Select Expert menuitem) and add (click <New>) a Selection for the ImgExpla_csv.ImgExplanationOfCost is equal to "Image Fee".  This will exclude rows that aren't Image Fees.  Hope that helps.

Beth Gamble
NMNH
Smithsonian Institution

--- Beth L. Gamble ----------------------------------
Senior Systems Analyst
National Museum of Natural History
Smithsonian Institution

Beth Gamble
Senior Systems Analyst
useravatar
Offline
29 Posts
Female 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Display of Single Row of Table Data Based on Field Value

Hi Beth,

Thank you for your reply. I must be doing something else wrong then, as I had used the Select Expert as you mentioned to only show rows with "Image Field." However, I still get all the values from all rows on the report. I wonder if I have something else set up improperly.

I tried both Group and Record selection formulas, to no avail. Screenshots attached.

Still tinkering.

Thanks,

Foy

https://emu.kesoftware.com/images/agorapro/attachments/1773/Costs-on-Report.JPG

https://emu.kesoftware.com/images/agorapro/attachments/1773/Record-Select.JPG

https://emu.kesoftware.com/images/agorapro/attachments/1773/Group-Select.JPG

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Display of Single Row of Table Data Based on Field Value

As a follow-up to this message, I have been told that:

"This is due to how table fields are exported to csv by EMu. A table is exported into a single field in a csv hence all the values are being shown, you would have go through the table fields line by line and print the lines where your values are revelant."

Thus, it seems a more complex procedure will be needed to get the data to appear as desired.

Foy

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Display of Single Row of Table Data Based on Field Value

OK, there are a couple of things that may be the problem.  When including fields in your report that appear in a grid in EMu you should ALWAYS group them together so they get put into their own .csv file and the rows and column data will stay together.  For these multi valued fields are you converting them to text via the table to text option in EMu report settings?  If so, don't do that.  That will put all your values into one atomic field value and you would have to manually parse out what you want in crystal via a formula.  Instead group the different field from the grid together using the group feature in EMu report settings.  Don't include the groupname.csv file in your main report.  It should be in the subreport only.  Don't include the modulename.csv file (master csv) in the supreport.  The subreport linkage will be on the shared key field.  In your subreport use the Select expert to add the filter for "Image Field."  If this doesn't work, attach your .rpt file here so I can have a closer look.

Beth :-)

--- Beth L. Gamble ----------------------------------
Senior Systems Analyst
National Museum of Natural History
Smithsonian Institution

Beth Gamble
Senior Systems Analyst
useravatar
Offline
29 Posts
Female 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Display of Single Row of Table Data Based on Field Value

Hi Beth,

Thanks. With the help of Jonathan Kwan, we did manage to get this to work, by a slightly different method. First, you're right, my main problem was that I didn't have all the fields grouped properly. Once they were all grouped together in the EMu reports properties, we created a subreport based on one of those fields and then suppressed the details section of the report via the section expert when the value was not "Image Fee." Now, it displays as intended. By this same method I was able also to display only single rows of related quantity and sub-total fields.

Thanks for your help. I think once the fields were grouped properly, we could have arrived at the same result using your method (show only if vs. suppress when not).

Now, I'm trying to figure out how to sum the single resulting values from those individual subreports to get a sub-total combining the two values pulled from the sub-reports. It never ends;-)

Thanks again,

Foy

Edited by: Foy Scalf - 06-May-15 03:56:42

Administrator has disabled public posting. Please login or register in order to proceed.

Re: Display of Single Row of Table Data Based on Field Value

If you do it via the select expert then you can use the summation formulas in Crystal because the other rows are not present and therefore don't factor into the calculations.

Good Luck!

--- Beth L. Gamble ----------------------------------
Senior Systems Analyst
National Museum of Natural History
Smithsonian Institution

Beth Gamble
Senior Systems Analyst
useravatar
Offline
29 Posts
Female 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Display of Single Row of Table Data Based on Field Value

Hi Beth,

Thanks a million! Worked like a charm. Reconfigured the subreports to use the selection expert, then added a final subreport using a sum formula on the subtotal field, summing all rows were "ImgExplanationOfCost" is one of "Image Fee", "Permission Fee." Seems to be working great.

Thanks again for your help with this - much appreciated!

Best,

Foy

Administrator has disabled public posting. Please login or register in order to proceed.
There are 0 guests and 0 other users also viewing this topic

Board Info

Board Stats
 
Total Topics:
603
Total Polls:
0
Total Posts:
1363
User Info
 
Total Users:
871
Newest User:
Monica Syrette
Members Online:
3
Guests Online:
114