locked
Unable to create a drill through or drill down report RRS feed

  • Question

  •  

    Hello I am new to 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: https://drive.google.com/file/d/0BzjF_kUloVtkSUk0YlZDbFVYR3c/edit?usp=sharing)

    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

    Thursday, February 20, 2014 12:18 PM

Answers

  • Hi Silent_Tracker,

    Based on your description, you have add a pie chart in your report. You want add a drilllthrough in the pie chart, when you click the specific slice get the correspoding data in the subreport. Please refer to the following steps:

    Subreport:

    1. Add a dataset (DataSet1) in the report with the corresponding fields. Add another dataset (DataSet2) with RiskCategory, MaintenanceCompliance two fields.
    2. Add two parameters (RiskCategory, MaintenanceCompliance) in the report, get these Available value from DataSet2 fields.
    3. Add two filters of DataSet1. Please refer to the following values:
      Expression: [RiskCategory]
      Operator: =
      Value: [@RiskCategory]

    Main report:

    1. Right click the pie chart, select Series properties.
    2. Click Action in the left pane, select “Go to report” option.
    3. Add the subreport below “Specify a report”.
    4. Click Add icon to add parameter to run the subreport. Please refer to the following values:
      Name: RiskCategory (Note: This is parameter name in the subreport.)      Value: RiskCategory
      Name: MaintenanceCompliance         Valeu: MaintenanceCompliance

    Reference: Adding Parameters to Pass to a Drillthrough Report

    If you have any questions, please feel free to let me know.

    Regards,

    Alisa Tang


    Alisa Tang
    TechNet Community Support

    • Marked as answer by Alisa Tang Wednesday, March 5, 2014 5:04 AM
    Friday, February 21, 2014 7:50 AM
  • Hi Silent_Tracker,

    In SSRS, when we add a filter to a dataset, all report parts or data regions use only data that matches the filter conditions. In your case, we need not add filter for dataset2 in subreport. The step: Add the subreport below “Specify a report” is to add the subreport name we create before. We can select the subreport in the drop down list.

    For this error message, there is an similar issue, you can refer to it.
    http://social.microsoft.com/Forums/en-US/7f3ff562-13ab-432a-a479-a07e23494887/an-error-occurred-while-trying-to-add-the-report-to-microsoft-dynamics-crm?forum=crm
    Since the issue is relate to Microsoft Dynamics CRM, I recommend you post the issue to the forum below:
    http://social.microsoft.com/Forums/en-US/home?forum=crm
    It is appropriate and more experts will assist you.

    If you have any questions about Reporting Services, please feel free to let me know.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    • Marked as answer by Alisa Tang Wednesday, March 5, 2014 5:04 AM
    Monday, February 24, 2014 3:21 AM
  • Hi Silent_Tracker,

    Thank you for Your detail information. Please refer to the steps below to modify your design:

    1. Right click pie chart, select Series Properties.
    2. Click Action in the left pane.
    3. Click the Expression icon before RiskCategory value with expression: =Fields!RiskCategory.Value
    4. Use the same method to modify the MaintenanceCompliance parameter.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    • Marked as answer by Alisa Tang Wednesday, March 5, 2014 5:04 AM
    Thursday, February 27, 2014 1:24 AM

All replies

  • Hi Silent_Tracker,

    Based on your description, you have add a pie chart in your report. You want add a drilllthrough in the pie chart, when you click the specific slice get the correspoding data in the subreport. Please refer to the following steps:

    Subreport:

    1. Add a dataset (DataSet1) in the report with the corresponding fields. Add another dataset (DataSet2) with RiskCategory, MaintenanceCompliance two fields.
    2. Add two parameters (RiskCategory, MaintenanceCompliance) in the report, get these Available value from DataSet2 fields.
    3. Add two filters of DataSet1. Please refer to the following values:
      Expression: [RiskCategory]
      Operator: =
      Value: [@RiskCategory]

    Main report:

    1. Right click the pie chart, select Series properties.
    2. Click Action in the left pane, select “Go to report” option.
    3. Add the subreport below “Specify a report”.
    4. Click Add icon to add parameter to run the subreport. Please refer to the following values:
      Name: RiskCategory (Note: This is parameter name in the subreport.)      Value: RiskCategory
      Name: MaintenanceCompliance         Valeu: MaintenanceCompliance

    Reference: Adding Parameters to Pass to a Drillthrough Report

    If you have any questions, please feel free to let me know.

    Regards,

    Alisa Tang


    Alisa Tang
    TechNet Community Support

    • Marked as answer by Alisa Tang Wednesday, March 5, 2014 5:04 AM
    Friday, February 21, 2014 7:50 AM
  • Hello Alisa

    Thank you for the response. I tried doing the following, and I am pretty sure I must be doing something wrong because of my inexperience with SQL tools.

    But I get an error while I try to upload it onto the server. I have uploaded my files here

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

    Is it possible to check these. The files are (.rdl file, error log text file, screen shot of error when uploading to server)

    Also, in your explanation you mentioned

      • Add a dataset (DataSet1) in the report with the corresponding fields (when I try to add another data set(DS1) with the values shown in DSMain (my main report), it does not allow populate the query fields of the main report (DSMain). So I have to manually add all query field + the two calculated fields (Risk_Category & Maintenance_Compliance). Add another dataset (DataSet2) with RiskCategory, MaintenanceCompliance two fields (I added another Dataset and named it as DS2, and copied the same Calculated fields from DSMain (main report) for Risk_Category & Maintenance_Compliance
      • Add two parameters (RiskCategory, MaintenanceCompliance) in the report, get these Available value from DataSet2 fields.( two parameters appears new

    RiskCategory --> Dataset-DS2, Value Field- Risk_Category, Label Field: Risk_Category

    MaintenanceCompliance --> Dataset-DS2, Value Field- Maintenance_Compliance, Label Field: Maintenance_Compliance

    3. Add two filters of DataSet1. Please refer to the following values:
    Expression: [RiskCategory]
    Operator: =
    Value: [@RiskCategory](I am not sure I have clearly understood this part. As my values in Dataset 1 (DS1) are the same as the main report (DSMain), the query + calculated fields are the same. Hence my Dataset 1 (DS1) will also have a Calculated field as Risk_Category and Maintenance_Compliance. And do I have to add these values as filters in DataSet2 (DS2) ? In which am actually manually typing Expression: [RiskCategory]
    Operator: = 

    Value: [@RiskCategory] 

    &

    Expression: [MaintenanceCompliance] 

    Operator: = 

    Value: [@MaintenanceCompliance]

    Main Report

    3. Add the subreport below “Specify a report”. (I had to give a random name as 'DrillThrough' in this field. Am I doing something wrong)

  • 4. Click Add icon to add parameter to run the subreport. Please refer to the following values:
    Name: RiskCategory (Note: This is parameter name in the subreport.)      Value: RiskCategory
    Name: MaintenanceCompliance         Valeu: MaintenanceCompliance
  • (I had to manually type the Name & Value fields, as they did not appear in the drop down list when I click on add)

    I am sure by now you must have realised how amateur I am with this tool

    Please let me know

    Thank you once again for being patient

    Silent_Tracker



Saturday, February 22, 2014 3:47 PM
  • Hi Silent_Tracker,

    In SSRS, when we add a filter to a dataset, all report parts or data regions use only data that matches the filter conditions. In your case, we need not add filter for dataset2 in subreport. The step: Add the subreport below “Specify a report” is to add the subreport name we create before. We can select the subreport in the drop down list.

    For this error message, there is an similar issue, you can refer to it.
    http://social.microsoft.com/Forums/en-US/7f3ff562-13ab-432a-a479-a07e23494887/an-error-occurred-while-trying-to-add-the-report-to-microsoft-dynamics-crm?forum=crm
    Since the issue is relate to Microsoft Dynamics CRM, I recommend you post the issue to the forum below:
    http://social.microsoft.com/Forums/en-US/home?forum=crm
    It is appropriate and more experts will assist you.

    If you have any questions about Reporting Services, please feel free to let me know.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    • Marked as answer by Alisa Tang Wednesday, March 5, 2014 5:04 AM
    Monday, February 24, 2014 3:21 AM
  • Hello Alisa

    Thank you for the quick reply. Sorry for confusing the matter more, but even though I use CRM the error message is mainly because I have not configured the .rdl correctly. I was wondering if you would advise if I followed the right steps as stated in my previous post. The .rdl file is uploaded in the link below. I think maybe I haven't followed your instructions correctly.

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

    Please let me know if possible

    Regards

    Silent_Tracker


    Tuesday, February 25, 2014 10:50 PM
  • Hi Silent_Tracker,

    Thank you for Your detail information. Please refer to the steps below to modify your design:

    1. Right click pie chart, select Series Properties.
    2. Click Action in the left pane.
    3. Click the Expression icon before RiskCategory value with expression: =Fields!RiskCategory.Value
    4. Use the same method to modify the MaintenanceCompliance parameter.

    Regards,
    Alisa Tang


    Alisa Tang
    TechNet Community Support

    • Marked as answer by Alisa Tang Wednesday, March 5, 2014 5:04 AM
    Thursday, February 27, 2014 1:24 AM