x

Report Builder 3.0 - Group Expression

Using Report Builder 3.0, I want to display a report that lists future months across the columns (start and finish month determined by parameters); Customers and related (drilldown) Products for the rows; and the projected sales amounts for each. The dataset fields include the customer, product, next due date, frequency (monthly, quarterly, half yearly or annually) and the amount of that future sale..

Firstly, I want to include the sale amount in a month column if that month (column) is the next due date of the sale - I have done this part using a matrix. Secondly, I now want to calculate all subsequent future due dates (based on next due date plus the number of months that represents the frequency, and so on until the finish month is reached) for each future sale and include those sale amounts in the relevant month columns.

Drilldown into customers to show/hide products is also required.

What is the best way to achieve this using Report Builder 3.0? Can I use the Matrix I have already built?

Bruce
more ▼

asked Nov 26, 2011 at 09:34 PM in Default

Bruce gravatar image

Bruce
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

You'll probably have to do most of the work in the query that supplies the data to Report Builder. It will probably involve a cross join to some form of tally table allowing you to create one row for each future payment at which point it should be possible to present the data with a simple matrix.

Updated with an example query, you may have to add some extras to convert the frequencies to numbers etc. but the principle will be the same: You will need to find your own code to create a tally table into #tally, unfortunately when I try and add mine the post refuses to submit.

create table #sales 
(sale_id int primary key
, Frequency int
,[Number of Payments] int
, [first payment] datetime)

INSERT #sales SELECT 1, 1, 5, '20110101'
INSERT #sales SELECT 2, 3, 3, '20110102'

SELECT s.sale_id
, dateadd(month,(t.N-1)*s.Frequency,s.[first payment]) AS [Payment Date]
, N as [Payment Number]
FROM #tally as t cross join #sales as s
where t.N <= s.[Number of Payments]

This gives you a row per payment with the correct date which should be easy to throw into a matrix

more ▼

answered Dec 05, 2011 at 07:23 AM

BarneyL gravatar image

BarneyL
62 1

(comments are locked)
10|1200 characters needed characters left

Yes I think you are correct. However, is anyone able to help me with the query, presumably a select query? The logic of what I am trying to achieve is outlined in the following steps:

For Each record in the dataset: Case Frequncy WHEN Monthly THEN FrequencyNo = 1 WHEN Quarterly THEN FrequencyNo = 3 WHEN Half Yearly THEN FrequencyNo = 6 WHEN Yearly THEN FrequencyNo = 12 END FrequencyIncrement = 1 While Month(NextDueDate) + (FrequencyNo * FrequencyIncrement) < MONTH(enddate) Name, Amount, Month(NextDueDate) + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueDate) + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueDate) + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueDate) + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueDate) + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueDate) + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueDate) + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueDate) + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueDate) + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueMonth + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueMonth + FrequencyNo * FrequencyIncrement Name, Amount, Month(NextDueMonth + FrequencyNo * FrequencyIncrement END WHILE

Notes: Startdate is a parameter entered by the user EndDate is a parameter entered by the user and must be < 12 months after StartDate Frequency can be monthly, Quarterly, Half Yearly or Yearly FrequencyNo = 1 for Monthly, 3 for Quarterly, 6 for Half Yearly and 12 for Yearly FrequencyIncrement is incremeted by one after each iteration in the while clause.
more ▼

answered Dec 05, 2011 at 03:36 AM

Bruce gravatar image

Bruce
11 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x22
x7

asked: Nov 26, 2011 at 09:34 PM

Seen: 1880 times

Last Updated: Nov 26, 2011 at 09:34 PM