Getting a parameter to depend on another parameter.

I'm putting together a crosstab showing some facts for a year, compared to the year before (so we're showing 2 years). Years in the columns and a dimension in the rows. Nothing fancy, but we do want to put in a parameter so the users can choose which year they want to see. So I put a range and get two parameters. We now want the first parameter, the "from", to take the value of the "to" - 1. I can get the same value in there, but appearantly getting the previous year isn't as simple as just taking the parameter - 1.
A helping hand would be great. This all seems a bit overkill for what to me feels like an easy report, so I feel like I'm missing something.

We're building this report on a cube.

Hi,

In the Report Parameters dialog, set the Default value of your second parameter to Non Queried and enter the following in the Expression Editor

Code Snippet

=CINT(Parameters!<Param1>.Value) - 1

Replace with the name of your parameter and this should achieve what you want to do.

HTH.

Cheers,

Leigh

|||That gives me an error while previewing. More precisely, when I select the "to" in preview, I get:

Code Snippet

An error occured during local report processing.
Error during processing of of "FromYear" report parameter.

Can it be because the first (to) depends on a time dimension?

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!

GETDATE() Query Giving Me Trouble

Hello all,

I'm trying to put together a query that will give me all records in my db where the date in the "NextDate" column equals the date that the query is run. Seems easy...so I put together the following query:

SELECT EventNo, NextDate, TrainersLastName, ItemSerialNo, ManufacturerName, ItemModel, ScheduledMaintenance, RBDate, Daily, Weekly, Monthly, Yearly
FROM Maintenance
WHERE (NextDate = GETDATE()) AND (RBDate = 'true') OR
(Daily = 'true') OR
(Weekly = 'true') OR
(Monthly = 'true') OR
(Yearly = 'true')
ORDER BY EventNo

I'm not getting any records returning even though there are records in the db that match the criteria. Any ideas on how I can solve this?

Thanks in advance for any help!

Tony

Date comparison's take the full date and time into consideration.

If you're looking to match only on MDY, then try

Code Snippet

datediff(dd, NextDate, GETDATE())=0

|||

Yes, datediff will work; however, it will not hit any potential indexes because of the operator on the nextDate column. Again, I admit that in this case indexes might not be relevant. Nonetheless, I will still prefer to at least have a chance at hitting an index. I would prefer something more like:

Code Snippet

where nextDate >= dateadd(day, datediff (day, 0, getdate()), 0)
and nextDate < dateadd(day, datediff (day, 0, getdate()), 0) + 1

I went back to grab a bottle of water and I realized that with all of those ORs it is probably not going to hit an index anyway. Please ignore my previous baloney.

( Thanks, Dale; yes, the water is ice cold. It is hot here too. )

|||

LOL

But, you do have a good point Kent.

However, without more info the point might be moot.

datediff(...) is the simplest solution; other solutions would need to take indexes, table size, etc. into consideration.

Hope it's ice cold water....it's 95 here today

|||

Thanks very much guys for the replies!

DaleJ,

The DATEDIFF solution worked great. I didn't realize that GETDATE() compared time also. No wonder nothing was matching. For my own clarification/education, could you explain a little bit regarding the DATEDIFF statement. Am I correct that the statement is specifying the formatted date (dd) difference between "NextDate" and GETDATE() is = 0 (therefore being the same date)?

Thanks again very much for the help!

Tony

|||

Hey Tony

datediff gets the number of units (operand 1, dd) between date1 (NextDate) and date2 (getdate()).

The =0 checks that that difference is 0, meaning that it's the same date

|||Got it. Thanks again very much!sql