By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Run Query doesn't ask for Params, Run Report does

100+
P: 294
I have a report comprised of 3 different queries.

One query simply lists the values in the table.

The second query compares each "Value" to the previous record's "value".

The third query compares each "value" to the previous year's "value" (similar to comparing year-end value's of some Account X).

It's the second and third queries that are bothering me, as they ask for parameters when the report is ran, but not when each query is ran on their own.

The parameter for each query (it's the same for both):
Expand|Select|Wrap|Line Numbers
  1. NAV_Tbl.NAV_Date
Here is the SQL for the 2 queries:

Expand|Select|Wrap|Line Numbers
  1. SELECT t.NAV_Date, ([NetAssetValue]/[prev_value])-1 AS QTD
  2. FROM (SELECT t.*, (select top 1 [NetAssetValue]  from NAV_Tbl AS t2  where t2.NAV_Date < t.NAV_Date  order by t2.NAV_Date desc) AS prev_value FROM NAV_Tbl AS t)  AS t
  3. WHERE (((([NetAssetValue]/[prev_value])-1) Is Not Null))
  4. ORDER BY t.NAV_Date;
  5.  
For the year-to-date:

Expand|Select|Wrap|Line Numbers
  1. SELECT (y1.NetAssetValue/YearEndValue)-1 AS YTD, (SELECT TOP 1 y2.NetAssetValue
  2.         FROM NAV_Tbl AS y2
  3.         WHERE Year(y2.NAV_Date) = Year(y1.NAV_Date) -1
  4.         ORDER BY y2.NAV_Date DESC
  5.     ) AS YearEndValue, y1.NAV_Date
  6. FROM NAV_Tbl AS y1
  7. ORDER BY y1.NAV_Date;
  8.  
Even if there is a null value, I don't want it to ask for parameters.

For example: in the test data, the NAV_Tbl has two fields - Date and Value.

For the Year-To-Date query, there will be a record where the Value is 0 because we did not keep track of it at that date in time.(Looking back a year, that is)

How can I stop this from happening?

Thanks!
Feb 13 '14 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Did you mean :
Expand|Select|Wrap|Line Numbers
  1. NAV_Tbl.NAV_Date
or rather :
Expand|Select|Wrap|Line Numbers
  1. NAV_Date
Feb 13 '14 #2

100+
P: 294
Where at, NeoPa? The Parameter box that pops up when the report is ran does indeed ask for NAV_Tbl.NAV_Date.
Feb 13 '14 #3

NeoPa
Expert Mod 15k+
P: 31,186
That's what I meant Mark. Not sure I can help in that case I'm afraid. If anything occurs to me I'll come back.
Feb 14 '14 #4

100+
P: 294
I'll try to squelch it by using "Is Not Null" - that's the only solution I can think of at the moment. I'll get back to you.
Feb 14 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.