How to Filter fields in ssrs custom reports in mscrm 2011

Toate mesajele

• 29 februarie 2012 13:32

   

   
  

  0

  You can create a subreport:-

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

  ---

  Regards Faisal

  • Mesaj
  • Citare

   
• 1 martie 2012 01: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

  • Propus ca răspuns de Kelly Hoang 1 martie 2012 07:43
  • Marcat ca răspuns de Shetty KP 2 martie 2012 07:01
  •  
  • Mesaj
  • Citare

   
• 1 martie 2012 03:37

   

   
  

  0
  Thanks for your reply..

  ---

  Krishn Prasad Shetty

  • Mesaj
  • Citare

   
• 1 martie 2012 03:39

   

   
  

  0
  Is it working for you?

  ---

  Regards, Kyaw Kyaw Tun

  • Mesaj
  • Citare

   
• 1 martie 2012 06: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

  • Mesaj
  • Citare

   
• 1 martie 2012 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

  • Mesaj
  • Citare

   
• 1 martie 2012 22: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

  • Mesaj
  • Citare

   
• 2 martie 2012 03: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

  • Mesaj
  • Citare

   
• 2 martie 2012 04: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

  
  
  

  • Editat de Kyaw Kyaw Tun 2 martie 2012 04:27
  •  
  • Mesaj
  • Citare

   
• 2 martie 2012 05: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

  • Mesaj
  • Citare

   
• 2 martie 2012 05: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

  • Mesaj
  • Citare

   
• 2 martie 2012 06: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

  • Mesaj
  • Citare

   
• 2 martie 2012 07: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

  • Mesaj
  • Citare

   
• 4 martie 2012 22:13

   

   
  

  0
  Cheers!

  ---

  Regards, Kyaw Kyaw Tun

  • Mesaj
  • Citare