SSRS sql paramater that works in Query Designer but not in the report

cindyfiller
cindyfiller used Ask the Experts™
on
I have a parameter in a where statement that works fine when I am using query designer in SSRS 2008.  But when I go back out to the report section, I get a pop up screen that says define query paramaters and my parameter that I used  has been stripped from the code or all of the fields in the dataset are gone.  The parm I'm having an issue with is the @Days.  If I substitute 90 for the @Days in query designer, it works in both the query designer and the SSRS report...  I'm not quite sure how to fix this...  Can anybody help?

WHERE  (FACT_GiftCommitment.BalanceAmount = 0) AND (FACT_GiftCommitment.LastPaymentDate BETWEEN @BeginDate AND @EndDate) AND (DATEADD(dd, - @Days, FACT_GiftCommitment.LastInstallmentDate) > FACT_GiftCommitment.LastPaymentDate)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornData Architect
Most Valuable Expert 2013
Author of the Year 2015

Commented:
- @Days should really be -1 * @Days
SQL Server Consultant
Commented:
There is a popup that can sometimes appear asking you to enter values for all of the query parameters. Is that the one you are seeing? If you enter sample values for all of the parameters, often that is all that is needed.

Another alternative is to manually copy the text from the designer window, cancel the designer, and paste it into the dataset window. This may not fully update the RDL (XML) with the correct parameter type information, however, and if you have already lost the field information you may need to create it by hand or go to a backup.

Does the @Day query parameter point to the @Day report parameter, or is it defined with some other expression? If it is defined as the @Day report parameter, what its type -- is it Integer or Text or something else?
cindyfillerDirector of IT

Author

Commented:
Yes that popup was asking for the values of the parameters.  In the past when I saw that pop up I thought there was an error and would click out of it and go back to try and fix the program.  I'd never tried entering the values again.  Wow - that actually solved the problem.  Learned a good lesson today.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial