SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Showing Period, YTD and Cumulative in one table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 02/10/2010 :  07:40:35  Show Profile  Reply with Quote
Hi folks

The 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

India
52325 Posts

Posted - 02/10/2010 :  08:44:29  Show Profile  Reply with Quote
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
Go to Top of Page

cidr
Posting Yak Master

United Kingdom
207 Posts

Posted - 02/10/2010 :  10:00:24  Show Profile  Reply with Quote
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?

Thanks
p
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/10/2010 :  10:19:21  Show Profile  Reply with Quote
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?

Thanks
p


yup they'll be report parameters declared inside report in parameters tab

------------------------------------------------------------------------------------------------------
SQL Server MVP
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000