Report Builder 3.0 - Group Expression

Question

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

Answer 1

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.

Answer 2

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

Report Builder 3.0 - Group Expression

Follow this question

Related Questions