Using Sort By to Change Group On Fields in SSRS

Epicor ERP 10
, ,
1

Good morning,
I would like to use the BAQ Report SortBy field (Fields!SortBy.Value, “BAQReportParameter”), to change the group on values for a row group in a RDL/SSRS report.

Capture32d2 cdsd
Capture32d2 cdsd802×635 55 KB

In my BAQ Report, there are 4 ways to sort, by Job, Part, Customer, or Date. I would like to reference these sorts when defining the group on expression. This is what I have tried in the group on expression:

=if((Fields!SortBy.Value, "BAQReportParameter") = "Sort By Customer",Fields!Customer_CustID.Value, if((Fields!SortBy.Value, "BAQReportParameter") = "Sort By Job Num",Fields!JobHead_JobNum.Value, if((Fields!SortBy.Value, "BAQReportParameter") = "Sort By Job Req Date",Fields!JobHead_ReqDueDate.Value, if((Fields!SortBy.Value, "BAQReportParameter") = "Sort By Part Num",Fields!JobHead_PartNum.Value, Fields!JobHead_JobNum.Value))))

Capture342 rsd
Capture342 rsd1332×892 145 KB

To me this says: if the sort is by customer, then group by cust ID, otherwise if the sort is by job num, then group by job num, and so on. At the end if none of the sorts match, default to group on job num (which shouldn’t ever happen if I set it up right). In the end I want three levels of grouping, but I will settle for getting one level to work. Eventually, I want to group by the sort by field first, then job, then asm, then op.

When I try to upload the report with this expression, I get the error:

Error Detail

Description: The SSRS server returned the status code 500 (InternalServerError) with the following error text:
The Group expression for the grouping ‘JobHead_JobNum’ refers to the field ‘SortBy’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case.
Program: Ice.Core.TaskBase.dll
Method: .ctor
Line Number: 32
Column Number: 11

When I first wrote the expression, it defaulted to using First(Fields!SortBy.Value, “BAQReportParameter”), which gave a similar error due to the aggregate. I removed the ‘first’ part, and then got the error above.

I am open to ideas!
Thanks!
Nate

2 Likes
2

Do you have that field in your dataset?

3

e: nm duh

4

No, the sort by field is built into the BAQ report as, Fields!SortBy.Value.

5

You may want to try a (Select Max(SortBy) From BAQReportParameter_" + Parameters!TableGuid.Value + " T100") AS ReportGroupSortBy statement in the Select statement of the RDL.

Then you will be able to group by the Fields!ReportGroupSortBy.Value since that field will be in the “Report Dataset”.

It sounds like you are trying to group on a field referenced in a dataset that is NOT the main report dataset and SSRS does not allow that.

I hope i understand the issue correctly and this helps.
DaveO

1 Like
6

Been a while since I have done this, so not sure if this is correct.

I believe you need to put the Sort By field in the header, hidden, and then get the name of the textbox (usually Textbox followed by a number). Then instead of (Fields!SortBy.Value, "BAQReportParameter"), you will want First(ReportItems!Textbox0.Value, “BAQReportParameter”).

1 Like
7

Are you sure that the text is output in the SortBy field, or might it come out as something else? Create a copy of the report and delete the matrix. Then add a new one in with the BAQReportParameter dataset to see what is actually sent.

8

Nope, I think this is wrong.

You would add a textbox to the header and that expression would be =First(Fields!SortBu.Value, “BAQReportParameter”). Then in your sort expression, you would have it be ReportItems!Textbox0.Value.

1 Like
9

What is the “T100” part in there for?

1 Like
10

I ended up using daves suggestion to add the sortby to the dataset. This allowed me to sort and filter on that field. In reality I am not sorting by that field, but I am using it in a calculation to determine the sorting and grouping.

In my final results query I have this:

="SELECT [Calculated_OPDoneTotal],
        [Calculated_WC],
        [Customer_CustID],
        [JobAsmbl_AssemblySeq],
        [JobAsmbl_PartNum],
        [JobAsmbl_RequiredQty],
        [JobAsmbl_RevisionNum],
        [JobHead_JobNum],
        [JobHead_PartNum],
        [JobHead_ProdQty],
        [JobHead_ReqDueDate],
        [JobHead_RevisionNum],
        [JobOper_OpCode],
        [JobOper_OprSeq],
        [JobOper_QtyCompleted],
        [Calculated_Ops],
        [Calculated_Over25Ops],
        [OrderDtl_UnitPrice],
        (Select Max(SortBy) From BAQReportParameter_" + Parameters!TableGuid.Value + " T100) AS ReportGroupSortBy
 FROM dbo.[BAQReportResult_" + Parameters!TableGuid.Value + "]"

Then in my tablix row group properties, I set the group by and sort by in three levels using this criteria:

group on
=if(Fields!ReportGroupSortBy.Value="Sort By Customer",Fields!Customer_CustID.Value, if(Fields!ReportGroupSortBy.Value="Sort By Job Num",Fields!JobHead_JobNum.Value, if(Fields!ReportGroupSortBy.Value="Sort By Job Req Date",Fields!JobHead_ReqDueDate.Value, if(Fields!ReportGroupSortBy.Value="Sort By Part Num",Fields!JobHead_PartNum.Value, Fields!JobHead_JobNum.Value))))

and on
=if(Fields!ReportGroupSortBy.Value="Sort By Customer",Fields!JobHead_JobNum.Value, if(Fields!ReportGroupSortBy.Value="Sort By Job Num",Fields!JobAsmbl_AssemblySeq.Value, if(Fields!ReportGroupSortBy.Value="Sort By Job Req Date",Fields!JobHead_JobNum.Value, if(Fields!ReportGroupSortBy.Value="Sort By Part Num",Fields!JobHead_JobNum.Value, Fields!JobAsmbl_AssemblySeq.Value))))

and on
=if(Fields!ReportGroupSortBy.Value="Sort By Customer",Fields!JobAsmbl_AssemblySeq.Value, if(Fields!ReportGroupSortBy.Value="Sort By Job Num",Fields!JobOper_OprSeq.Value, if(Fields!ReportGroupSortBy.Value="Sort By Job Req Date",Fields!JobAsmbl_AssemblySeq.Value, if(Fields!ReportGroupSortBy.Value="Sort By Part Num",Fields!JobAsmbl_AssemblySeq.Value, Fields!JobOper_OprSeq.Value))))

I used the same exact formulas in my sorting options. This seems to be working properly now.
Thank you all!!

One last question should tie this up. In my group by expressions, when the sortby field is “Sort by Job Num” I want the third group by option to return to null instead of grouping by op seq. Can a group by be set to null by expression?

2 Likes
11

Just an alias - some habits are hard to ignore

1 Like
12

Yes it can! I set it to an empty string as null didn’t work. So my third group/sort expression is now:

=if(Fields!ReportGroupSortBy.Value="Sort By Customer",Fields!JobAsmbl_AssemblySeq.Value, if(Fields!ReportGroupSortBy.Value="Sort By Job Num", "", if(Fields!ReportGroupSortBy.Value="Sort By Job Req Date",Fields!JobAsmbl_AssemblySeq.Value, if(Fields!ReportGroupSortBy.Value="Sort By Part Num",Fields!JobAsmbl_AssemblySeq.Value, ""))))
1 Like
13

I found one more neat little gotchya… If you manually update the query expression in the RDL to include “ReportGroupSortBy” for example, then you cannot easily use Report Style to sync dataset for BAQ report. In my case I had to remove the grouping expressions then I could save it and sync the dataset. Then I had to add the “ReportGroupSortBy” back in to the query expression after I synced the dataset.

1 Like
14

I have two styles in this BAQ report. I guess I changed the dataset for one of the styles to include that “ReportGroupSortBy” field. But since I didn’t change the other style, I can no longer use Report Style to sync the dataset for the BAQ report. Is this just because I did not keep my datasets between the styles in sync? I think I have to open my other report style and modify that one to temporarily remove the “ReportGroupSortBy”, so that I can sync the dataset. Then I would have to update both report styles to add that back in.

Anyone else have issues like this? Am I on the right track?
Thanks!