SSRS Dynamic select data in dataset

Hello Experts Exchange
I have a SSRS report that displays daily sales data, the user selects the date for the day they want to see.

I need to show target amount based on the day the user has selected.

I have two tables, one table has the target amount for the week.  The second table has a percentage the shop should do on a particular day of the week.

Table 1 looks like this
Loc_ID      Week_No      Year      Target
2000      26                      2015      2225

Table 2 looks like this
Loc_ID      SUN_Perc      MON_Perc      TUE_Perc      WED_Perc      THU_Perc      FRI_Perc      SAT_Perc
2000      0                       16.54              15.19               14.9               15.69               22.12      15.56

I need to take the Target from table1 divide it by 100 then times it by the day of the week percentage from table 2

So for Mon day the it would be (2225/100)*16.54

But I need to select the percentage dynamically based on the date the user selects, how would I do this?  

Regards

SQLSearcher
SQLSearcherAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
I'm not sure where the date comes into this.  There is no date in table2, so I'm guessing that the percentages are fixed no matter what the week is and the only date criteria is in table1.  If that is the case, then I believe that this does what you are looking for.
SELECT a.loc_id, 
       a.week_no, 
       a.year, 
       a.target, 
       ( a.target / 100 ) * b.sun_perc sun_amt, 
       ( a.target / 100 ) * b.mon_perc mon_amt, 
       ( a.target / 100 ) * b.tue_perc tue_amt, 
       ( a.target / 100 ) * b.wed_perc wed_amt, 
       ( a.target / 100 ) * b.thu_perc thu_amt, 
       ( a.target / 100 ) * b.fri_perc fri_amt, 
       ( a.target / 100 ) * b.sat_perc sat_amt 
FROM   tab1 a 
       join tab2 b 
         ON a.loc_id = b.loc_id 
WHERE  a.loc_id = 2000 
       AND a.week_no = 26 
       AND a.year = 2015; 

Open in new window

If that isn't what you are looking for, then I think we need more information.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Koen Van WielinkBusiness Intelligence SpecialistCommented:
I suspect that further to Johnsone's comment, you need to know how to use the parameter in the query?
If so, in the "where" clause of the query above, replace as follows:

Where a.loc_id = 2000
and a.week_no = datepart(Week,@selectedDate)
and a.year = datepart(Year,@selectedDate)

Open in new window


where @selectedDate is your parameter value.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.