Dear all, I've got a reporting problem that I'm unsure how to solve. We have a table that has Policy Name, Commencement Date, Expiration Date, Uploaded Date, Amount. The customer wants to display this in a grid: May 2011 June 2011 July 2011 ... Apr 2012 5 10 10 ... May 2012 3 10 ... So the date the policy was uploaded would appear in the row "Apr 2012, May 2012...". For each month the policy is active (between commencement and expiration) the amount would be split up and assigned to a cell in "May 2011, June 2011...". If the month started or finished that particular month then a partial amount is calculated for the day it started. For example if there's a policy of 25 that was uploaded Apr 2012 and commences 15 May 2011 to 31 Jul 2011 then it would appear with 5 in May and 10 in June and 10 July 2011. (The amounts aren't actually right but hopefully you get the picture). Now I don't believe this can be done with the current data structure through Reporting Services - is that correct? I think I would have to create a new table/database that would be built up every night, say, that split each policy into its monthly amounts. This new table would be used for the report. What do you think, is this the best way? Any other alternatives? Or could Analysis Services do this task without the intermediatary table? Thanks for any advice you can give. Kind regards Sidharth

It is hard to suggest something useful without knowing the logic behind the scene and not seeing sample data for testing.Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Hi Uri, Thanks for your reply. Well the logic behind it is that the money is received when the policy is bought but this "earnings grid" is required for accounting purposes in case there's a claim at any point during the policy. The uploaded date does not reflect the policy commencement/expiration date so a policy could be uploaded well in advance (or after) the policy ever commences. I hope that provides some reasoning for this report? Kind regards Sidharth

Hi Sidharth, At run time, as the report data and data regions are combined, a matrix grows horizontally and vertically on the page. In your case, I think that you can try to use a matrix to design the report. In addition, you can use the following expression to get the expected date format. Please see: =Format(CDate(Fields!Date.Value), "MMMM yyyy") Here is an article about adding a matrix for your reference, please see: http://technet.microsoft.com/en-us/library/ms157334(v=sql.100).aspx If I have something misunderstood, please point out and post some sample data for testing. Regards, Bin LongBin Long TechNet Community Support