Need Help Adding JobMtl.DocUnitPrice to SCTicket Service Call Center


(Shawn Hobdy) #1

Hello,

I have someone who is telling me I need to add the JobMtl.DocUnitPrice to the Service Call Center report.

I have tried to add the table JobMtl to the dataset FSCallHd in SCTicket. That didn’t help.

Tried looking for it in Linked Tables, no good.

Can anybody out there tell me the best way to do it?

I would really appreciate it.

Thanks,
Shawn


(Shawn Hobdy) #3

Hello,

I would appreciate any help at all.

Anybody? Please?


(Shawn Hobdy) #4

Hello,

Still needing help on this.

I tried adding JobMtl to SSRS report but it doesn’t seem to be pulling all the PartNum according to the temp table for JobMtl I looked at. It pulls in all the actual parts but not the PartNum called SvcLabor.

Any ideas out there?


(Chris Conn) #5

Are you actually modifying the Report Data Definitions to include and define the relationships for those new tables?

Somewhat similar: Add to RDD


(Shawn Hobdy) #6

@Chris_Conn

The tables are already in RDD. I didn’t add or modify in any way.

All I tried to do was add a relationship in SSRS Report and pull data from there.

When I did that it started multiplying the fields by the number of items in the details row and still didn’t fill it in.

Any ideas?


(Shawn Hobdy) #7

Any ideas?


(Chris Conn) #8

Not sure, reports are not my forte. I do have some confusion about why you are having to modify relationships in the SSRS if the data you want is already included on the RDD. From my understanding, the RDD is what drives the data that will populate the dataset for the report.


(Shawn Hobdy) #9

The table is in RDD, yes. But it was not in the SSRS report mapped to the dataset I needed it to be mapped to. It was in a separate dataset that didn’t seem to be able to pull the data. I tried adding it to the FSCallHd dataset but didn’t seem to help.

Any ideas anyone???

Really stuck here and need to make some progress on it soon.


(Shawn Hobdy) #11

Hello,

I got pulled off this for a bit and trying to get back into it.

Does anybody out here know how to add the JobMtl.DocUnitPrice to the dataset FSCallHd in the SCTicket so I can use that field for some calculations?

Any help is appreciated.

Thanks,

Shawn


(Shawn Hobdy) #12

Help? Anybody?


(Bruce Ordway) #13

In the ServCall.Rdl?
Then I’m thinking you’d want the SUM of JobMtl.DocUnitPrice added to FSCallHd.
Since there can be multiple materials for a job. Or do you really want to add JobMtl.DocUnitPrice to ServCall_Materials.Rdl - in the JobMtl dataset?

Either way… I’d start by making a copy of the RDD SCTicket and remove the exclusion for JobMtl.DocUnitPrice. Then make a copy of the SCTicket report style and specify the new RDD.

Simply allowing the field in the RDD doesn’t always add your field automatically to the desired dataset in your reports RDL file(s).

The thread below is for a different report but…
Might give you an idea (of one way) you can add new field(s) to report RDL/dataset(s) that are used by SCTicket.


(Shawn Hobdy) #14

@bordway,

The specifics of why I need it is because my employer wants to take the specific cost of labor and divide it by a number to determine the number of hours to show on ticket.

So I need the SCTicket to pull the cost of the labor (LABR) and put in the ticket.

I have tried removing exclusion from the JobMtl table in the RDD. Since the table I am working in is set to FSCallHd, I can only get First or Sum for the DocUnitPrice field instead of one specific one.

Do you know any way to obtain the specific price?

Any and all help is certainly appreciated.


(Bruce Ordway) #15

I haven’t looked at the specifics…
So just what I’d start with… trying to add JobMtl.DocUnitPrice to dataset FSCallHd
AND…it looked like this would need to be the SUM, since there can be multiple mtl’s per job?
If so, similar instructions as that thread I referenced earlier
EXCEPT… would also need the SUM statement to fit into the expression for FSCallHd dataset ( via properties --> query --> expression )

Since the expression editor is so primitive… I often times will use a BAQ to help get the statement started. If you can diagram the joins, fields to display I might be able to help.


(Shawn Hobdy) #16

@bordway,

There will only be one LABR price and that is what I need to reference. SUM will not work.

I tried adding the JobMtl table to the FScallHd dataset but then it started printing multiples of the rows instead of the correct number of rows.


(Shawn Hobdy) #17

I did a left outer join.


(Bruce Ordway) #18

That is why I was thinking of a SUM…
because the JobMtl table can have many rows per each Job.


(Shawn Hobdy) #19

@bordway,

Maybe I am misunderstanding. I am trying to extract only one number from the field ‘DocUnitPrice’ for the row that contains a SCMiscCode of ‘LABR’. Each job will only have the one row that contains the code of ‘LABR’. I do not need any other data from this table. If I summed all the fields in the table for that job, it would give me an incorrect number.

Am I missing something?


(Bruce Ordway) #20

OK… the thread started out with JobMtl.DocUnitPrice, so I was still thinking that.

I think I’m probably the one missing something.
Can you explain where the field SCMiscCode shows up, RDD and/or
in the SSRS report.
And where DocUnitPrice shows up in the RDD?


(Shawn Hobdy) #21

@bordway,

The boss wants me to add a row to the report that calculates the number of hours of labor. The only good way to do this is to get the JobMtl.DocUnitPrice from the row that contains JobMtl.SCMiscCode value of ‘LABR’. The matrix in the report is set to use the FSCallHd dataset which does not normally contain the JobMtl table in it. In order to get the one row that contains the value of ‘LABR’ in it, I tried to add the JobMtl table with a left outer join I think. Once I did that, the row started printing multiple times instead of once. If you want me to post the example dataset query here, I can. If I am leaving something specific out, please ask and I will answer it to the best of my abilities.


(Bruce Ordway) #22

OK… I think this is making more sense now
I’m assuming you added the left outer join in SSRS expression for FSCallHd?
in addition to adding the JobMtl table, your expression will also need a filter by that code ‘LABR’?
BUT… if JobMtl can still return more than one row after all this THEN you’d still need to figure out some kind of a SUM in the statement (and/or Group By).

Keeping in mind I don’t have the same data as you, it’s hard for me to build an example up.
Also, I’m looking at editing the expression for FSCallHd (coped below for ref), there are other ways to skin the cat… maybe adding thru subreport?

FSCallHd - stock expression
="SELECT T1.CallComment,
T1.CallNum,
T1.CallPriority,
T1.SchedDate,
T1.Calc_SchTime,
T1.Calc_ShipToContactName,
T1.Calc_ShipToAddr,
T2.CallComment as FSCallDt_CallComment,
T2.CallLine,CAST( T2.CallQty as float ) as CallQty,
T2.IUM,T2.JobNum,
T2.LabCovered,
T2.LineDesc,
T2.MatCovered,
T2.MiscCovered,
T2.PartNum,
T2.ProjectID,
T2.RevisionNum,
T2.SerialNumber,
T2.XPartNum,
T2.XRevisionNum,
T2.Calc_ContDesc,
T2.Calc_ReasonDesc,
T2.Calc_SerialNumber,
T2.Calc_WarrDesc,
CAST( T3.AssemblySeq as nvarchar ) as AssemblySeq,
T3.CallLine as JobAsmbl_CallLine,
T3.CallNum as JobAsmbl_CallNum,
T3.Company,T3.[Description],
T3.JobNum as JobAsmbl_JobNum,
T3.PartNum as JobAsmbl_PartNum,
T4.MiscCode,CAST( T4.Calc_DocBillMiscAmt as float ) as Calc_DocBillMiscAmt,
T4.MiscCode_Description,
T4.Calc_SCMiscCode,
T4.Calc_SCMiscDescription

FROM FSCallHd_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN

FSCallDt_" + Parameters!TableGuid.Value + " T2

ON T1.Company = T2.Company
AND T1.CallNum = T2.CallNum

LEFT OUTER JOIN JobAsmbl_" + Parameters!TableGuid.Value + " T3

ON T2.Company = T3.Company
AND T2.CallNum = T3.CallNum
AND T2.CallLine = T3.CallLine

LEFT OUTER JOIN FSMisc_" + Parameters!TableGuid.Value + " T4

ON T3.CallLine = T4.CallLine
AND T3.CallNum = T4.CallNum
AND T3.Company = T4.Company
AND T3.AssemblySeq = T4.Calc_AsmSeq"