April 4, 2012 at 8:17 am
I have a requirement for a report which outputs data for the current financial year (defined as 1 Apr to 31 Mar). The user does not select the year, I just want it to use whatever the current year is.
I've got the following two expressions which will give me the start and end dates of the report:-
=cdate("1 Apr " & year(today) - iif(month(today())<=3, 1, 0))
=cdate("31 Mar " & year(today) + iif(month(today())<=3, 0, 1))
No problem there.
The issue I'm facing is that this report uses three separate datasets, each of which is based on a query that requires these two dates. At the moment I'm using these expressions to define the parameters in each of the datasets, which seems wasteful. I'd like to put the expressions in one place where all the datasets can reference them. Essentially, I want a variable with Report level scope.
Aha, I thought, that would be a report variable then. So I created report variables for these two values. I then went to the datasets parameters and pointed the apropriate parameters at the apropriate variable. I expected this to work but I get a message saying that variable values cannot be used in query parameter expressions.
Can anyone tell me how I can achieve this?
April 4, 2012 at 8:45 am
Hi
There may be a better way but you could create 2 datasets FinancialStart and FinancialEnd and write a SQL statement i.e.
FinancialStart
SELECT CAST(DATEADD(YEAR,-1,'01-04-'+CONVERT(VARCHAR(4),YEAR(GETDATE()))) AS DATE) --01-04-2011
FinancialEnd
SELECT CAST('30-04-'+CONVERT(VARCHAR(4),YEAR(GETDATE())) AS DATE) --30-04-2012
Then go to your parameter list > Parameter Properties > Available Values > Get Values From Query and pick the relevant parameter then use these in your queries - I haven't tried this but it could work...
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 4, 2012 at 10:02 am
Thanks. That sort of works. I don't need to create the datasets, I can just specify values in the parameter rather than getting from a query. And I need to set the default value rather than the available value. Still feels very messy, though.
I think I'm going to go with this for now but I'm leaving the floor open to a more elegant solution is anyone has one.
Just as an aside, does anyone know the reasoning behind not allowing report variables to be used in queries? It seems like an obvious ommission to me so I'm curious as to why it's there.
April 4, 2012 at 11:05 am
I agree it does seem likes a bit of a hack!
Yes you could do that the only reason I suggested a dataset is I feel its a little more visable rather than it being "hidden" within the parameter especially if you give it a meaningful name.
No idea why but I will be keeping an eye on this topic to see if any other methods are suggested!
Glad I could help...kind of 😉
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 4, 2012 at 12:03 pm
I agree it does seem likes a bit of a hack!
Meh, it wouldn't be the first and I'm sure it won't be the last.
Glad I could help...kind of
There's no "kind of" about it. Using a pram might be a bit of a hack but it's still much better than having the same expression being maintained in three different places.
Thanks again
April 10, 2012 at 9:58 pm
I agree Andy.
I use this method all the time...
I have lots of reports/queries like this, and its the only way I could find to make it work...
Query > Report Parameter > Query
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply