Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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

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)
0
cindyfiller
Asked:
cindyfiller
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
- @Days should really be -1 * @Days
0
 
Megan BrooksSQL Server ConsultantCommented:
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?
0
 
cindyfillerAuthor 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.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now