June 27, 2008 at 7:10 am
Hi,
I am stuck and I don't know what to do. My boss wants me create this report that has parameters, if he wanted to see the report weekly he could, if he wanted to see the report Monthly he could and also if he wanted to see it yearly. I created a drop down so he can select what type of report he would like to see. My problem is, when I do that it makes me choose all of the parameters. what should I do or what function do I need to use to accomplish this.
Here are my formulas
Week -
(YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))
Month -
(MONTH(CLM_DOUT) = MONTH(GETDATE())) AND (YEAR(CLM_DOUT) = YEAR(GETDATE()))
Year -
(YEAR(CLM_DOUT) = YEAR(GETDATE()))
Thanks!:)
Wendy
June 27, 2008 at 7:23 am
It really depends on how you are using your parameters inside the report or dataset. Normally what I would expect to see is one parameter only, and that parameter has a drop down choice of Weekly, Monthly, Yearly.
Either the dataset, or the contents of the report will then act upon the value in that parameter.
Tell me how the report changes for each type and maybe I can be a little more specific for you.
Nigel West
UK
June 27, 2008 at 7:28 am
Yeah What I did was I created 3 data sets. One for each of those formulas for Weekly,Monthly and Yearly.
Then this is my Sql below.
SELECT DISTINCT
clm_id1, CONVERT(CHAR(10), clm_rcvd, 110) AS Daterecvd, CONVERT(CHAR(10), CLM_DOUT, 110) AS dateclosed, clm_wkpct, clm_1a, clm_12a,
clm_12b, clm_55d, clm_clir, clm_65a, clm_medb2, clm_tchg, clm_base, clm_stades, clm_prod, clm_nego, clm_sppo, clm_1e, Note, AccessFeeFinal,
CLM_ATT2, CLM_ATT3, ACCESSFEEIMPACT, CAST(clm_sppo / clm_tchg * 100 AS decimal(4, 2)) AS PercentSavings, MAS90#,
clm_meda4 AS OriginalAllow, CLM_H30 AS AdjustedTotalSavings
FROM All_Clients_Discount_Management_DEVON_ADV
WHERE (clm_nego > 0.00) AND (clm_sppo > 0.00) AND (Note = 'AF') AND (CLM_DOUT = @Year) OR
(CLM_DOUT = @week) OR
(CLM_DOUT = @Month)
Does this help? If not, I can try to explain more.
June 27, 2008 at 8:00 am
OK, I think I understand what you are trying to achieve, I am guessing that you want the user to be able to input either the week, month or year into a parameter.
So, consider the following:
Set up one parameter with a drop down that allows them to choose between three strings (Week, Month, Year). Then set up another parameter that allows them to input the number.
In your ONE data set you should add the following code (similar anyway):
WHERE (@DateType='Week' AND ......convert date to week no..... =@DateNumber)
OR (@DateType='Month' AND ......convert date to month no..... =@DateNumber)
OR (@DateType='Year' AND ......convert date to year no..... =@DateNumber)
Does this make sense to you?
Nigel West
UK
June 27, 2008 at 8:19 am
I think this may work, but I am still a little confused. So in my 1st dataset I am going to put....
SELECT CLM_DOUT
FROM All_Clients_Discount_Management_DEVON_ADV
WHERE (@DateType='Week' AND (YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))=@DateNumber)
OR (@DateType='Month' AND(MONTH(CLM_DOUT) = MONTH(GETDATE())) AND (YEAR(CLM_DOUT) = YEAR(GETDATE()))=@DateNumber)
OR (@DateType='Year' AND (YEAR(CLM_DOUT) = YEAR(GETDATE())) =@DateNumber)
June 27, 2008 at 8:31 am
SELECT CLM_DOUT
FROM All_Clients_Discount_Management_DEVON_ADV
WHERE (@DateType='Week' AND (YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (DATEPART(wk, CLM_DOUT) = DATEPART(wk, GETDATE()))=@DateNumber)
OR (@DateType='Month' AND(MONTH(CLM_DOUT) = MONTH(GETDATE())) AND (YEAR(CLM_DOUT) = YEAR(GETDATE()))=@DateNumber)
OR (@DateType='Year' AND (YEAR(CLM_DOUT) = YEAR(GETDATE())) =@DateNumber)
I think there might be some syntax issues here...
it should be something like:
SELECT CLM_DOUT
FROM All_Clients_Discount_Management_DEVON_ADV
WHERE (@DateType='Week' AND (YEAR(CLM_DOUT) = YEAR(GETDATE())) AND (DATEPART(wk, CLM_DOUT) = @DateNumber))
OR (@DateType='Month' AND(MONTH(CLM_DOUT) = @DateNumber) AND (YEAR(CLM_DOUT) = YEAR(GETDATE())))
OR (@DateType='Year' AND (YEAR(CLM_DOUT) = @DateNumber)
The point here is that date number represents the actual value you are looking for, so if its a year then I expect datenumber to equal 2008 for example. If its a month then the datenumber should be between 1 and 12.
Nigel West
UK
June 27, 2008 at 8:41 am
Ok, I will try that. Thanks for your help! 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply