• Replies 12 replies
  • Subscribers 963 subscribers
  • Views 405 views
  • Users 0 members are here
Related

Custom Report not accepting parameters from Tessitura Report window

David Rosen
$organization

Hi,

I created a custom report in BIDS, but I am having trouble getting the report/SSRS to accept the parameters that get passed from the report window and then run automatically. For example, here is the report window with the parameters selected:

When I click OK, I get the following:

As you can see, “Contact Info” did not accept the “No” parameter and the giving categories I selected did not get passed to the report, as I have to click on the “giving cats” dropdown and reselect the categories I want. Both "giving years" and "list no" did actually accept the given parameter values, but I'm not sure why those did and not the others. Additionally, even after I reselect my parameters, I still have to click “View Report.”

I want the report to select the parameters I initially chose and then run by itself. Can anyone help me with this? I’ve been back and forth between Tessitura, BIDS, and report setup, and I can’t’ seem to figure out the issue. Any help would be greatly appreciated. Thanks!

David

Parents
  • $organization

    Hi David - Without having seen the files, I'd recommend:

    1. In SSRS parameter properties:
      1. Change your parameter visibility to hidden (this has solved problems more than once for me)
      2. Make sure your multi-select parameters are set to Data Type = Text
      3. I've also resolved issues by checking the "Allow null value" box on multi-select parameters even when they're required in Report Setup
      4. There's no need to check the "Allow multiple values" checkbox on the parameter in SSRS if you're passing it as a string which is the only way I've found that works consistently.
    2. In Tessitura Report Setup:
      1. Make sure the "MS" box is checked for the multi-select parameters
      2. Make sure the parameter name exactly matches the parameter name in the stored procedure/query (including capitalization)

    If it helps, I've included an example of part of one stored procedure I use where the first parameter (season_str) is a single select and the second parameter (pkg_str) is multi-select:

    ----

    --SEASONS PARAMETER TABLE
    Create Table #season (
    season_id int null
    ,season_desc varchar(50) null
    )

    Insert #season (season_id, season_desc)
    SELECT DISTINCT
    s.id
    ,s.description
    FROM [dbo].TR_SEASON AS s
    JOIN [dbo].FT_SPLIT_LIST(@season_str, ',') x on s.id = x.element

    --PKG PARAMETER TABLE
    Create Table #pkgs (
    pkg_no int null
    ,pkg_desc varchar(50) null
    ,season int null
    )

    Insert #pkgs (pkg_no, pkg_desc, season)
    SELECT DISTINCT
    p.pkg_no
    ,p.description
    ,p.season
    FROM [dbo].T_PKG AS p
    JOIN [dbo].FT_SPLIT_LIST(@pkg_str, ',') x on p.pkg_no = x.element
    JOIN #season AS s ON p.season = s.season_id
    WHERE p.season IN (s.season_id)

    ---

    If you'll you send me your stored procedure/query, SSRS file, and an export of the report set-up, I'm happy to look at this for you in greater detail.

    Cheers,
    Sara Nemeth
    snemeth@cballet.org

Reply
  • $organization

    Hi David - Without having seen the files, I'd recommend:

    1. In SSRS parameter properties:
      1. Change your parameter visibility to hidden (this has solved problems more than once for me)
      2. Make sure your multi-select parameters are set to Data Type = Text
      3. I've also resolved issues by checking the "Allow null value" box on multi-select parameters even when they're required in Report Setup
      4. There's no need to check the "Allow multiple values" checkbox on the parameter in SSRS if you're passing it as a string which is the only way I've found that works consistently.
    2. In Tessitura Report Setup:
      1. Make sure the "MS" box is checked for the multi-select parameters
      2. Make sure the parameter name exactly matches the parameter name in the stored procedure/query (including capitalization)

    If it helps, I've included an example of part of one stored procedure I use where the first parameter (season_str) is a single select and the second parameter (pkg_str) is multi-select:

    ----

    --SEASONS PARAMETER TABLE
    Create Table #season (
    season_id int null
    ,season_desc varchar(50) null
    )

    Insert #season (season_id, season_desc)
    SELECT DISTINCT
    s.id
    ,s.description
    FROM [dbo].TR_SEASON AS s
    JOIN [dbo].FT_SPLIT_LIST(@season_str, ',') x on s.id = x.element

    --PKG PARAMETER TABLE
    Create Table #pkgs (
    pkg_no int null
    ,pkg_desc varchar(50) null
    ,season int null
    )

    Insert #pkgs (pkg_no, pkg_desc, season)
    SELECT DISTINCT
    p.pkg_no
    ,p.description
    ,p.season
    FROM [dbo].T_PKG AS p
    JOIN [dbo].FT_SPLIT_LIST(@pkg_str, ',') x on p.pkg_no = x.element
    JOIN #season AS s ON p.season = s.season_id
    WHERE p.season IN (s.season_id)

    ---

    If you'll you send me your stored procedure/query, SSRS file, and an export of the report set-up, I'm happy to look at this for you in greater detail.

    Cheers,
    Sara Nemeth
    snemeth@cballet.org

Children
No Data