Passing a calculated field to a subreport

Kevin M 96 Reputation points
2021-03-10T20:09:43.17+00:00

Hi!

I am trying to pass calculated numeric field data from the main report to the subreport. I also am passing field data to the subreport with no problem, it's only calculated data not working.

The data is from the calculated field is called YYYY0901. The calculation simply converts a 6 digit integer from another field into an 8 digit integer.

I created the parameter within the subreport properties matching it up to a field in the subreport called DUE_DATE (image below). This field is just an 8 digit integer representing a date, e.g., 20200901 translates to Sept 1st of 2020.

76443-parameters.png

When I run the report, nothing is returned from the subreport. If I hardcode the DUE_DATE in the subreport with a known proper value and just pass an ID to the person's record from the main report, I get the data. So the sub report and main report are working, I am having trouble passing the calculated data. I verified the calculated data is valid by including it into the main report so I can see what it should be and it is correct.

I am using MS Report Builder in SQL Server 16.

TIA, Kevin

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,813 questions
0 comments
Accepted answer
  1. Kevin M 96 Reputation points
    2021-03-17T15:02:01.007+00:00

    Hi!

    I ended up going a different route. I am now passing over the semester to the subreport, calculating the date in the subreport, and then filtering the dataset using the new calculated value to get my return data to the main report. This is working perfectly. Still not sure why I couldn't pass a calculated value but this new process is working fine.

    Thank you for your help!

    Kevin

    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,296 Reputation points
    2021-03-11T08:38:45.94+00:00

    Hi @Kevin M ,

    I did some test locally that I couldn't reproduce the issue.

    Suppose that there are some different settings between our reports or I misunderstand your issue.

    Below is my simple report.

    The Mdate is a calculated field and the subreport is well done.

    76605-screenshot-2021-03-11-163518.jpg

    If possible you may share the simple design sample for us to do more analysis.

    Regards，

    Zoe

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

  2. Kevin M 96 Reputation points
    2021-03-16T14:49:17.373+00:00

    Hi!

    Here is the breakdown of the value I am passing;

    [YYYY0901] is defined as (Int(((Parameters!CURRENT_SEMESTER.Value)/100)-1)*1000)+901 where CURRENT_SEMESTER is a 6 digit number. In this example, it is 202101.

    Running of the report produces:
    78312-image.png

    The value of 20200901 is correct as in the snippet. Note the Sport(s) column is blank. It should not be. In this example, I pass a student ID and the 20200901 record to pull data from a table that would have matches on these values. If I run the subreport directly and give it a known ID and 20200901, then I do get results.
    78331-image.png

    So, the values passing to the subreport look correct, just not sure why it's not returning the data. I admit that SSRS is new to me as I've been writing reports using Crystal Reports for 20+ years.

    Here are the Parameters defined in the subreport:
    78240-image.png 78332-image.png

    Subreport properties:
    78322-image.png

    Let me know if I forgot anything!

    TIA, Kevin