Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2010-02-10 : 07:40:35
|
| Hi folksThe report I'm working on fairly complex and is for financial job costing.The report must show three seperate columns for costs. These columns are Period (which is the costs for whichever period is selected for that month), the yeatToDate(i.e. from Jan to Date) and Cumulative (being from the start of the project which could be any date in the past)There will be several drop downs for this report, but specifically a period drop down list. Lets say the period of Dec 2009 is selected. The report should display only Dec 2009 in the Period column, all data from Jan 2010 to date in the YTD column. And, if the project for which the costs relate to, started on Feb 2009, then it should show all costs from Feb 2009 to date for the Cumulative column.is there any way to do this so that the parameter will only effect the one column (i.e. period)?This has puzzuled me for some time so any help will help a lot!Thankyou |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-10 : 08:44:29
|
| i think what you need to do is to add an expression for grouping property of report based on parameter and then provide corresponding aggregate functions for data columns to get cummulative sum,YTD, etc------------------------------------------------------------------------------------------------------SQL Server MVP |
 |
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-02-10 : 10:00:24
|
| Hi visakh,The report's quite complex with lots of data and groups. I was thinking of using a correlated subquery with date parameters, only for the subquery. I imagine that would work as well. Really, it's whatever's faster. I take it when you refer to parameters, that the parameters would only be report parameters for the expression and wouldn't be declared within the query itself?Thanksp |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-02-10 : 10:19:21
|
quote:
Originally posted by cidr
Hi visakh,The report's quite complex with lots of data and groups. I was thinking of using a correlated subquery with date parameters, only for the subquery. I imagine that would work as well. Really, it's whatever's faster. I take it when you refer to parameters, that the parameters would only be report parameters for the expression and wouldn't be declared within the query itself?Thanksp
yup they'll be report parameters declared inside report in parameters tab------------------------------------------------------------------------------------------------------SQL Server MVP |
|
|
|
|
|
|
|
|
Showing Period, YTD and Cumulative in one table