Solved

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

Posted on 2016-09-27
3
159 Views
Last Modified: 2016-09-28
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
Comment
Question by:cindyfiller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 41818425
- @Days should really be -1 * @Days
0
 
LVL 16

Accepted Solution

by:
Megan Brooks earned 500 total points
ID: 41818640
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
 

Author Comment

by:cindyfiller
ID: 41819672
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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question