I’m trying to recreate one of our custom Crystal reports in SSRS where the user can enter a customer and the report will pull all the open quotes for said customer, total the parts, labor and some custom field data. In Crystal I was able to use sub-reports and pass values back and forth to create the required output. I don’t seem to be able to do that with SSRS. The table relations in the SQL query pull duplicate values to the report which inflate the column totals.Any suggestions?
Definitely try and fix the source (BAQ or RDD), as opposed to trying to “hack” the results into what you want.
This is the SSRS query.
SELECT Erp.QuoteMtl.MtlSeq, Erp.QuoteMtl.Description, Erp.QuoteMtl.QtyPer, Erp.QuoteMtl.PartNum, Erp.QuoteAsm.PartNum AS QtAsmPartNum, Erp.QuoteAsm.Description AS QtAsmDesc, Erp.PartClass_UD.Number01,
Erp.QuoteDtl.PartNum AS QtDtlPartNum, Erp.QuoteDtl.RevisionNum, Erp.QuoteMtl.EstUnitCost, Erp.QuoteMtl.EstMtlBurUnitCost, Erp.QuoteDtl_UD.CheckBox02, Erp.QuoteDtl_UD.Number08, Erp.QuoteOpr.OprSeq,
Erp.QuoteOpr.ProdBurRate, Erp.QuoteOpr.ProdStandard, Erp.QuoteQty.MiscCost, Erp.QuoteQty.MiscCostMarkUp, Erp.QuoteQty.MiscCostDesc, Erp.QuoteDtl.QuoteNum, Erp.QuoteMtl.AssemblySeq, Erp.QuoteAsm.RequiredQty,
FROM Erp.QuoteDtl_UD RIGHT OUTER JOIN
Erp.QuoteDtl LEFT OUTER JOIN
Erp.QuoteQty ON Erp.QuoteDtl.Company = Erp.QuoteQty.Company AND Erp.QuoteDtl.QuoteNum = Erp.QuoteQty.QuoteNum AND Erp.QuoteDtl.QuoteLine = Erp.QuoteQty.QuoteLine ON
Erp.QuoteDtl_UD.ForeignSysRowID = Erp.QuoteDtl.SysRowID LEFT OUTER JOIN
Erp.PartClass_UD RIGHT OUTER JOIN
Erp.PartClass ON Erp.PartClass_UD.ForeignSysRowID = Erp.PartClass.SysRowID RIGHT OUTER JOIN
Erp.QuoteMtl RIGHT OUTER JOIN
Erp.QuoteOpr LEFT OUTER JOIN
Erp.QuoteAsm ON Erp.QuoteOpr.Company = Erp.QuoteAsm.Company AND Erp.QuoteOpr.QuoteNum = Erp.QuoteAsm.QuoteNum AND Erp.QuoteOpr.QuoteLine = Erp.QuoteAsm.QuoteLine AND
Erp.QuoteOpr.AssemblySeq = Erp.QuoteAsm.AssemblySeq ON Erp.QuoteMtl.Company = Erp.QuoteAsm.Company AND Erp.QuoteMtl.QuoteNum = Erp.QuoteAsm.QuoteNum AND
Erp.QuoteMtl.QuoteLine = Erp.QuoteAsm.QuoteLine AND Erp.QuoteMtl.AssemblySeq = Erp.QuoteAsm.AssemblySeq ON Erp.PartClass.Company = Erp.QuoteMtl.Company AND Erp.PartClass.ClassID = Erp.QuoteMtl.Class ON
Erp.QuoteDtl.Company = Erp.QuoteOpr.Company AND Erp.QuoteDtl.QuoteNum = Erp.QuoteOpr.QuoteNum AND Erp.QuoteDtl.QuoteLine = Erp.QuoteOpr.QuoteLine
WHERE (@QuoteNum = Erp.QuoteDtl.QuoteNum)
Dan, you may wish to work through your query a bit.
I see you wish to display costs… there is a lot in the query (joins) that I don’t understand.
There are a lot of fields in different tables that are needed on the report. The materials in assembly 0 pulling duplicate values due to the relationship with the QuoteOpr table but I need the operation info to calculate the operation values. the operations in the labor area of the report are duplicated due to the relationship to the QuoteMtl table.
On the Crystal report the material area is the main report and the labor area and final total are contained in a sub-report. The material total is passed to the sub-report to be used in the calculations on the sub-report.
I took your SQL expression and just reformatted it (added line breaks and tabs) for readability, and get:
That is some strange joining. I think you need to double check the join directions.
Dan, I understand. You can use grouping in the ssrs report using different tablixes in different groups or you can create subreports I suppose.
Group on assembly in the report and then for each group add a subreport that lists the materials related to that assembly and their costs.
Either way, I am with @ckrusen, your join directions don’t make sense.
I struggled to understand how to do some of these things and it may help to spend some more time with ssrs. I took a few classes on it at Epicor Insights and I think they offer classes in epicor university as well. There are also consultants that might be able to help you learn.
This thread that you posted here is far-reaching and might not yield a great result for you.
Are you asking us to give you the query you need or explain grouping or sub reports or are you just looking for general suggestions?
So I’m trying to create the report like we had it in crystal where the materials values are summed on the main report and then passed to a sub-report to add to the labor. That all works except that the sub-report is causing weird page breaks when viewing via print layout view. They both look right when run by themselves or in the standard preview panel. As soon as I click on print layout or print the report I get odd page breaks that are not caused by grouping, etc. Any ideas?
Is the sub-report being pushed to the next page?
No it breaking at seeming random spots. It looks the the problem happens when its converted to .pdf. If I export to Word, Excel, Power Point, etc. its looks just fine.
Things that we so simple in Crystal are a real pain, I’m beginning to hate SSRS. I fixed the issue by going into Advanced mode and changing all KeepWithGroup to “None”. I did not set them to begin with but some were set to before and after by Visual Studios.Now all I have left to do is figure out why some of the sub-report row/column outlines are being cut off when it it print on the main report.
The structure of your SQL query seems to be a bit unorganized. Perhaps I just haven’t run into a use case, but i’ve always lived by “If your think a right join is appropriate, you probably just have the wrong parent table.”
For what information your pulling in your select statement, i don’t see anything odd here to go the route of right joins. It might help with clarity/grouping to restructure your query.
When i created the query all my joins were left outer, it looks like the query designer changed my query.