• Replies 5 replies
  • Subscribers 338 subscribers
  • Views 83 views
  • Users 0 members are here
Related

Fiscal Year as SSRS Report Parameter

Frances O'Connell
$organization

Hi there,

Has anyone ever tried to use the Fiscal Year column in T_CAMPAIGN as a report parameter?  It looks like this column has a data type of integer in SSMS so I have defined it as such in my stored procedure that is the data set for the SSRS Report.  In SSRS Parameter Properties, for this Parameter, it is also defined as an Integer.  When I provide a Default Value (2013) to preview the Report in SSRS, I receive the following error:

"An error occurred during local report processing.  Cannot read the next datarow for the dataset DataSet1. Conversion failed when converting the varchar value '@fiscal_yr' to data type int."  Now i assume this is because SSRS does not recognize '2012' as an integer data type but rather a varchar data type.  But when I tried defining it as varchar(4) in the SP and 'text' in SSRS, it also did not translate successfully.

If I forgo previewing in SSRS and move to Report Setup, add it as a Parameter row, and run in Tess, I currently have a fyear parameter dropdown with nothing to choose from (just a blank scroll down window - that strangely does include gray rows for, I assume, those fiscal years in T_CAMPAIGN that are inactive).

If anyone has any experience using this column in SSRS or Report Setup I'd love to hear if you were able to make it work!

Thanks - 

Frannie

  • $organization

    Based on that error, you have @fiscal_yr defined as a varchar type in DataSet1. Check that the parameter datatype is Integer in the SSRS report, and being passed as an Integer datatype from Tessitura.  For the display column in the Report Setup (Tessitura) dropdown, you’ll need to convert the datatype to a varchar, while keeping the data value (fyear) as the integer datatype (no conversion).

     

    +Ryan Creps

    +Tessitura Network

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Frances O'Connell
    Sent: Friday, October 19, 2012 12:46 PM
    To: Ryan Creps
    Subject: [Tessitura Technical Forum] Fiscal Year as SSRS Report Parameter

     

    Hi there,

    Has anyone ever tried to use the Fiscal Year column in T_CAMPAIGN as a report parameter?  It looks like this column has a data type of integer in SSMS so I have defined it as such in my stored procedure that is the data set for the SSRS Report.  In SSRS Parameter Properties, for this Parameter, it is also defined as an Integer.  When I provide a Default Value (2013) to preview the Report in SSRS, I receive the following error:

    "An error occurred during local report processing.  Cannot read the next datarow for the dataset DataSet1. Conversion failed when converting the varchar value '@fiscal_yr' to data type int."  Now i assume this is because SSRS does not recognize '2012' as an integer data type but rather a varchar data type.  But when I tried defining it as varchar(4) in the SP and 'text' in SSRS, it also did not translate successfully.

    If I forgo previewing in SSRS and move to Report Setup, add it as a Parameter row, and run in Tess, I currently have a fyear parameter dropdown with nothing to choose from (just a blank scroll down window - that strangely does include gray rows for, I assume, those fiscal years in T_CAMPAIGN that are inactive).

    If anyone has any experience using this column in SSRS or Report Setup I'd love to hear if you were able to make it work!

    Thanks - 

    Frannie




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

  • $organization in reply to Ryan Creps (Past Staff Member)

    Thanks Ryan! I have been watching and rewatching your webinars to try to figure this out.

    I went back to the SP to see if it would execute in SSMS, and I get the same error whether I define @fyear as int OR varchar(4). - "Conversion failed when converting the varchar value '@fyear' to data type int."

    I guess I can deduce that the problem is not even in SSRS but in defining the parameter in the SP.

    If anyone has used this column in a report I'd love to know - I don't know why it's not being recognized as an integer when the column is defined that way...

    Thanks!

  • Former Member
    Former Member $organization in reply to Frances O'Connell

    From reading the error, it looks as though it is reading the value as ‘@fyear’ itself, instead of ‘2012’. How have you set the default value in SSMS? Could it be that @fyear = ‘@fyear’?

  • $organization in reply to Former Member

    Thanks Tash!  It was defined correctly, but in my 'where' clause I had "where c.fyear = '@fyear'  - got rid of those quotes and SP executes and the report is available for preview in SSRS.

    thank you!

  • $organization

    Go team!  Glad you are sorted.

     

    +Ryan Creps

    +Tessitura Network

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Frances O'Connell
    Sent: Friday, October 19, 2012 5:45 PM
    To: Ryan Creps
    Subject: RE: [Tessitura Technical Forum] Fiscal Year as SSRS Report Parameter

     

    Thanks Tash!  It was defined correctly, but in my 'where' clause I had "where c.fyear = '@fyear'  - got rid of those quotes and SP executes and the report is available for preview in SSRS.

    thank you!

    From: Natasha Purkiss <bounce-natashapurkiss5883@tessituranetwork.com>
    Sent: 10/19/2012 2:00:04 PM

    From reading the error, it looks as though it is reading the value as ‘@fyear’ itself, instead of ‘2012’. How have you set the default value in SSMS? Could it be that @fyear = ‘@fyear’?




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!