question

KevinM-207 avatar image
0 Votes"
KevinM-207 asked KevinM-207 answered

Passing a calculated field to a subreport

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
parameters.png (17.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

KevinM-207 avatar image
0 Votes"
KevinM-207 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT commented

Hi @KevinM-207,

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



· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

may I know how's the issue going on now?

0 Votes 0 ·
KevinM-207 avatar image
0 Votes"
KevinM-207 answered ZoeHui-MSFT commented

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



image.png (4.5 KiB)
image.png (4.8 KiB)
image.png (15.4 KiB)
image.png (11.9 KiB)
image.png (18.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KevinM-207, I tried to add the [YYYY0901] to subreport and then pass the calculated field value to the DUE_DATE in subreport. And then the main-report is well done.
You may also have a try.

0 Votes 0 ·