Where does the calculation take place in a calculated field in a RDD?


(Richard Hodges) #1

Hi Guys

I’ve created a copy of the OrderAck RDD for a custom SSRS report and I’ve ran into an issue adding in a custom calculated field.

I can add single fields that are in the table (e.g. OrderComment in OrderDtl) however I would like to add a calculated field that takes two decimal columns and performs a calculation on them (so the calculated field would be the results from column1 - column2).

I’ve had a look at the OrderHed DataSource for the RDD and it has a calculated field called BillToAddressList but when I look in the OrderHed table I can’t see this column so I assume it’s a concatenation of some of the address columns in OrderHed.

However when I look through the RDD I can’t see where this concatenation takes place I’ve looked in the DataSources > Report Table tabs (I thought it’d be in Calculated Fields but that’s just a list of them).

I tried looking at the data set in the RDL but that’s selecting the BillToAddressList column directly so the calculation isn’t performed there.

Is there somewhere I can find this calculation so I can add one for my custom calculated field? I can do it in the RDL directly if I have to but I was hoping to avoid that if possible.

Many thanks in advance for any assistance.


(Nancy Hoyt) #2

Hi Richard,
I don’t think you can view calculations for calc’d fields nor add your own using the RDDs unless you have the software development kit version of the program. Perhaps someone with the SDK might chime in… (i’ve heard the SDK is expensive and not easy to use).

I’ve always found it incredibly annoying that Epicor has made it so difficult to work with their Rdds. Sometimes we’ve gone the path of BAQ reports where we’ve needed more control. Alternately, do calcs on report side of things… (not always too efficient however).

Nancy


(Jarrad Layne) #3

One thing you may be able to do is create the calculated field in the SSRS report. If you go into SSRS and right click on your data set then go to properties you will see a selection on the left called ‘Fields’. Inside of there you will see the Add button with two selections, query field or calculated field. Add a new calculated field and name it in the left column, then in the right column of fields click the fx button and write your expression

=Fields!Column1.Value - Fields!.Column2.Value

Another way would be to write it into the sql query as a new field then add that field as a query field to the data set.

I’ve also had issues in the past looking into RDD calculated fields in the past. There does not seem to be an easy, or accessible way to look at these.


(Richard Hodges) #4

Thanks guys I was afraid of that, I have got developer mode enabled so I’m assuming that means I have the SDK (though I could be wrong of course, it’s not my licence/software so I didn’t install it). I’ve been doing my calculations in the RDL file all day in case that was the issue. Many thanks for your input.

If it’s okay with you I do have another question, I have ran into another snag to do with the RDD’s. I’ve also tried adding in a new table to the RDD, it’s the Customer table I’ve added it as an additional datasource and then I’ve added a relationship for it with OrderHed on Company and CustNum (OrderHed is the Parent Customer is the child), it’s all been added to the RDL’s dataset fine and I can reference the Customer fields correctly but it’s not actually showing any data (so the report runs fine but the fields are blank), I think I may have missed a step or something but when adding a new table but I have no clue, I don’t suppose it’s something you guys have ever ran into?

Cheers again!


(CL Yuen) #5

I don’t sure what SDK includes but, developer mode and customization are basic Epicor function, nothing to do with SDK.
afaik, in E9, RDD calc field is a blackbox to users, we can use them in reports, further processing the result data on report, but it is not showing how those “calc_” is calculated.


(Nancy Hoyt) #6

Hi Richard,

A couple of thoughts. Sometimes messing around with the “relation type” field has had an effect on getting the data out for us. Also, check that your data sources / report table / exclusions doesn’t have all of your fields from customer table checked as excluded. See screenshot below where we tied in JobProd for addtl data into a job status report in the RDD.
Are you using Crystal or SSRS? We’re primarily on Crystal still.
If Crystal, is the data in the xml? If so then you probably need to mess around with the Crystal Database Expert / Links to get the link setup with the new table (last screenshot).

Nancy


(Monty Wilson) #7

SDK is the software developer’s kit. I haven’t run across a single consulting firm that’s shelled out the $thousands to get it, and I’ve asked some big ones. It’s my understanding that the SDK is what’s required to “see” the calculated fields of an RDD. However, others in the thread are on the right track: you can un-exclude a field from a table, make sure you save all levels, run the report again, and then in SSRS or Crystal do your own calculation, and you can replace the “calc” fields in the report with your own.


(Shawn Hobdy) #8

In my opinion,

It would be nice if there was a list out there that told you what the calculation was for each calculated field, since you can’t access it to see for yourself.

Probably would never happen but would be nice…