FetchXML - Query Work Order and its associated Notes files

(0) Share

Report

Posted on by EBMRay

873

Hello everyone,

In the notes entity I have two images related to a work order with the below subjects:

Record 1 - Customer Signature
Record 2 - Resource Signature

I am building an SSRS report that is linked to the Work order entity and the notes entity.
I would like to display in the report the subject of the notes in two different text labels based on the data retrieved from my fetch XML.

I tried the below fetch XML but I am still struggling on the below points:

1. I have tried the below logic in advanced find which gave me the below query:

Line 10 in the above XML query should be changed based on the current WO ID when running the report instead of hardcoding the values.
2. how to pass a parameter for each text label to display for the first label Customer signature and the second label Resource signature based on the subject retrieved from fetch XML query.
The notes look like the below: (Two different records related to WO)

I was using a sub-report and passing some parameters to it but now I want to avoid sub-reports and build everything under the same report.
4162.ReportProp.PNG

In addition, previously assisted me on a similar topic on fetch XML but with different logic here

Can you please provide an example and the corrected XML query version to solve the above issue and display for those two labels the subject retrieved based on any WO ID the report is running?

Any help is highly appreciated.

Best regards,
EBMRay

All responses (6)

Answers (1)

EBMRay 873 on at

Like (0)

Report

RE: FetchXML - Query Work Order and its associated Notes files

Hi ,

Thank you so much! It is showing the images as expected so the trick is to filter the notes table by defining an alias to specify the content of the image we need.
Verified answer

Community Member Microsoft Employee on at

Like (2)

Report
RE: FetchXML - Query Work Order and its associated Notes files

Hi EBMRay,

Please try this fetch xml:

And insert two column and in each column insert an image(The design page like this):

Preview:
EBMRay 873 on at

Like (0)

Report

RE: FetchXML - Query Work Order and its associated Notes files

Hey Steve,

Thank you so much for providing an example.

I will test it on my end and get back to you.

Is it possible to group them by Work order ID? Because I want to show both signatures under one record for the work order instead of 2.
What should be changed in the table design to group them by WO ID?

Looking forward to your response.

Best regards,
Suggested answer

Community Member Microsoft Employee on at

Like (1)

Report
RE: FetchXML - Query Work Order and its associated Notes files

Hi EBMRay,

Please follow the steps:

1. Fetch XML is like this(You need to add the attribute you need):

2. Design the table like this:

3. Switch to Design page, add a column and insert a image:

4. Set image property like this(MIME type is up to your signature file):

Result:
EBMRay 873 on at

Like (0)

Report
RE: FetchXML - Query Work Order and its associated Notes files

Hello ,

Thank you for providing an example.

The main goal of that query is to extract the resource signature and the customer signature from the notes entity which is related to a WO.
In addition to the above, in the report I want to display two different images:

one image will display the resource signature content.

The other image will display the customer signature content.

I have updated my XML query as you mentioned but I am still struggling on how can I specify to display the image of either the resource or customer signature?

Before, I was simply calling a sub-report and passing some parameters to indicate what it should display the image as below: (The documentbody value of the image was included in the sub-report as an expression)

Currently, I removed sub-reports and I am using the image control directly in the report.

To summarize, in the report we need two image controls and each will display either the customer signature or the resource signature from the notes entity that is related to a WO.

The expected outcome that we should have is the below:

I hope I have provided all the details needed. Please let me know if you need any further information.

Would highly appreciate it if you could provide an example based on the details I gave.

Thank you!
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report
RE: FetchXML - Query Work Order and its associated Notes files

Hi EBMRay:

For the point1, you could add enableprefiltering="1" property on the entity. Please try the fetch xml:

Result:

When you click "Edit Filter" button, you would find the filter like this:

For the point2, I'm not sure what you want to do. Could you provide more details?