June 4, 2010 at 5:57 am
Hi
I have a budget report which returns the estimated and actual costs for a group of cost types. These cost types are related to provision pupils receive in schools.
The report needs to return either delegated funding costs or non-delegated funding costs. There is no where in the database which states which cost types or provisions are delegated or non-delegated. Therefore I have created a parameter which enables the user to select either 'Delegated' or 'Non delegated'. This parameter returns a integer of 1 for Delegated and 2 for Non-Delegated.
Based on the output of this parameter I would like to filter the report to show the appropriate cost types or provisions.
If Delegated is selected then Cost_type IN("B-P", "B-S", "C-P", "C-S", "DEBL", "MONP", "MONS", "PTFA", "PTFE", "PTFH", "PTFP", "PTFS", "PTFV", "SSM", "SSS", "STFE", "STFF", "STFH", "STFV")
however if Non delegated is selected then Provision IN("EQUI", "TRA", "PRU", "HOME", "CENN", "OOC", "PERI")
I have this working in Crystal Reports but cannot figure out how to get it working in SSRS without using stored procedures. I'm currently using SSRS 2005.
Please Help - i don't really want to have two separate reports when i can have one.
Many thanks
Kat
June 15, 2010 at 8:07 am
Hi,
You can do this simply in your sql query in the Design tab of your report.
You will need something like
Select * from Table_Cost_Types CT
where
CT.cost_type in
( case when @Parameter = 'Delegated' then
("B-P", "B-S", "C-P", "C-S", "DEBL", "MONP", "MONS", "PTFA", "PTFE", "PTFH", "PTFP", "PTFS", "PTFV", "SSM", "SSS", "STFE", "STFF", "STFH", "STFV")
when @parameter = 'Non-Delegated' then
("EQUI", "TRA", "PRU", "HOME", "CENN", "OOC", "PERI") end)
When you have created the above query click on the run icon and then type in the parameter value,
This should then run the report.
When you view the report the parameter should appear.
You will want to create the default values,
click on the menu item report > report parameters and then add the values in the available values section under non-queried.
Hope this helps.
June 29, 2010 at 3:19 am
Thank your so much. This really helps and speeds up my query.
Thanks
kat
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply