Microsoft Dynamics CRM (Archived)

Drill Through reports

(0) Share

Report

Posted on by luckyk

420

Dear All

I am new to CRM and report builder (or any SQL related tools) and I am still learning to do a many things.

I have managed to design a pie-chart report that shows Number of Assets, and when they were maintained. The fields I have for the table seven columns.

Asset ID #

Accepted On

Last Serviced On

Overall Risk Score (Product Category)

Minimum Service Visits Per Annum (Product Category)

Risk Category

Maintenance Compliant

This report was designed using SQL Report Builder 2008 R2 (.rdl file)., which was eventually uploaded into our CRM 2011 database to run a scehduled report every hour. Now my problem is I would like to make this a drill through report such that whenever I click on each slice it should give me the results and table for that specific slice.

I believe I need to create seperate reports for each slice and try and link it as a url Action. So, I tried to design another report with only the Slice results and added the url link to my original .rdl file. The steps I took were as follows

1. Create a secondary report i.e secondary .rdl files, for each slice and upload it to the CRM server

2. Run the secondary report and hit F11 and copy the url for the secondary report

3. I then open my original .rdl file. and select one of the data label on the Pi-Chart .... right click it and select Series label properties.

4. Under properties I click on Action and under Change Action Options select 'Go to url' and paste the link for one slice. Save it

5. Unfortunately, when I select one Data label on the pie-chart, it highlights or select all data labels. So when I right and go to properties to add Actions, it will apply the same action for all the other data labels.

I am not sure how can I give, seperate action for each Data label. Meaning for each Legend 'Orange-Compliant', 'Orange-No Proof', 'Green-Compliant', 'Green- No Proof', there would be four seperate url links respectively.

Not sure if I have made myself clear enough for this

*This post is locked for comments

I have the same question (0)

All responses (39)

Answers (0)

Sort by

luckyk 420 on at

Like (0)

Report
Copy link

Link copied!

Hello I am new to CRM and report builder (or any SQL related tools) and I am still learning to do a many things.

Background: I have managed to design a pie-chart report that shows Number of Assets, and when they were maintained. The fields I have for the table seven columns

Asset ID (e. 123456), Accepted On Date(dd/mm/yyyy), Last Serviced on Date(dd/mm/yyyy), Min. Service interval per year (e.g 1,2,3,4) Overall Risk Score (between 0-16), Risk Category (e.g Green or Orange), maintenance Compliance (e.g Compliant or No Proof)

I get an extract in .rdl format from our Asset Database (CRM) which includes Asset ID, Accepted On Dates, Last Serviced on Date, Min Service Interval per year, Overall Risk Score

I add calculated field of Risk Category as Orange or Green, where an overall risk score > 8 will make the Asset ID fall in the Orange Category and any risk < 8 will be Green Category

The second calculated field is the Maintenance Compliance, where I have added formula when certain conditions between Accepted On Date(dd/mm/yyyy), Last Serviced on Date(dd/mm/yyyy) and Min. Service interval per year, are met. When they are met, it is "Compliant", when not met it is "No Proof".

The pie chart has

Values as Count(Risk Category)

category Groups (Risk category) + (Maintenance Compliance)

Probelm: I am trying to design a drill down or a drill through report where if I click on the slice, the table displayed below will only contain values (or rows) corresponding to that region. So, if I click on Green - Compliant, then a table should appear which shows only asset values which have Risk category value Green and Maintenance Compliance Value as Compliant

Please let me know if anyone can help (Link Copy Paste in browser: drive.google.com/.../edit)

Note: I am doing this report offline, as the server connection does not work. So i usually edit the .rdl file and then upload it to the server to check if it runs

Was this reply helpful? Yes No
Suggested answer

Matt_K 400 on at

Like (0)

Report
Copy link

Link copied!

Can you include Series labels for the pie sections? You can specify the 'Go To Report' action, and should also be able to pass the series label field value as a parameter.

Was this reply helpful? Yes No
luckyk 420 on at

Like (0)

Report
Copy link

Link copied!

It does not allow me to do that. Right click series properties and then go to report allows only one report to be linked. As you can see from the slices for the pie chart above I need to pass 4 separate reports for four slices

Was this reply helpful? Yes No
Suggested answer

Matt_K 400 on at

Like (0)

Report
Copy link

Link copied!

It wouldn't be under the Series Properties context menu, rather the Series Label Properties. One report would be all you need. To use the graph you provided above as an example, you could click on the number 27542 and it would link to another report and pass 'Green - Compliant' as a filter parameter (you'd need to specify that as a Hidden parameter in the target report, of course). Here's a screenshot of a report I built that has similar functionality:

Was this reply helpful? Yes No
luckyk 420 on at

Like (0)

Report
Copy link

Link copied!

Thanks Matt. This is what I have been trying to do but I am sure I am doing it wrong. Considering the example you just gave above. Say when I click on the Yellow slice (shown by 89) and follow the instructions of then going to Series Label Properties and then Go to Report and type a random name for the SubReport and then click on Add, what do I need to type in there? It appears blank in the drop-down menu.

Note1: I create this report Offline using Report Builder 2008 R2. So the only thing I am able to specify if the link or the url for the sub-report I wish to view.

Note 2: The link or url for the sub-report is got by building a report in Report Builder 2008 R2, and then uploading it to CRM 2011. This is where I can actually see if the report runs (I am unable to run the report in report builder as there is no connection to any server). Once this report runs, I hit F11 on the keyboard which gives me the link for the report address bar. This is the link I use to paste it into the GoTo Report option.

Now, coming back to the steps I followed. So far I have been using Go to Url and was pasting the link for the sub-report. But whenever, I click on Series Label Properties by selecting the appropriate slice, i can paste the url by using Action-GotoUrl. But now if I need to paste the seperate url for the next slice, iit does not allow me to do that as the previous link already appears in there.

I have uploaded my screen shot and the .rdl file to this link

https://drive.google.com/folderview?id=0BzjF_kUloVtkQmdENFI5TkJoRzA&usp=sharing

Please let me know what I should be doing.

Was this reply helpful? Yes No
Suggested answer

Matt_K 400 on at

Like (0)

Report
Copy link

Link copied!

You might try downloading a copy of your subreport and linking your parent report to that offline .rdl file. Just be sure both .rdl files are stored in the same folder. Then go through the same steps that got you to the screen you just shared, but instead of selecting 'Go to URL' try 'Go to report'.

When I go to Series Label Properties/Action/Go to report, I don't have to type in a report name; it shows a dropdown list of all .rdl files that exist in the same folder as the report I'm currently building. I then click the Add button to specify input parameters which the subreport is designed to accept. When both reports are finished or ready for testing, I upload the parent first, and then when I upload the subreport it automatically detects the parent so no additional linking is necessary.

Good luck!

Was this reply helpful? Yes No
luckyk 420 on at

Like (0)

Report
Copy link

Link copied!

Just to add to my comments above. Say, as you mentioned, when clicking on 27542, in my original screen shot for the first post, I can link the other report for Green_Compliant with hidden parameters (Green & Compliant)

So what will I have to do for the remaining slices shown by the numbers 2805 , 512 and 14543 corresponding to report that have hidden parameters as (2805-Orange & No Proof), (512-Orange & Compliant), 14543 (Green & No Proof). How will I design the drill through action for these?

Was this reply helpful? Yes No
Suggested answer

Matt_K 400 on at

Like (0)

Report
Copy link

Link copied!

I think I see....you would need to specify input parameters in your subreport which correspond to the grouping fields in your main report. That is, your subreport would need to filter by Risk Category and Maintenance Compliant (or you could concatenate the two into a single parameter).

If you haven't already, you'll also need to add a clause to your data query for the subreport. This will all need to be done before you create that link from the parent report to the sub.

If I'm misunderstanding, please let me know.

Was this reply helpful? Yes No
luckyk 420 on at

Like (0)

Report
Copy link

Link copied!

Thanks for this. I was thinking its going to be something like that, but the problem is because I am not familiar with either CRM or report builder, I am not sure how I can do this.

if you check the .rdl link I pasted in my previous post, this is the my main report.

Now for the sub-report I have created an exact copy of my original report, but added filters of Green and Compliant.

So eventually I have created four seperate reports, as follows

Sub-report 1 = Filter (Risk Category= Green & maintenance Compliance = Compliant)

Sub-report 2 = Filter (Risk Category= Green & maintenance Compliance = No Proof)

Sub-report 1 = Filter (Risk Category= Orange & maintenance Compliance = Compliant)

Sub-report 1 = Filter (Risk Category= Orange & maintenance Compliance = No Proof).

Again, these are all .rdl extensions. The next steps I took was to upload all these reports into our CRM database. So basically, I have One Main Report + 4 sub reports, on the server. And all the sub-reports generate a unique report ID each and/or a unique url respectively. I can either use this Report ID or the url anywhere on the main report to pass the value.

The sub-reports generate the filtered report they have been designed for ( Green & Compliant will generate all value under '27542' slice and the table below will only show those values)

Now coming back to your explanation. How would I go about adding these sub-reports to the main report. I am not sure what I should add into the Specify a report and what should I enter into the parameters below.

I can see you have added CRM_owner for the name, but the Value has to be selected from the drop down menu. And if you see in the rdl file I have provided, I am not sure what should I select.

I maybe be asking too much but is it possible to give me one step by step example?

Was this reply helpful? Yes No
Suggested answer

Matt_K 400 on at

Like (0)

Report
Copy link

Link copied!

Start with your main (pie chart) report. Aside from your series data, you should have at least one grouping.

I’d recommend specifying a Series Group (bottom) at the very least. A Category Group alone may be sufficient, but you shouldn’t need both. As an example, when run, my report shows me a currency total with each slice of the pie corresponding to a User.

Now, switch to your Subreport. I want this report to show me a list of currency records, based on the slice of the pie chart I click on. For instance, if I click on the light-blue section pictured above with the value 79, it should show me all the currency records for ownerid A1. You’ll start by adding a clause to your data query to accept an input parameter (WHERE clause for SQL query, filter/condition for FetchXML) as shown. For best results, begin the parameter name with “@CRM_”.

You’ll also need to set up the parameter in your report itself. If you included it in the query first, it may have already been created for you in the report but you should still verify the options. Otherwise, just right-click ‘Parameters’ and hit New.

*NOTE: You do not need to add a Filter to the report control (table/matrix, etc.).

I used the data type Text because the parameter is a GUID, you may need to use a different type based on your requirements.

Now we go back to the pie chart report to set up the link. Series Label Properties/Actions/Go to report, select the report from the dropdown. Then click Add to specify a parameter. If you set up the parameter properly in your subreport, it should be available in the ‘Name’ dropdown in the box below. Then you should set the Value as the same field you used for your pie chart grouping.

Save and test. If you need to upload the reports to test, I recommend doing the main report first, then subreport.

I'll need a bit more time if you want screenshots, if so just let me know.

Was this reply helpful? Yes No