SOForm subreport SOForm_OLMscCharges appears to be buggy

ckrusen · November 8, 2019, 4:36pm

I’m having an issue with printing OrderAck reports for orders with high line counts. They tend to timeout before completing.

After a lot of digging, it appears that the subreport SOForm_OLMscCharges is to blame.

When I remove that subreport, a 184 line Order takes a few seconds to render. With that subreport in, it times out. And this Order has no Misc Charges!

Opening the subreport in Report builder and previewing it, it takes about 30 seconds to complete - to just return no records.

So at 30 seconds per order line, that 180 line report would take 90 minutes to complete.

Running the subreport’s query query expression in SSMS, yields that it returns 31,000 rows. Again, this report has no Misc Charges (neither line nor header).

Copying the results from SSMS to Excel, shows that there are multiple records for each Order Line - I would have thought there would be one per MscCharge (so in my case zero records). I get 176 records per Order Line (8 shy of the 184 lines in the order).

I now see that the OrderNum field is coming up 'Null`

Here’s the SQL query

SELECT T1.OrderRelNum,T1.OrderLine,T1.ReqDate, T2.OrderLine as OrderMsc_OrderLine,T2.MiscCode,T2.OrderNum,T2.DocMiscAmt, _over_400_more_fields_not shown_
FROM OrderRel_a23558f6332a45a2a68d03479f221838 T1
 LEFT OUTER JOIN OrderMsc_a23558f6332a45a2a68d03479f221838 T2
  ON T1.Company = T2.Company AND T1.OrderLine = T2.OrderLine AND T1.OrderNum = T2.OrderNum
 JOIN OrderHed_a23558f6332a45a2a68d03479f221838 T3
  ON T1.OrderNum = T3.OrderNum	AND T1.Company = T3.Company
 JOIN RptLabels_a23558f6332a45a2a68d03479f221838 T4
  ON T4.RptLanguageID = T3.RptLanguageID LEFT OUTER JOIN OrderDtl_a23558f6332a45a2a68d03479f221838 T5 ON  T3.Company = T5.Company AND T3.OrderNum = T5.OrderNum
 LEFT OUTER JOIN FSContDt_a23558f6332a45a2a68d03479f221838 T6 ON  T2.Company = T6.Company AND T2.OrderLine = T6.OrderLine AND T2.OrderNum = T6.OrderNum

The Report’s dataset does use filters,

so none of these fields display, but they all are returned for every Order Line.

Seems like the RDD makes a bad OrderMsc_<GUID> table.

ckrusen · November 8, 2019, 5:26pm

I did a few tests with a couple different Orders.

Setup

Open SOForm_OLMscCharges in Report Builder
Open the properties of dataset OrderMsc
Open the Query Expression and copy it
Paste the query expression into a new SSMS for the reports DB.
In Order Entry, select an order with many lines
Print the order, using the style Standard - SSRS
After the report renders, copy the GUID from System Monitor (reports tab)
Back in SSMS, edit the query
a. Removing the leading =", and trailting "
b. Replace " + Parameters!TableGuid.Value + " (including the quotes) with the GUID from step 7.
Execute the Query on SSMS.

Using the GUID for an order with 373 lines, the query in SSMS returned over 53,000 records, and took 12 seconds to process. This query is run for each Order line, so it would have taken 4,476 seconds just for the OLMisc subreports. That’s almost 1 hour 15 minutes.

Using the GUID for an order with 252 lines:

63,504 records, and took 12 seconds to process.

Using the GUID for an order with 98 lines:

8,836 records, and took 2 seconds

Using the GUID for an order with 10 lines:

100 records, and took less than 1 second

Randy · November 8, 2019, 7:44pm

We don’t use line misc charges either so I at first thought we removed that subreport but alas we still have it and never had problems. Our subreport has the same filters too.

What are the parameters passed from the main report? Our are:

ckrusen · November 8, 2019, 8:31pm

Opening the SOForm_OLMscCharges RDL in Report builder and previewing it (pass it a GUID from an OrderAck with lots of lines, and supply the other params - OrderNum and OrderLine), and you see how long that takes to render - with no records.

I think the issue is that the RDD is bad because the OrderRel.OrderNum isn’t being set in the temp tables created for the OLMscCharges tables.

The report “works” because even though 60,000 records are found for the Misc Line Charges, they get filtered out at the dataset. I would have thought the query for the subreport would have included the parameters in a WHERE clause.

Hmmmm … the subreport’s properties (in the main report) don’t include the OrderLine

But that doesn’t really change the fact that the query for the subreport doesn’t use any of those paramters at all. The are only applied as a filter, after the dataset is returned.

bcombs · July 16, 2020, 6:54pm

=FormatNumber((Code.SetLMiscExt((iif(Fields!FreqCode.Value = “E”, CountDistinct(Fields!OrderRelNum.Value),1)) * Fields!DocMiscAmt.Value)), 2)

I have submitted this to Epicare they say it will be next year before any change is made to this problem. We calculate tariff by % on line misc charge with it charging on every release. The problem is the out of the box SSRS is multiplying the # of releases by the total tariff amount calculated and presenting this as the extended amount. This is fine when there is only 1 release, but if 2 or more releases then causes the tariff amount to display 2 or more times what the tariff amount should be. This is on the out of the box SSRS Order Acknowledgement. The weirdest thing is that on the back end it appears to invoicing correctly with no issues. Why cant the out of the box reports reflect the correct calculation. Looking for a simple solution now until next year.