CODEWITHSUNDEEP
sql-serverreporting-servicesreportdrilldowndrillthrough
Merlin Nestler
Merlin Nestler

Reputation: 448

Pass dates between SSRS reports

I have two SSRS reports. They are both accessing the same underlying table, aggregate or filter it and then display a table to the user. When I click on an Item Id in the table of the first report I want the second report to open and show data using the same parameters as were used in the first report.

Report 1 has 2 parameters. Date1 and Date2. The underlying table stores snapshot data and report 1 compares this data for all items. Report 2 compares the data only for one item.

When I click on an item in report 1 I want to pass Date1, Date2 and the item to report 2.

In both reports the Date1 and Date2 parameters are given a list of available dates which are drawn from the table containing the snapshots. This way the user has a dropdown to choose the dates he wants to compare.

I followed this tutorial about how to create drill down reports. https://www.youtube.com/watch?v=7Qk8BWLwGPI

It works for the item. It gets passed from report 1 to report 2. But the dates do not get passed and when an item is clicked in report 1 there is still "Select a Value" displayed in the input fields of the date parameters in report 2.

I already tried to create hidden parameters with default NULL which don't use the dropdown. Then I set the dates with dropdown to be nullable and set their default to NULL. In the SQL code I overwrite the original date parameters with the hidden ones if they are not NULL. Then I passed the parameters from report 1 to the hidden parameters in report 2.

But it is still not working. I did this test because I guessed that the dropdown somehow interferes with passing parameters.

Do you have experience with typical problems when passing date parameters between SSRS reports? Please give me suggestions what I can try to solve this problem.

Upvotes: 0

Views: 35

Answers (1)

Merlin Nestler
Merlin Nestler

Reputation: 448

When dates are passed to SSRS everything after the seconds gets cropped.

See this answer for reference: SSRS Report DateTime Parameters Do Not Support Milliseconds - Previews Correctly in Visual Studio But No Records in SSRS

When report 1 passes the parameter values to report 2 they are therefore cropped to seconds.

However, report 2 compares the dates to the original values from the database which are containing milliseconds.

If the cropping is already included in the SQL statements of both reports SSRS can do a successful comparison and report 2 gets passed the correct values.

Credits to the following community members who insisted on this being a formatting problem. They were proven to be correct.

Upvotes: 0

Related Questions