How to Filter fields in ssrs custom reports in mscrm 2011

모든 응답

• 2012년 2월 29일 수요일 오후 1:32

   

   
  

  0

  You can create a subreport:-

  http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/e43934cd-e7c6-4bda-921e-915cc4d04d7d/

  ---

  Regards Faisal

  • 응답
  • 인용

   
• 2012년 3월 1일 목요일 오전 1:41

   

   
  

  1

  For your reference,

  1. Create a DataSet called DS_Customer as following.

      Select new_CustomerName, new_CustomerId from filterednew_customer

  2. Create a Parameter called CustomerId As following.
      Available Values - Get values from a query.
      Dataset: Ds_Customer
      Value field: new_CustomerId
      Label field: new_CustomerName

  3. Create a DataSet called DS_Project as following.

     Select new_ProjectName, new_ProjectId from filterednew_project AS P WHERE
     P.new_CustomerId = @CustomerId

  4. Create a second parameter called ProjectId As following.
      Available Values - Get values from a query.
      Dataset: Ds_Project
      Value field: new_ProjectId
      Label field: new_ProjectName

   5. Make sure paramerter CustomerId must be on top.

  ---

  Regards, Kyaw Kyaw Tun

  • 답변으로 제안됨 Kelly Hoang 2012년 3월 1일 목요일 오전 7:43
  • 답변으로 표시됨 Shetty KP 2012년 3월 2일 금요일 오전 7:01
  •  
  • 응답
  • 인용

   
• 2012년 3월 1일 목요일 오전 3:37

   

   
  

  0
  Thanks for your reply..

  ---

  Krishn Prasad Shetty

  • 응답
  • 인용

   
• 2012년 3월 1일 목요일 오전 3:39

   

   
  

  0
  Is it working for you?

  ---

  Regards, Kyaw Kyaw Tun

  • 응답
  • 인용

   
• 2012년 3월 1일 목요일 오전 6:48

   

   
  

  0

  Hi Faisal ,

  This is my reports screen shot.

  Here I Taken two parameters Customer name and Project Name. I need to filter such a way that ,when I select  Customer Name from 'India Info Line'  then in Project name field should show the project Names  for the customer 'India Info Line'

   After That I will Select the Project Name and view the report. But Here  now it shows all the projects that comes in all customers. How to filter it based on first parameter(Customer Name) to display  project Names only for  selected customer in second parameter.

  ---

  Krishn Prasad Shetty

  • 응답
  • 인용

   
• 2012년 3월 1일 목요일 오전 10:29

   

   
  

  0

  Hi.. Tried same thing how you specified but i am getting only "new_projecttopic" from query.

  This is the step  what i did.... tell me if I did any thing wrong...

  
  

  1)Created a data set (DataSet2) and query is  ,

  select  distinct Filterednew_projectstatus.new_potentialcustomerid ,
           Filterednew_projectstatus.new_potentialcustomeridname
            from Filterednew_projectstatus

  IT Displays customer name and customer-id -- fine.

  
  

  2)Created a Parameter (ReportParameter1)  as following ,
  

      Data set: DataSet1
      Value field: new_potentailcustomerid
      Label field:new_potentailcustomeridname

  
  

  3) Created second data set (DataSet3) with query is  ,

   select  new_projecttopic from Filterednew_projectstatus
           where  new_potentialcustomeridname IN ('@ReportParameter1')

  Here I am getting the  output is  'new_projecttopic'  it wont gives project name .Why?

  4)Created a second  Parameter (ReportParameter2)  as following ,

      Data set: DataSet2
      Value field: new_projecttopic
      Label field:new_projecttopic.

  Doing all these and while checking preview i am getting like this.... ?

   

    
  

  
  

  
  

  ---

  Krishn Prasad Shetty

  • 응답
  • 인용

   
• 2012년 3월 1일 목요일 오후 10:14

   

   
  

  0

  3) Created second data set (DataSet3) with query is  ,

   select  new_projecttopic from Filterednew_projectstatus
           where  new_potentialcustomeridname IN ('@ReportParameter1')

  should be

   select  new_projecttopic from Filterednew_projectstatus
           where  new_potentialcustomerid IN (@ReportParameter1)

  ---

  Regards, Kyaw Kyaw Tun

  • 응답
  • 인용

   
• 2012년 3월 2일 금요일 오전 3:43

   

   
  

  0

  Hi.. Thanks for your reply...

  
  

  I tried this query

    select  new_projecttopic from Filterednew_projectstatus
           where  new_potentialcustomerid IN (@ReportParameter1)

  but  i am getting  error like this ,

  "Conversion failed when converting from a character string to uniqueidentifier"
  

  ---

  Krishn Prasad Shetty

  • 응답
  • 인용

   
• 2012년 3월 2일 금요일 오전 4:25

   

   
  

  0

  Change the ReportParameter1 Value field as follow.

  Created a Parameter (ReportParameter1)  as following ,
  

      Value field: new_potentailcustomeridname
      Label field:new_potentailcustomeridname

  Created second data set (DataSet3) with query is  ,

   select  new_projecttopic from Filterednew_projectstatus
           where  new_potentialcustomeridname IN (@ReportParameter1)

  I hope this will solve your problem.

  ---

  Regards, Kyaw Kyaw Tun

  
  
  

  • 편집됨 Kyaw Kyaw Tun 2012년 3월 2일 금요일 오전 4:27
  •  
  • 응답
  • 인용

   
• 2012년 3월 2일 금요일 오전 5:06

   

   
  

  0

     Yes  I tried this one... see this is my Parameter1 and the query of dataset3 is

   select  new_projecttopic from Filterednew_projectstatus
           where  new_potentialcustomeridname IN ('@ReportParameter1')

   If  I execute this  I am getting this ,

  

  

  ---

  Krishn Prasad Shetty

  • 응답
  • 인용

   
• 2012년 3월 2일 금요일 오전 5:33

   

   
  

  0

   select  new_projecttopic from Filterednew_projectstatus
           where  new_potentialcustomeridname IN ('@ReportParameter1')

  without single quote

           where  new_potentialcustomeridname IN (@ReportParameter1)

  ---

  Regards, Kyaw Kyaw Tun

  • 응답
  • 인용

   
• 2012년 3월 2일 금요일 오전 6:28

   

   
  

  0

  Hi ... Kyaw Kyaw Tun

  I tried all these... it gives error ... that is -- (Msg 137, Level 15, State 2, Line 3
  Must declare the scalar variable "@ReportParameter1".)

  ---

  Krishn Prasad Shetty

  • 응답
  • 인용

   
• 2012년 3월 2일 금요일 오전 7:03

   

   
  

  0

    Hi ,

  Thanks for your reply , i got the solution ... Finally report is working fine...

  Thanks a lot  Kyaw Kyaw Tun

  ---

  Krishn Prasad Shetty

  • 응답
  • 인용

   
• 2012년 3월 4일 일요일 오후 10:13

   

   
  

  0
  Cheers!

  ---

  Regards, Kyaw Kyaw Tun

  • 응답
  • 인용