Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2016-09-27
3
Medium Priority
?
177 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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

719 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