I wanted to add sales order number in the ARForm.(Invoice). Downloaded the report style and checked in the datasets but I was not able to find the sales order number in the dataset.
Hence I checked in the Report data definition to make sure the order number is excluded. The order number was already excluded in the Invoicedtl datasources but why it is not appearing in the report style dataset to choose?
I am using ARForm.rdl file to modify the SSRS report.
Even though OrderNum is in the dataset (included in both InvcHead and InvcDtl tables it looks like), it isn’t specified in the Datasets query in the rdl file. So you’ll have to add it into the query yourself.
Order Number is not listed in the dataset properties or query. Can you please let me know how to add the query.
Anyone can help me on this. how to bring the sales order number in ARForm
Yeah, if it isn’t in the dataset you need to add it like all the rest of the fields. In @Adam 's posts above, he shows you the AR form data set highlighted. When you go into the fields section you need to click add and then add the OrderNum field. THEN, you need to go to the Query tab right above it and modify the query like he has highlighted in his second picture. Take a second and study how invoicenum is handled by the base report. You will then do the same to add the order number.
In other words, all of those fields that you see in the first picture are tied to the fields being returned in the query.
In order to add a field to a report you need to do three things:
Regarding #3: You may need to glance over query a bit. Most likely, you’ll simply add “T1.OrderNum,” to the beginning of that expression. That is, if yours starts like this:
=“SELECT T1.CorrectionInv,T1.TaxRegionCode…”
You’ll change it to:
=“SELECT T1.OrderNum, T1.CorrectionInv,T1.TaxRegionCode…”’
Nice Adam.
And in other cases, you may need to add a “prefix” to the field. Look at other fields coming from the same datasource/table in the RDD. You might need something like:
T1.Customer_ResaleID instead of just T1.ResaleID
Thanks everyone. I got it!!! Thanks for the steps Adam with the screenshots.
Adam one more help please…I am trying to add Tracking number in the Invoice. I did the following
1)In Report data definition - Copied the ARForm and named it as ARFormTrackNum
2)Added the ShipHead Table
3)Excluded, the fields, company, packnum & Tracking Numner
4)Created Relationship as the screenshot below
5)In Report Style - Duplicate the copy of ARForm
6)Choose the newly created data definition and clicked on the Synch dataset
But getting the below error.
Hi @subha,
Better not to use Sync Dataset feature as it’ll cause you field limit errors.
It’ll be good if you just add your ShipHead field in dataset as you did for ordernum.
Regards
Subhashree, the reason you are getting this error is because the AR Invoice Form is actually comprised of two folders. If you look closely you will see then the path on the default AR Invoice has a comma separating the two reports:
reports/CustomReports/ARInvoiceForm/ARForm,reports/CustomReports/ARPBForm/ARPBForm
I would check the downloaded folders to make sure the objects are there, the message is telling you that the report is not found where you downloaded it. Looks like it’s missing from your ARPBForm folder…
Alex,
As I checked, looks like I downloaded both the folders
but is that report in both folders?
is your join correct for the ShipHead Table?
@subha you have to add a join in the query tab. See how other tables are joined in the query such as Invc detail. You have to use the table ShipHead_ + " Parameters!GUID " + or something like that. Take a look at the query and see how other tables are joined. The first step is to make the relationship (join) in the report dataset as you already did. I think maybe that is what @AlexAverbukh is asking you to double check.
Yes Utah is correct, you should have a left outer join between InvDtl to ShipHead on
Company and PackNum
