• Replies 5 replies
  • Subscribers 336 subscribers
  • Views 53 views
  • Users 0 members are here
Related

Problem with SSRS not displaying data

Former Member
Former Member $organization

I created a stored procedure that includes a variable for a name, and variables for start date and end date.
If a name is passed in, the stored procedure ignores the start/end date values.
If start/end date values are passed in, the name is ignored.

The stored procedure works as expected.
If I execute the stored procedure with just a name, the desired data appears.
If I execute the stored procedure with just start/end dates, the desired data appears.

My problem is with SSRS.

I created a new report and pointed the dataset to the stored procedure.

The variables are visible and can be NULL.
When I preview the report and insert a name (but not the dates), the report works.
When I preview the report and insert start/end dates (but not a name), no data appears.

I verified valid start/end date values are being passed in, and checked the parameter values.  They appear to be OK.

I even changed the datatypes for the start/end dates.
Again, it works in SMSS but not in SSRS.

I have other reports that include start/end dates, and they work as expected.
I checked the values of these reports against my newly created report, and everything appears to match.

I even checked the code of the new report (sometimes an update in the GUI doesn't update the code).


I tried to ensure nothing has been overlooked, but I just can't find the solution.


To any SSRS gurus out there, any suggestions will be welcome.

Best Regards
Wendell Baskin
Bass Hall - Fort Worth

  • $organization

    Hi

    One of the things I have done in the past to help with this, is to create a local table (in your case, have run_dt [datetime], name [varchar] NULL, start date [datetime] NULL and end dates [datetime] NULL) and then add a line to your stored procedure to store in that table the parameters that are passed in when you run the report from SSRS.

    This will allow you to see the parameters and run the sp yourself with the same parameters to see what the data output is. (I am sure sql trace and other tools are available to do this but this is one of those things you know is simple and will always work)

    It could be that if you blanked the name field you have a Blank ('') rather than NULL being passed to the name parameter. 

    Mark

  • Former Member
    Former Member $organization

    Thank you, Mark … I’ll try that (I did include a SELECT @start_date for testing).

    Also, I had not considered a SQL trace.

    I hate trawling through all of that data, but it can reveal problems.

     

    I not too SQL savvy, but I may have discovered another problem.

    In the stored procedure I used an IF with BEGIN / END followed by another IF with BEGIN / END.

    It appears SSRS doesn’t like that.

     

    Today I will resolve this puzzle.

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Mark Ridley
    Sent: Thursday, April 16, 2015 02:27
    To: Wendell Baskin
    Subject: Re: [Tessitura Technical Forum] Problem with SSRS not displaying data

     

    Hi

    One of the things I have done in the past to help with this, is to create a local table (in your case, have run_dt [datetime], name [varchar] NULL, start date [datetime] NULL and end dates [datetime] NULL) and then add a line to your stored procedure to store in that table the parameters that are passed in when you run the report from SSRS.

    This will allow you to see the parameters and run the sp yourself with the same parameters to see what the data output is. (I am sure sql trace and other tools are available to do this but this is one of those things you know is simple and will always work)

    It could be that if you blanked the name field you have a Blank ('') rather than NULL being passed to the name parameter. 

    Mark

    From: Wendell Baskin <bounce-wendellbaskin7249@tessituranetwork.com>
    Sent: 4/15/2015 4:14:56 PM

    I created a stored procedure that includes a variable for a name, and variables for start date and end date.
    If a name is passed in, the stored procedure ignores the start/end date values.
    If start/end date values are passed in, the name is ignored.

    The stored procedure works as expected.
    If I execute the stored procedure with just a name, the desired data appears.
    If I execute the stored procedure with just start/end dates, the desired data appears.

    My problem is with SSRS.

    I created a new report and pointed the dataset to the stored procedure.

    The variables are visible and can be NULL.
    When I preview the report and insert a name (but not the dates), the report works.
    When I preview the report and insert start/end dates (but not a name), no data appears.

    I verified valid start/end date values are being passed in, and checked the parameter values.  They appear to be OK.

    I even changed the datatypes for the start/end dates.
    Again, it works in SMSS but not in SSRS.

    I have other reports that include start/end dates, and they work as expected.
    I checked the values of these reports against my newly created report, and everything appears to match.

    I even checked the code of the new report (sometimes an update in the GUI doesn't update the code).


    I tried to ensure nothing has been overlooked, but I just can't find the solution.


    To any SSRS gurus out there, any suggestions will be welcome.

    Best Regards
    Wendell Baskin
    Bass Hall - Fort Worth




    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 Former Member

    I am guessing the 2 if blocks are because one you look for name and the second date.

    If the SSRS is passing a Blank rather than a null for name plus your dates it may well be that it is bringing back both data sets and will either have an issue or only show the first data set.

    If it is that then seeing what is passed in will help.

    Mark

     

  • Former Member
    Former Member $organization

    Wendell

    I've fallen foul of this situation before, if I've understood you correctly. When you have an SSRS report with a stored procedure that has multiple pathways through producing different potential results, you have to make sure that each result set that could be produced by the SP have exactly the same columns in them, even if some of those columns are redundant. When you add the call to the SP as a datasource within SSRS it produces a blueprint of the output generated from the SP. If one of your alternative pathways deviates from that blueprint in any way, the report won't work when run in Tessitura.

    I've even had to write some dummy code in an SP just returning the columns I need as NULLs to get the blueprint generated correctly at design time, and then swapped out the SP code for the real stuff after the SSRS has been compiled.

    I hope this makes sense and helps you solve your problem.

    Christine

  • Former Member
    Former Member $organization

    Thanks, Christine … your comments will be helpful.

     

    After much trial-and-error, I replaced the stored procedure with a bunch of SQL.

    The code is kinda ugly, but it works.  Now, it’s up to my user to validate the results.

     

    My SQL skills are so-so, but my SSRS skills are weak (still learning).

    Thanks to some online resources and the many great people in Tessitura land, things are improving.

     

    Best Regards

    wendell

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Christine Chester
    Sent: Friday, April 17, 2015 08:46
    To: Wendell Baskin
    Subject: Re: [Tessitura Technical Forum] Problem with SSRS not displaying data

     

    Wendell

    I've fallen foul of this situation before, if I've understood you correctly. When you have an SSRS report with a stored procedure that has multiple pathways through producing different potential results, you have to make sure that each result set that could be produced by the SP have exactly the same columns in them, even if some of those columns are redundant. When you add the call to the SP as a datasource within SSRS it produces a blueprint of the output generated from the SP. If one of your alternative pathways deviates from that blueprint in any way, the report won't work when run in Tessitura.

    I've even had to write some dummy code in an SP just returning the columns I need as NULLs to get the blueprint generated correctly at design time, and then swapped out the SP code for the real stuff after the SSRS has been compiled.

    I hope this makes sense and helps you solve your problem.

    Christine

    From: Wendell Baskin <bounce-wendellbaskin7249@tessituranetwork.com>
    Sent: 4/15/2015 4:14:56 PM

    I created a stored procedure that includes a variable for a name, and variables for start date and end date.
    If a name is passed in, the stored procedure ignores the start/end date values.
    If start/end date values are passed in, the name is ignored.

    The stored procedure works as expected.
    If I execute the stored procedure with just a name, the desired data appears.
    If I execute the stored procedure with just start/end dates, the desired data appears.

    My problem is with SSRS.

    I created a new report and pointed the dataset to the stored procedure.

    The variables are visible and can be NULL.
    When I preview the report and insert a name (but not the dates), the report works.
    When I preview the report and insert start/end dates (but not a name), no data appears.

    I verified valid start/end date values are being passed in, and checked the parameter values.  They appear to be OK.

    I even changed the datatypes for the start/end dates.
    Again, it works in SMSS but not in SSRS.

    I have other reports that include start/end dates, and they work as expected.
    I checked the values of these reports against my newly created report, and everything appears to match.

    I even checked the code of the new report (sometimes an update in the GUI doesn't update the code).


    I tried to ensure nothing has been overlooked, but I just can't find the solution.


    To any SSRS gurus out there, any suggestions will be welcome.

    Best Regards
    Wendell Baskin
    Bass Hall - Fort Worth




    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!