Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Drill Down SSRS based on Chart

Answered Drill Down SSRS based on Chart

  • Monday, February 04, 2013 9:20 AM
     
     
    Sign In to Vote
    0

    Hi All,

    I have a Report with One Graph (Pie Chart) This contains three section, OverDue, InLine and AboutToDue tasks, below this Pie Chart i have a tablix which has the data related tasks like assigned user, date etc.

    Now i want to populate the data based on the click i click in Graph.

    Ex, if i click InLine, it should show me all inline task details in Tablix below.

    I tried using go to report option, but how can i redirect to three reports, i can re-direct it only to one report :(

    Something Like below. When i click inline it should give me only inline values.

    Please help, thank you.

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com


     

All Replies

  • Monday, February 04, 2013 2:02 PM
     
     
    Sign In to Vote
    0

    Hello,

    See this link might be helpfull to you

    http://www.bi-rootdata.com/2012/10/drilldown-dashboard-in-ssrs.html


    blog:My Blog/

    Hope this will help you !!!
    Sanjeewan

     
  • Tuesday, February 05, 2013 7:52 AM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Manjunath,

    You needn’t to redirect to three reports, you can drill-through to the same report to pass a parameters into it, and then set the row visibility base on the parameter’s value. For more information about how to drill-through to the same report and set the report items visibility, please see:
    http://www.codeproject.com/Articles/270924/Master-Details-Report-in-SSRS-2008

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    Charlie Liao
    TechNet Community Support

     
  • Tuesday, February 05, 2013 9:57 AM
     
     
    Sign In to Vote
    0

    Hi Charlie and Sanjeevan, 

    This approach should have given me. I dont know what wrong i am doing.

    1. I have a dataset testMyPie which gives me task_Number,Assigned_user,DateDue,SomeID,Status

    select task_Number,Assigned_user,DateDue,SomeID,Status from mytable

    This i have put in PieChart where in Categroup is my status and category groupname is chart_categorygroup. Now since i want the percentage of each status in graph, i have put the following expression in my values field on pieChart

    =Count(Fields!Status.Value,"chart_categorygroup") /Count(Fields!Status.Value, "testMyPie")

    My PieChart is showing correct Values in terms of percentage.

    On Action Tab i set go to MyReport the same report and in parameter section i have given as 

    Name: taskids value [task_number]

    2. I have another dataset mygroupDataset which gives me task_Number,Assigned_user,DateDue,SomeID,Status

    select task_Number,Assigned_user,DateDue,SomeID,Status from mytable where task_Number in (@taskids)

    This created a parameter @taskids, i set its properties as hidden and default values as expression =-1

    3. I Created a Tablix and gave dataset name as mygroupDataset

    Now when i preview, my graph shows correct percentage, but no data in Tablix initially. 

    Now when i click on Inline Part of chart the report gets refreshed but again i dont see any data in my tablix.

    I dont know what i am missing here.

    Please help

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

     
  • Tuesday, February 05, 2013 10:26 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hi Manjunath,

    One Status can have multiple task_number in you dataset,  so you should pass the "Status" as the parameter value to the dataset.  In your scenario, when you click on Inline Part of chart the report gets refreshed but again you dont see any data in my tablix, this issue was caused by that the parameter value was the sum of the task_number. Please change the parameter to the Status.

    Hope this helps.

    Regards, 

    Charlie Liao
    TechNet Community Support

     
  • Tuesday, February 05, 2013 11:49 AM
     
     
    Sign In to Vote
    0

    Hi Charlie,

    Status column is a computed column, It does not reside in anytable. I am computing it. But in graph for each status i am having task_number also. So if i pick the Inline part of chart, it should put all the task_number which are in that section put in a paramter which allows multiple values and then pass this parameter to tablix. 

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com

     
  • Tuesday, February 05, 2013 2:01 PM
     
     
    Sign In to Vote
    0

    Hi All,

    Thank you for all your support. I was able to Do it.

    I added a parameter in my select query, where status in (@status) something like this.

    Then i added a hidden parameter with Allow MultiValues and Loaded Default Data with my Select query without any status.

    Then in Action Tab of series properties of chart in go to report i added this parameter.

    Now when firsttime my report load i get all data with all status in my tablix because i have given default value. Then when i click on particular status, i get the data only for that status in my tablix.  I also did some color coding to reflect. Thank you all.

    Thanks a Ton again. 

    Thanks & Regards,
    Manjunath C Bhat,
    http://manjunathcbhat.blogspot.com
    http://manjunathcbhat.wordpress.com