none
SSRS 2008 issue with Parameters RRS feed

  • Question

  • Hi,

    Im fairly new to reporting Services, im running SSRS 2008 R2 and writing reports off a MySQL db.

    I have a query for my dataset(ds) and i created a Report wit no parameters with no problems.

    I parameterised the ds query for YEAR (INT data type in the mysql table definition) and works fine. However when i have inserted 2 more parameters for a DateFrom & DateTo fields (datetime datatypes) i get the following error.

    ERROR[07001][MySQL][ODBC 5.1 DRIVER] SQLBing parameter not used for all parameters.

     

    My Code is here :

    /* QSP DOWNLOAD ACCOUNT & PRODUCTS ******* MUST BE ON THE QSPP DATABASE */

    SELECT DISTINCT
    SUBSTR(TEBINDEX.TRGT_PATH_KEY,1,4) "Centre"
    ,SUBSTR(TEBINDEX.TRGT_PATH_KEY,5,5) "Nominal"
    ,SUBSTR(TEBINDEX.TRGT_PATH_KEY,10,4) "Product"
    ,SUBSTR(TEBINDEX.TRGT_PATH_KEY,14,4) "Channel"

    ,TEDTRAN.per ,TEDTRAN.tran_date ,TEDTRAN.btch_num ,TEDTRAN.btch_seq_num
    ,TEDTRAN.btch_type ,TEDTRAN.btch_ref ,TEDTRAN.ref_num ,TEDTRAN.input_usr ,TEDTRAN.input_date
    ,TEDTRAN.fncl_val ,TEDTRAN.descv

    FROM TEDTRAN ,TEBINDEX

    WHERE TEDTRAN.YY =? /*YY as Parameter datatype INT*/
    AND TEDTRAN.BAL = "AB"
    AND TEDTRAN.SysRef = TEBINDEX.SysRef
    AND TEBINDEX.srce_path = 1
    AND SUBSTR(TEBINDEX.TRGT_PATH_KEY,1,4) IN(9500)

    /*and substr(TEBINDEX.TRGT_PATH_KEY,1,4) = "Centre" as Parameter datatype CHAR*/
    /*and substr(TEBINDEX.TRGT_PATH_KEY,5,5) = "Nominal" as parameter datatype CHAR*/
    /*and substr(TEBINDEX.TRGT_PATH_KEY,10,4) = "Product" as parameter datatype CHAR* /
    /*and substr(TEBINDEX.TRGT_PATH_KEY,14,4) = "Channel" as parameter datatype CHAR*/
    AND TEDTRAN.tran_date BETWEEN ? AND ? /*'2009-01-01' AND '2010-01-01' Parameter name ="Tran From" AND Parameter name="Tran to"  datatype datetime '2008-02-31'*/
    AND TEDTRAN.input_date BETWEEN '2009-01-01' AND '2010-01-01'/* Parameter name ="Input From" AND Parameter name="Input to" datatype datetime*/

    AND TEDTRAN.per BETWEEN 1 AND 2 LIMIT 10 /*Parameter name="Period From" AND parameter name= "Period to" datatype INT*/
    ;

     

    The parameterised query runs fine in Query Designer and when excuted does give me a result set.

    Can someone give me some solution for the above problem and also provide guidance in the best approach for writing reports off a mySQL db.

    Regards

    RT

    Monday, March 7, 2011 11:11 PM

Answers

  • I'm not familiar with the specific requirements for MySQL but apparently the data provider is having trouble resolving the parameter references using this syntax.  Regardless of the data source, one reliable work-around is to build the entire query string in an expression.  After writing and executing the base query to get the field metadata into the report design, open the dataset query text in the expression editor and convert it to a VB expression.  You can explicitly concatonate the parameter values into the query with any necessary formating or program logic.

    Here's a simple example of a query expression with a parameter reference:

    =”Select Top ” & Parameters!TopCustomerCount.Value & ” ” _ 
     
    & “   F.CustomerKey, F.OrderDateKey, ” _ 
     
    & “   Case When DC.MiddleName Is Null Then FirstName + ‘ ‘ + LastName ” _ 
     
    & “   Else FirstName + ‘ ‘ + MiddleName + ‘.’ + ‘ ‘ + LastName End CustomerName, ” _ 
     
    & “   Convert(Char(11),DT.FullDateAlternateKey,106) OrderDate, ” _ 
     
    & “   Sum(F.SalesAmount) SalesAmount ” _ 
     
    & “From ” _ 
     
    & “   FactInternetSales F, DimTime DT, DimCustomer DC ” _ 
     
    & “Where ” _ 
     
    & “   F.OrderDateKey = DT.TimeKey ” _ 
     
    & “   And F.CustomerKey = DC.CustomerKey ” _ 
     
    & “   And Convert(Int,DT.CalendarYear) = ” & Parameters!Year.Value & ” ” _ 
     
    & “Group By ” _ 
     
    & “   F.CustomerKey, F.OrderDateKey, DC.MiddleName, DC.FirstName, DC.LastName, ” _ 
     
    & “DT.FullDateAlternateKey ” _ 
     
    & “Order By ” _ 
     
    & “   F.OrderDateKey Desc, CustomerName”
    I hpe this helps.

     


    Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com *Please vote if helpful*
    Tuesday, March 8, 2011 8:42 AM
    Moderator

All replies

  • I'm not familiar with the specific requirements for MySQL but apparently the data provider is having trouble resolving the parameter references using this syntax.  Regardless of the data source, one reliable work-around is to build the entire query string in an expression.  After writing and executing the base query to get the field metadata into the report design, open the dataset query text in the expression editor and convert it to a VB expression.  You can explicitly concatonate the parameter values into the query with any necessary formating or program logic.

    Here's a simple example of a query expression with a parameter reference:

    =”Select Top ” & Parameters!TopCustomerCount.Value & ” ” _ 
     
    & “   F.CustomerKey, F.OrderDateKey, ” _ 
     
    & “   Case When DC.MiddleName Is Null Then FirstName + ‘ ‘ + LastName ” _ 
     
    & “   Else FirstName + ‘ ‘ + MiddleName + ‘.’ + ‘ ‘ + LastName End CustomerName, ” _ 
     
    & “   Convert(Char(11),DT.FullDateAlternateKey,106) OrderDate, ” _ 
     
    & “   Sum(F.SalesAmount) SalesAmount ” _ 
     
    & “From ” _ 
     
    & “   FactInternetSales F, DimTime DT, DimCustomer DC ” _ 
     
    & “Where ” _ 
     
    & “   F.OrderDateKey = DT.TimeKey ” _ 
     
    & “   And F.CustomerKey = DC.CustomerKey ” _ 
     
    & “   And Convert(Int,DT.CalendarYear) = ” & Parameters!Year.Value & ” ” _ 
     
    & “Group By ” _ 
     
    & “   F.CustomerKey, F.OrderDateKey, DC.MiddleName, DC.FirstName, DC.LastName, ” _ 
     
    & “DT.FullDateAlternateKey ” _ 
     
    & “Order By ” _ 
     
    & “   F.OrderDateKey Desc, CustomerName”
    I hpe this helps.

     


    Paul Turley, MVP [Hitachi Consulting] SQLServerBIBlog.com *Please vote if helpful*
    Tuesday, March 8, 2011 8:42 AM
    Moderator
  • Haven't tried such before. Just a thought,

    1. Try use single quotes that MySQL seems like single quotes

    2. Try convert your datetime datatype to varchar or something MySQL can accept.


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Tuesday, March 8, 2011 9:21 AM