Solved

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

Posted on 2016-09-27
3
107 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
3 Comments
 
LVL 65

Expert Comment

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

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

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.

Question has a verified solution.

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

Suggested Solutions

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

680 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