BAQ report where baq includes an inner sub query

Epicor ERP 10
1

I’m a bit bamboozled by this one.

I have created a shipping analysis baq that allows us to assess on time, in full and on time in full per month by customer. As we can do multiple shipments per sales order release I need to include a sub query to get the last shipment date and total shipped quantity for each sales order release. This all works great in the BAQ and I have created a dashboard.

I have been asked to graph this data and have found the dashboard charts to be too limited. I have copied to excel and graphed this data but it requires quite a bit of faffing about.

I have tried to create a baq report but keep getting a message saying query execution failed for dataset rptlabels. Looking at the srss logs this is failing on the sub query - if I remove the sub query fields the execution works but without the data that I need.

I have recreated the query as a new data in a new report in SRSS and everything works - it just limits the amount of filtering that I can do and the interface appears different.

Doing some basic research I have seem some threads online about baq reports not working if they include inner sub queries but generally no confirmation that this is the case or resolutions. I’m just wondering if anyone else has come across this and it is indeed a limitation of baq reports or if there is some clever fix.

2

Are you getting the error in Report Builder? When the BAQ opens in report builder to create an SSRS report, you will see multiple datasets that populate. Make sure you are not linking to the “rpt label” dataset instead of the “BAQ result” dataset.

3

If you don’t have remote errors turned on you may want to do that. It can give you a better idea of issues that are causing the errors inside the system monitor.

Inside SSMS select your report sever then depending on your version you just need to change remote error property. A quick google search should give you more specific directions for your version.

4

I don’t have an answer for you but 50 points in the game of life for use of the word faffing in a technology setting. I like it.

Mark W.

1 Like
5

This is a bug supposedly fixed in 10.1.500.16

SCR 199448

6

I get no error in the report builder. It is when I try and run the report. I have tried just having a blank report with the datasets and it still gives the same error. When I look in the srss logs it is always the sub query that I get the issue - below is an example of the error in the SRSS logs

processing!ReportServer_0-20!ee4!04/26/2017-12:08:05:: e ERROR: An exception has occurred in data set ‘RptLabels’. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘RptLabels’. —> System.Data.SqlClient.SqlException: Invalid column name ‘InvcDtl_OrderNum’.
Invalid column name ‘InvcDtl_OrderLine’.
Invalid column name ‘InvcDtl_OrderRelNum’.
Invalid column name ‘Calculated_LastShip’.
Invalid column name ‘Calculated_ShippedQty’.

7

Unfortunately we are on 10.0.7 and have no maintenance.

8

I have question that somewhat follows this thread
I have an existing BAQ that does quite a bit and I am adding to it and what I need to do is join Job Prod table (existing in BAQ) to a new table (Job Mtl) BUT… I only want to return records that satisfy a condition in the Order Dtl table (is part of the BAQ), SalesCatID= (my thing).

If it does not satisfy this condition, I dont want any job material records

I understand that constructing the BAQ around the Order Details to only get data based on the that condition would work at a glance but remember I have a pretty substantial BAQ that I want to add Job Material info to ONLY if that condition is met (otherwise I get way too much data that I dont want)

This may be a sub querry but I don’t understand how to set it up (the ICE documentation I have ready dont help me much on this topic)

9

All you need to do to make a subquery is go to subquery options, and add new Innersubquery. Then you can make your query, and in the top level you add the subquery and treat it like a normal table. The hardest part about it is it can be tedious to test out the sub. To do that, just change the type on the sub to Top Level, and the Top Level to Sub and run it. Then change it back when you are good.

image
image.png815×459 25.4 KB

image
image.png796×387 31.6 KB

2 Likes
10

Brandon

Thank you

I think I follow you and have stumbled around with exactly what you describe but have not gotten it all clear.

Not that I better understand it is the way to go, I will pursue it deeper and may come back to you with more specific questions.

Have not worked with a subquery before so establishing correctly is a stumbling block this first time.

John Zachow

Vice President, Wire & Cable Systems

P: +1 920.215.6628 | C: +1 860.271.9397

jzachow@davis-standard.com

davis-standard.com

Davis-Standard Logo_4color_Tagline

1 Like
11

Make sure you spend some time searching around this site and reading. There is a lot of stuff on here that will answer most of your basic questions.

12

I thought I had a solution to this based on feedback from this posting but I am still missing something, see the picture of what I have and understand pretty well. What I am missing is when I bring SubQuery2 into SubQuery1 as a table, I have the join the way I want and am getting the correct data but too much of it.

What I need is in order to bring ANY data from table 3 (SubQuery2) I want a filter of IF Calc(ResaleCheck) = ‘True’ THEN return data ELSE ‘’ (dont return data)

I have played with a few ways of doing this without success.

This is a incremental improvement to a BAQ/DB I have been using for some time so this isn’t the entire picture, just what I am stumbling on.

Basically for a certain type of SalesCatID, I want to display ALL JobMaterial lines of selected data (staring with PartNumber and Mtl Seq). All other types of SalesCatID, I do not want to display ANYthing from the JobMaterial table

IMG_1936

13

Did you add your table criteria?

image
image.png1470×711 37.2 KB

14

I think it would be better to take baby steps. Lets fix one issue at the time.

I am guessing that your Subquery2 is the first query, is that working fine?

1 Like
15

to add if you can post the BAQ or the Query Phrase it would help us, help you.

1 Like
16

That full join is going to return everything in your subquery (even the stuff that doesn’t match the join). Are you sure you want a full join there? Or just the ones that match the job number?

image
image.jpg684×520 96.6 KB

17

All – thank you for your round of feedback and questions

I can make the entire BAQ work (subquery 1 and 2) if I find a field I can filter on that is in subquery 2 (filter is in table criteria)

image001.png2323×1471 159 KB

The field I have found to filter on is not bullet proof but “pretty good”

My intent was to use a field/table I am using in subquery 1 (that is 100% bullet proof) (OrderDtl.SalesCatID)

I could put that table in Subquery 2 but to link it to my table in use will take an intermediate table (JobProd), both of which I already need and use in Subquery 1

I cannot filter something in Subquery 2 that doesn’t exist in 2 (OrderDtl.SalesCatID) so I got a suggestion to build and Calc field which results in a yes/no and then use the calc field to filter.

I built a Calc field in Subquery 1 where the table/filed exists (Calculated.Subqueryfilter) and that returns the True/False correctly.

I then am trying to use that Calc field in Subquery 2 in place of my less than perfect existing filter.

On the Table Criteria, I cannot pull in the calc field, only JobMtl fields are available

On the SubQuery Criteria I can choose either the JobMtl table or Calculated table but it has not fields to choose (it seems to be offering me only calculated fields in Subquery 2)

Filtering in Subquery 2 is the only way that I have found what I want (return all the subquery 1 data regardless of subquery 2 but if the filter is met then I want the subquery 2 data added to the lines on subquery 1 (instead of them being blank and NOT bringing multiple job material records when the filter is not met)

Maybe my structure/thought process is off completely but I can do exactly what I want with the filter in the Subquery 2 Table Criteria (this is the only spot it works or it filters too much or none at all)

I have changed some of my joins since my picture, I am using all rows from the table “above” the one I am selectively pulling the data rather than all rows from both tables.

I am tempted to live with the field that seems to have a near bullet proof filter I can use and is contained in the table in the subquery 2 but am holding hopes for using a filter field that is accurate but is not in the subquery 2

John Zachow

Davis-Standard Logo_4color_Tagline

18

Did you add the other fields in the display part of your subquery2? You can filter on the top level by any fields that you have displayed in a subquery.

You can bring in that table and use it as many times as you want. It just creates a new alias (adds that number at the end) this is fine.

Why don’t you bring it into Sub2 then?

Do this. Either post the SQL in the general tab, or export your BAQ and upload it here. We are all having a hard time figuring out why you are having difficulties.

Or at least more screenshots.

19

2nd question in the BAQ is; I am using “Define Parameters” and All I can do is name the new fields as Database names but not put in a corrected “display name”

Is there way?

Seems nit picky but I am a user rather than a IT type

image001.png3000×2000 170 KB

John Zachow

Davis-Standard Logo_4color_Tagline

20

thank you all for your help
I was able to successfully implement the sub-query with the filters I was looking for

1 Like