Accedi
 
HomeLibraryRisorseFormazioneDownloadSupportoCommunityForumBlog
Risorse per professionisti IT >
> 
> 
Help in making a query driven by expression

Con risposta Help in making a query driven by expression

  • venerdì 2 marzo 2012 06:39
     
     
    Registrati per votare
    0

    I wrote the following query and it works very well

    declare @start date, @end date
    select @start = getdate()
    select @end = dateadd(day, -30, @start)


    ;with datesequence (date) as(
    select @start as Date
    union all
    select dateadd(day, -1, date)
    from datesequence
    where date > @end
    )


    select ProviderName, date, count(id) as ExceptionCount from InfraErrorLog
    inner join datesequence on date = dateadd(day, 0, datediff(day, 0, timecreated))
    group by ProviderName, date
    order by date desc

    but I need to dynamically change a few things in this and I try to convert it into an expression like

    ="

    declare @start date, @end date
    select @start = getdate()
    select @end = dateadd(day, -30, @start)


    ;with datesequence (date) as(
    select @start as Date
    union all
    select dateadd(day, -1, date)
    from datesequence
    where date > @end
    )


    select ProviderName, date, count(id) as ExceptionCount from InfraErrorLog
    inner join datesequence on date = dateadd(day, 0, datediff(day, 0, timecreated))
    group by ProviderName, date
    order by date desc"

    But it immediately gets and exception 

    Incorrect syntax near '@start'. Must declare the scalar variable "@start". Must declare the scalar variable "@start". Must declare scalar variable @end. Incorrect syntax near the keyword by

    How can i convert this into an expression?

    MSDNStudent Knows not much!

     

Tutte le risposte

  • lunedì 5 marzo 2012 02:13
     
     
    Registrati per votare
    0
    bump! 

    MSDNStudent Knows not much!

     
  • lunedì 5 marzo 2012 02:58
     
      Contiene codice
    Registrati per votare
    0

    You cannot write Expression like this way:

    For expression learning, please read this article:
    http://msdn.microsoft.com/en-us/library/ms157328.aspx

    =IIF(Parameters!IncludeURLs.Value,"http://adventure-works.com/productcatalog",Nothing)

    BTW, if you want to use a data driven query, you need to create some Parameters and set them into your query or SP.
    Tutorial: Adding Parameters to a Report (SSRS)

    Regards, Nighting Liu

     
  • lunedì 5 marzo 2012 05:47
    Moderatore
     
     Con risposta
    Registrati per votare
    0


    Hi MSDN Student,

    As far as I know, Reporting Services query designer doesn’t support the key word “declare”. That is why theerror occurs.

    And, I noticed thatthere is no report level parameter to use dynamic SQL. So, why not packed it as
    stored procedure and refer to the stored procedure in Reporting Services?

    Lola
    MSDN Community
    Support | Feedback to us

    Get or Request Code Sample from Microsoft

    Please remember to mark the replies as answers if they help and unmark them if
    they provide no help.


    Please remember to mark the replies as answers if they help.


     
  • lunedì 5 marzo 2012 07:19
     
     
    Registrati per votare
    0

    This may be more of org thing but SP team is different and reporting team is different so asking for a new stored procedure is time consuming process as compred to writing everything in SSRS itself.

    anyways will get the SP created.

    MSDNStudent Knows not much!