Adding ID Column To Report

Kinetic ERP
, ,
1

I have a BAQ report that can be sorted in four ways. By Job Due date, by part number, by job number, or by customer. Each of these sorts come with a few layers of sorting. For example, if the user chooses the sort option for due date, then my expressions chooses the right sorting field.
Sort 1:

=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_DueDate.Value, if(Fields!ReportGroupSortBy.Value="Sort By Part Num",Fields!JobHead_PartNum.Value, Fields!JobHead_JobNum.Value))))

Sort 2

=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))))

Sort 3

=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, ""))))

I want to add an ID field for users to quickly identify a line when reviewing this report as a group. It is easier to say item 123, than part number 11238419233. I can make the BAQ calculate a dense rank, but that means I have to hard code in the sorting option (if I want the IDs to be sequential). This is the expression I use to create the dense rank in my BAQ:

DENSE_RANK() OVER (ORDER BY OldTop.JobHead_JobNum, OldTop.JobAsmbl_AssemblySeq)

I would like to apply this kind of expression at the report level, after the sort has been determined. I think that I would need different syntax, and report builder doesn’t like to use dense rank. Can anyone say if this is possible? I want to rank my report items based on the sort that gets selected at the BAQ report screen.

Thanks for your time!

2

Just to make sure I understand what you’re asking: you want to apply some kind of ranking to the BAQ and have it always correlate to a given row of data regardless of how it is sorted?

3

No not quite. I would like my BAQ report to always list the items in sequential ID order. I want that sequential ID order to be determined at the time the report is run based on which option the user chooses at the BAQ Report level. Does that make sense?

4

I could be over-simplifying this… but do you use the Sort field as defined at the BAQ Report? Does that sort option dynamically drive the BAQ like the filters do? If so, you would just need to display the row_number as a calculated field because the BAQ will be sorted based on the input… then output to the report server already sorted, right?

1 Like
5

I have a custom field that I added to my dataset like this:

,(Select Max(SortBy) From BAQReportParameter_" + Parameters!TableGuid.Value + " T100) AS ReportGroupSortBy

When the user chooses a sort by from the BAQ Report drop down list, the RDL checks that sortby field and then applies a 3 level sort based on which sort was selected. If the job number is selected, then it first sorts by job num, then by asm seq, then by op seq. It does the same thing depending on if you choose to sort by job date, or customer, just putting the most important sort field first.

I tried applying the expression =RowNumber(“BAQReportResult”) to an ID field in my RDL. I added this as a field to the left inside my main group. It does seem to apply a unique value to each row, but the values are not sequential. Can I use RowNumber to sequentially identify the rows based on the sort the user chooses?

6

Hmmm. I’m sorry - I thought you were sorting using the Sorts in the BAQ Report here.

image
image826Ă—579 13 KB

I meant do the Row Number as a calculated field in the BAQ and then add that new field to your BAQ report RDL and display it.

7

I am using the report sorting:

Capture2345234wefrf
Capture2345234wefrf802Ă—629 53.2 KB

Once the user chooses a sort, the RDL file parses that out and instead of applying the sort as defined by the BAQ Report, it applies the sort here:
Capture24r2ef43
Capture24r2ef43964Ă—953 101 KB

It the uses the expressions I posted earlier to determine the actual fields that get sorted.

1 Like
8

Oh gosh. OK. Yeah sorry. I haven’t used it before. I was hoping it applied to the BAQ like the filters do. That’s a bummer.

9

could this work:

10

Ooohh tricky! I will give it a shot!

11

Well that kind of worked. But no matter what I do, it always starts at 659 instead of 1. This is my code, just like the example:
Report Code:

Public Dim Rank AS Integer = 0

Public Function GetRank() AS Integer
  Rank = Rank + 1
  Return Rank
End Function

It is called in the tablix with the expression:

=Code.GetRank()

Is there any other way to make sure it is set to 1 or 0 before beginning the report?

Capture34f43f4f
Capture34f43f4f826Ă—356 66 KB

12

Weird. Does that 659 correlate with the number of rows or anything useful? It’s a shame Row Number didn’t work right. That’s really all you need, right?

13

I can’t figure out where it comes from. My underlying BAQ has tens of thousands of records, they get compressed and filtered down to show a row per assembly sequence. It could be there are about 670 assembly parts once combined. It is just weird that it starts there are 659 instead of 1.

14

Found this on google search:

=RunningValue(Fields!ItemId.Value,CountDistinct, "DataSet1")

So, it will look at your dataset (need to use whatever you named yours) and gives you a running count of how many times it sees the Fields!xxx.Value (which I think would be the field you’re grouping on)?

Haven’t tried it but may work for you.

1 Like
15

Jackpot! This expression did the trick! You guys are my heroes!

=RunningValue(Fields!JobAsmbl_PartNum.Value,CountDistinct, "BAQReportResult")

I can even concatenate values in the expression to further refine my value:

=RunningValue(Fields!JobAsmbl_PartNum.Value & Fields!JobHead_JobNum.Value,CountDistinct, "BAQReportResult")
2 Likes
16

It was a lucky find!

2 Likes