Sorting a String within a Subquery

Epicor ERP 10
1

I have a BAQ that pulls all the job information for a specific job number. Part of the BAQ includes a subquery that gets a list of (releases) quantities and dates related to the job. The query does a good job at pulling the values I need, however I can’t seem to sort them like I want.

The query in question is SubQuery4 in my BAQ. I am not sure if I can post images yet, so I will describe the layout.

SubQuery4:
Table1: JobProd
Table2: OrderRel

Subquery Criteria:
JobProd=JobHead.JobNum
OrderRel.OurReqQty=OrderRel1.OurReqQty FOR XML PATH(’’)

The only field returned from SubQuery4:
MyQtyDate=convert(varchar, cast(OrderRel1.SellingReqQty - OrderRel1.OurJobShippedQty - OrderRel1.OurStockShippedQty as decimal(10,0))) + ’ ’ + convert(varchar, convert(varchar, OrderRel1.ReqDate, 1) ) + char(10)

At the top level, my first query pulls the calculated value from subquery 4 like this:
MyQtyDates=REPLACE(REPLACE({SubQuery4} , ‘</Calculated_MyQtyDate>’,’’),’<Calculated_MyQtyDate>’,’’)

This does a great job at showing the releases I need in a single concatenated field. However, I want to be able to sort them in date order. So if release 1 gets moved to a later date, then it is not sorted to the top as it is now.

Once I have the data in the BAQ I pass it to a BAQReport and populate the SSRS report.

In an attempt to sort the text field in the report designer, I took another drastic step towards complicating things. I created a function that should take in the unsorted, concatenated field that I generated in the BAQ above, then sorts it according to the dates.

Here is the function:
Function SortString (sort As String) As String
Dim str() As String = Split(sort,chr(10))
Dim rtn, NewQty, NewDate As String
Dim j, x as Integer
Dim dtb As New System.Data.DataTable
dtb.Columns.Add(“Qty”)
dtb.Columns.Add(“myDate”)
j = str.Length() - 1
For i As Integer = 0 To j
x = InStr(str(i)," ")
NewQty = Left(str(i),x-1)
NewDate = Right(str(i),10)
dtb.Rows.Add(NewQty,NewDate)
Next
Dim dvw As System.Data.DataView = dtb.DefaultView
dvw.Sort = “myDate ASC”

Dim dtbSorted As System.Data.DataTable = dvw.ToTable()
Dim row As System.Data.DataRow
For Each row In dtbSorted.Rows
Dim strQtyDate As String
strQtyDate = row(“Qty”) + " " + row(“myDate”)
rtn = rtn + strQtyDate + chr(10)
Next row
Return rtn.Trim(chr(10))
End Function

This function is called from the report field this way:
=Code.SortString(Fields!Calculated_MyQtyDates.Value)

Even though it looks like the code works, and there are no errors when uploading the report, the field outputs a simple, useless #ERROR instead of my sorted values.

I have included the proper assemblies in the report form:
System.Data
System.XML

Can anyone help push me in the right direction here? The overall goal is to get a sorted list inside a subquery.

There’s a lot going on here, so feel free to ask clarifying questions.
Thanks!
Nate

2

What is the query?

3

why not have your sub query ALSO return the date, then your top query can sort by that date for you.

4

Why don’t you just sort the display fields in the subquery? The tab next to column select is sort order.

Otherwise, maybe reconsider why you are crunching it into one field and/or one row for the releases. At least that is what I think you are saying you are doing by skimming your post. Can’t you just use the groupings in the SSRS to do what you want. Make the query pull a row for each release, then make a group by job or part or whatever and another group for your detail records which are the releases. Sort it the group.

5

Hi Mike!

I started there, sorting the display fields. This made my BAQ return an error. The problem is compounded I think. The display field is a calculated field that contains both a quantity, a few spaces, and the related date. On top of that, subquery4 is not directly
referenced by the top level query. The only reference is through a calculated field.

With that confusing part out of the way, I think you are on to something with utilizing the grouping in SSRS. I can get my BAQ to return one row for each of the unique releases I want. I expect I could even sort them properly that way.

The problem I’ve experienced with this approach is really my ineptitude with building reports in this limited SSRS report builder.

My report is built on top of a giant PNG image template. I have to position the fields over the appropriate section of the report. So far I can’t seem to use the grouping to float one set of data (the quantity + date field I made) on top of the single unique
record that makes up most of the report.

If you could help me with this approach, what other information would be helpful for you?

Thanks a bunch!

Nate

6

Here is the entire query phrase from the BAQ.

/*

  • Disclaimer!!!
  • This is not a real query being executed, but a simplified version for general vision.
  • Executing it with any other tool may produce a different result.
    */

select distinct
[JobHead].[JobNum] as [JobHead_JobNum],
(left(JobHead.JobNum,charindex(’/’,JobHead.JobNum)-1)) as [Calculated_MyJob],
(right(JobHead.JobNum,len(jobhead.jobnum)-charindex(’/’,JobHead.JobNum))) as [Calculated_MyLot],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[SubQuery3].[Customer1_CustID] as [Customer1_CustID],
[SubQuery3].[OrderHed1_PONum] as [OrderHed1_PONum],
[JobHead].[PartNum] as [JobHead_PartNum],
[SubQuery3].[OrderRel_OrderNum] as [OrderRel_OrderNum],
[JobHead].[PartDescription] as [JobHead_PartDescription],
[JobAsmbl].[PullQty] as [JobAsmbl_PullQty],
[JobAsmbl].[QtyPer] as [JobAsmbl_QtyPer],
(’(’ + cast(SubQuery5.ShipTo2_ShipToNum as varchar) + ‘) ’ + cast(SubQuery5.ShipTo2_City as varchar) + ‘, ’ + cast(SubQuery5.ShipTo2_State as varchar)) as [Calculated_MyShipTo],
(REPLACE(REPLACE(((select
(CAST(JobOpDtl.ResourceGrpID AS VARCHAR) + ‘, ‘) as [Calculated_WC_List]
from Erp.JobOpDtl as JobOpDtl
where JobOpDtl.JobNum = JobHead.JobNum and JobOpDtl.AssemblySeq = JobAsmbl.AssemblySeq and JobOpDtl.ResourceGrpID = JobOpDtl.ResourceGrpID FOR XML PATH(’’))) , ‘</Calculated_WC_List>’,’’),’<Calculated_WC_List>’,’’)) as [Calculated_WCS],
[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
(REPLACE(REPLACE(((select top (10)
(convert(varchar, cast(OrderRel1.SellingReqQty - OrderRel1.OurJobShippedQty - OrderRel1.OurStockShippedQty as decimal(10,0))) + ’ ’ + convert(varchar, convert(varchar, OrderRel1.ReqDate, 1) ) + char(10)) as [Calculated_MyQtyDate]
from Erp.JobProd as JobProd
inner join Erp.OrderRel as OrderRel1 on
JobProd.OrderNum = OrderRel1.OrderNum
and JobProd.OrderLine = OrderRel1.OrderLine
and JobProd.OrderRelNum = OrderRel1.OrderRelNum
where JobProd.JobNum = JobHead.JobNum and OrderRel1.OurReqQty = OrderRel1.OurReqQty FOR XML PATH(’’))) , ‘</Calculated_MyQtyDate>’,’’),’<Calculated_MyQtyDate>’,’’)) as [Calculated_MyQtyDates],
[SubQuery6].[Calculated_MyTotQty] as [Calculated_MyTotQty],
(iif(right(JobHead.XRefPartNum, 4) =’-OSP’, JobHead.XRefPartNum, iif(right(JobHead.XRefPartNum, 5) =’-OSPR’, JobHead.XRefPartNum, JobHead.PartNum))) as [Calculated_MyPartNum],
(left(JobHead.JobNum,5)) as [Calculated_PlainJob]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
and ( JobAsmbl.AssemblySeq = 0 )

left outer join (select
[OrderDtl].[Company] as [OrderDtl_Company],
[OrderHed1].[PONum] as [OrderHed1_PONum],
[JobProd1].[JobNum] as [JobProd1_JobNum],
[OrderRel].[OrderNum] as [OrderRel_OrderNum],
[OrderRel].[OrderLine] as [OrderRel_OrderLine],
[OrderRel].[OrderRelNum] as [OrderRel_OrderRelNum],
[Customer1].[CustID] as [Customer1_CustID],
[ShipTo1].[CustNum] as [ShipTo1_CustNum],
[ShipTo1].[ShipToNum] as [ShipTo1_ShipToNum],
[ShipTo1].[City] as [ShipTo1_City],
[ShipTo1].[State] as [ShipTo1_State],
[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
[OrderRel].[ReqDate] as [OrderRel_ReqDate]
from Erp.JobProd as JobProd1
inner join Erp.OrderHed as OrderHed1 on
JobProd1.Company = OrderHed1.Company
and JobProd1.OrderNum = OrderHed1.OrderNum
inner join Erp.OrderDtl as OrderDtl on
OrderHed1.Company = OrderDtl.Company
and OrderHed1.OrderNum = OrderDtl.OrderNum
inner join Erp.OrderRel as OrderRel on
OrderDtl.Company = OrderRel.Company
and OrderDtl.OrderNum = OrderRel.OrderNum
and OrderDtl.OrderLine = OrderRel.OrderLine
inner join Erp.Customer as Customer1 on
OrderRel.Company = Customer1.Company
and OrderRel.ShipToCustNum = Customer1.CustNum
inner join Erp.ShipTo as ShipTo1 on
Customer1.Company = ShipTo1.Company
and Customer1.CustNum = ShipTo1.CustNum
and Customer1.ShipToNum = ShipTo1.ShipToNum
group by [OrderDtl].[Company],
[OrderHed1].[PONum],
[JobProd1].[JobNum],
[OrderRel].[OrderNum],
[OrderRel].[OrderLine],
[OrderRel].[OrderRelNum],
[Customer1].[CustID],
[ShipTo1].[CustNum],
[ShipTo1].[ShipToNum],
[ShipTo1].[City],
[ShipTo1].[State],
[OrderRel].[OurReqQty],
[OrderRel].[ReqDate]) as SubQuery3 on
JobHead.Company = SubQuery3.OrderDtl_Company
and PlainJob = SubQuery3.OrderRel_OrderNum
left outer join (select
[JobProd2].[OrderNum] as [JobProd2_OrderNum],
[JobProd2].[OrderLine] as [JobProd2_OrderLine],
[JobProd2].[OrderRelNum] as [JobProd2_OrderRelNum],
[JobProd2].[JobNum] as [JobProd2_JobNum],
[ShipTo2].[ShipToNum] as [ShipTo2_ShipToNum],
[ShipTo2].[City] as [ShipTo2_City],
[ShipTo2].[State] as [ShipTo2_State]
from Erp.JobProd as JobProd2
inner join Erp.OrderRel as OrderRel2 on
JobProd2.Company = OrderRel2.Company
and JobProd2.OrderNum = OrderRel2.OrderNum
and JobProd2.OrderLine = OrderRel2.OrderLine
and JobProd2.OrderRelNum = OrderRel2.OrderRelNum
inner join Erp.ShipTo as ShipTo2 on
OrderRel2.ShipToCustNum = ShipTo2.CustNum
and OrderRel2.ShipToNum = ShipTo2.ShipToNum) as SubQuery5 on
JobHead.JobNum = SubQuery5.JobProd2_JobNum
left outer join (select
[JobProd3].[JobNum] as [JobProd3_JobNum],
(sum(OrderRel3.OurReqQty - OrderRel3.OurJobShippedQty - OrderRel3.OurStockShippedQty)) as [Calculated_MyTotQty]
from Erp.JobProd as JobProd3
inner join Erp.OrderRel as OrderRel3 on
JobProd3.OrderNum = OrderRel3.OrderNum
and JobProd3.OrderLine = OrderRel3.OrderLine
and JobProd3.OrderRelNum = OrderRel3.OrderRelNum
group by [JobProd3].[JobNum]) as SubQuery6 on
JobHead.JobNum = SubQuery6.JobProd3_JobNum

7

So your calculated field is a string field that looks like a date. The problem is then that the field is being sorted by string, which is not what you are looking for.

Cast/Convert the calculated field to a date field.

8

If you need help let us know.

Make the subquery the top query, and the main query a inner one.

That way we can focus on what the subquery is doing first. Once you are happy with the results, then we can connect back to the main query. Up to you.

9

Hi Ken,
It is true that the calculated field is a string. However, it is a string that contains both a quantity value and the date. I can’t cast/convert the calculated field to date since it is not strictly a date field.

The whole idea of returning the results of a query within a single cell like I am trying to do is a bit confusing to me. Using the “FOR XML PATH (’’)” in the subquery criteria allows me to return the values into a single cell like I need. I just can’t sort the values within that cell.

I also tried putting the subquery into its own single query. I can basically reproduce the same issue I was having. However, I need this to be a subquery so that I can utilize the REPLACE functions to strip out the XML headers that get applied using the FOR XML PATH output. Regardless, I still can’t sort the values inside a single cell. Here is the single query version of the issue:

img1
img1.jpg813×562 177 KB
img2
img2.jpg807×120 38.6 KB
img3
img3.jpg1007×529 127 KB

10

So after pulling the subquery into a single query. I modified it a bit to list the releases and dates I want and even sorted them using the display sort in the BAQ. But as soon as I move this to a subquery to include other details I get this error:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

So I can’t use order by in subqueries?

11

Why are you creating one field with two variables? Do you need to do this?

12

That goes back to the SSRS. I need one field that contains multiple fields to get my formatting correct. I believe with a different approach on the SSRS I could do this much easier.

Having said that, I seem to have come to a solution. The query below returns exactly what I need. Now I just have to squeeze it back into the big query. What an ugly mess! :smiley:

select 
	(replace(replace(((select 
	(convert(varchar,cast(SubQuery2.OrderRel_OurReqQty as int)) + '   ' + convert(varchar, SubQuery2.OrderRel_ReqDate,1) + char(10)) as [Calculated_MyQtyDates]
from  (select top (10)  
	[OrderRel].[OurReqQty] as [OrderRel_OurReqQty],
	[OrderRel].[ReqDate] as [OrderRel_ReqDate]
from Erp.JobProd as JobProd
inner join Erp.OrderRel as OrderRel on 
	JobProd.OrderNum = OrderRel.OrderNum
	and JobProd.OrderLine = OrderRel.OrderLine
	and JobProd.OrderRelNum = OrderRel.OrderRelNum
where JobProd.JobNum = '29624/1'

order by OrderRel.ReqDate)  as SubQuery2
where SubQuery2.OrderRel_OurReqQty = SubQuery2.OrderRel_OurReqQty FOR XML PATH (''))), '</Calculated_MyQtyDates>',''), '<Calculated_MyQtyDates>','')) as [Calculated_QtyDates]
13

I notice now what you are trying to do. Get the next 10 dates. Instead of having ten records you want to use one column for the ten data elements. This is a bit more work as you have to code the wrapper and then decode it as well.

Why not write a subreport in SSRS to get the data back at that time? Then you can use SSRS grouping. I noticed that @Mike suggested this as well above. If you go that route, credit goes to him. :slight_smile:

14

No, that’s the thing, the XML bit is only used within the BAQ to concatenate all the fields together into a single cell. The XML tags are stripped out in a calculated field using the replace function in SQL. No XML is passed to the SSRS.

15

I think this is the right idea. But I am just not sure how.

16

There are a few youtube video you can check out. It might be a good use of a few hours learning. It will save you time and headaches down the road.

17

This looks great! I will watch through and maybe get some useful info out. But I am hamstrung by this limited Report Builder that I have to use. It certainly isn’t Visual Studio! :stuck_out_tongue:
I am in SQL Server 2012 Report Builder 3.0

18

same. we use that report build as well. we have many sub reports.

19

I am afraid this is getting a bit far from my original post subject. However, I think I am getting closer to the correct solution.

I updated my BAQ to return one row for each release. (Instead of a single row and all the release concatenated into a single cell.) In this way I can sort the rows by date. However, when I load this into my report editor I get 5 pages, one for each record. I only want one page showing just the single record. Then I want the subreport to show the 5 releases related to the order.

After a bit of mucking about, I created a subreport, and added it to my main report. But I still get 5 pages of the main report, and the subreport only returns an error: Error Subreport could not be shown.

I will continue mucking…
Thanks!
:slight_smile:

20

All good.

You don’t need to change the BAQ for the main report.

The subreport is where the magic will happen. There you can use data from the main report, jobnum to get your second query. That will be your datasouce for the subreport. Then you just display the data how you want in the subreport.