none
Error in CRM report using SSRS

    Question

  • Hi,

    I m creating a report for crm using SSRS.When it is select query it works fine,,, but when include paramater in it its not wrking.. below is my query.. plz help me to sort this issue,,

    declare @SQL nvarchar(max)
     set @SQL = 'select quote0.quotenumber as ''quotenumber'', quote0.name as ''name'', quote0.customeridname as ''customeridname'', quote0.customerid as ''customerid'', quote0.crm_moneyformatstring as ''crm_moneyformatstring'', quote0.new_turbototalamount_base as ''new_turbototalamount_base'', quote0.quoteid as ''quoteid'' from (' + @CRM_FilteredQuote + ')
     as quote0
    where quote0.CreatedOn between @Datefrom and @Dateto and quote0.customeridname =@Customer and quote0.OwnerIdName=@Sales and quote0.New_Order_NAV=0
    order by quote0.quotenumber asc'
     exec(@SQL)

     

    without where condition query wrks .. but when i add "where condition" it is not wrking..

    i dont know where i m wrong??

    Thursday, April 15, 2010 3:02 AM

Answers

  • Hi Kalai,

    Since you adding paramete to string you need use + operator make SQL string correctly .. following is the correction you need to on where part

    where quote0.CreatedOn between '  + @Datefrom +  ' and  ' + @Dateto + '  and quote0.customeridname= ' +  @Customer + ' andquote0.OwnerIdName= ' + @Sales+  ' and quote0.New_Order_NAV=0
    order by quote0.quotenumber asc'

    try chaning as mentioned above and see if it works..

    Thursday, April 15, 2010 8:06 AM

All replies

  • Hi Kalai,

    Since you adding paramete to string you need use + operator make SQL string correctly .. following is the correction you need to on where part

    where quote0.CreatedOn between '  + @Datefrom +  ' and  ' + @Dateto + '  and quote0.customeridname= ' +  @Customer + ' andquote0.OwnerIdName= ' + @Sales+  ' and quote0.New_Order_NAV=0
    order by quote0.quotenumber asc'

    try chaning as mentioned above and see if it works..

    Thursday, April 15, 2010 8:06 AM
  • I have tryed MayankP's surgestion, and it works.
    Henrik Jensen Instructor / Consultant MCP, MCAD, MCSD, MCTS, MCPD, MCITP, MBSS, MBSP, MCT  www.crmblog.dk and  mscrm.easyconsult.dk (Danish only)
    • Proposed as answer by Carla J Sunday, April 18, 2010 11:11 PM
    Thursday, April 15, 2010 12:16 PM