SSRS User Story-7: Create a SSRS report group by Case, retrieve all work orders and make work orders as sub group, retrieve all bookable resource bookings based on work order. If booking is completed then show the row color as green.

Entities:

·         Cases

·         Wok Order

·         Bookable Resource Booking

Fetch xml for retrieving cases which does not contains Work Orders (or) choose contain work Orders data:

If you choose contains data then in fetch xml in link entity, link type shown as Inner

If you choose does not contain data then in fetch xml in link entity, link type shown as Outer.

In this scenario we need all work Orders that’s why choose link type as Outer

In this below fetch xml code need to remove filter condition

Here also need to remove filter condition

 

Final Fetch xml:

Here am writing one link entity data into another link entity

Need to pass a from as attribute from link entity

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">

            <entity name="incident">

                        <attribute name="title" />

                        <attribute name="ticketnumber" />

                        <attribute name="incidentid" />

                        <attribute name="caseorigincode" />

                        <order attribute="title" descending="false" />

            <link-entity name="msdyn_workorder" from="msdyn_servicerequest" to="incidentid" link-type="outer" alias="ac" >

                                    <attribute name="msdyn_name" />

                                        <attribute name="msdyn_serviceaccount" />

                                    <attribute name="msdyn_workorderid" />

                                    <attribute name="msdyn_servicerequest" />

            <link-entity name="bookableresourcebooking" from="msdyn_workorder" to="msdyn_workorderid" link-type="outer" alias="ad">

                                                <attribute name="msdyn_workorder" />

                                                <attribute name="starttime" />

                                                <attribute name="resource" />

                                                <attribute name="bookingstatus" />

                                                <attribute name="bookableresourcebookingid" />

                                    </link-entity>

                        </link-entity>

            </entity>

</fetch>

Design:

Background colour updating if bookable resource booking status as Completed:

Right click on report and choose properties then select the columns (for which column you need to apply background colour) then write Expression

Here am applying Expression for 2 columns

Expression for background colour:

Output in Visual studio:

Output in CRM:

Open any case record click on Run Report and choose SSRS Report