Annual Report by Month

rbuser

Looking for some direction as to the right approach here.
Using RB 10 Enterprise.

I have a table with a date field, and I need to summarize the month end
values. Ultimately I want to present the data in the report in the
following way:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
New 22 12 16 11 15 12 14 12 18 12 19 15
Refi 25 19 17 10 11 14 16 17 18 11 12 11

I can use Dade to setup the SQL something like the following:

Select RepDate, Extract(Month from RepDate) as "Month" from "EodSum",
New, Refi
Order by "Month" Desc, RepDate Desc

I get something like the following returned:

RepDate Month New Refi
12/31/2009 12 15 11
12/30/2009 12 11 10
...
11/30/2009 11 19 12
11/29/2009 11 16 11

I really only need to extract records for the end of each month because
the records contain Month-To-Date values within them already. So my
first question is whether there is an easy way to have only the first
record for each month group returned? If not, then how do I filter the
group within the report?

My second question concerns displaying the final results. Do I switch
Visible to false for the detail band and set everything up in the
summary band? Or is this a situation where I should consider the
CrossTab component, which is something I have never used before.

nicocizik

Hi David,

To get the data you are after with your design, you will need to group by
each month and select the max value for the New and Refi fields. Something
like the following...

Select Max(New), Max(Refi), Month
From EodSum
Group By Month
Order By Month

Since you will have a static number of columns in your output (12), you
could use a columnar report set to traverse data LeftToRight. This setting
can be found on the DetailBand.

Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Oh, yeah, I forgot about the columnar report. That helps a lot.

Not sure if the max thing will work. I gave example to keep it simple,
but I actually have hundreds of columns. Lot of work to max all those,
and not sure how that would work. I might play with it, but thinking
of editing SQL and doing subquery selection.

Thanks very much, Nico.

rbuser

Hi Nico.

I figured out the SQL to get the last record of each month, but I am
stumped on getting the row labels on the left hand side of the report.

Basically my data has 12 records which list across the page by using
the left to right column layout. I want it to look like the following:

Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
New 22 12 16 11 15 12 14 12 18 12 19 15
Refi 25 19 17 10 11 14 16 17 18 11 12 11

I have thought of three approaches to getting the row text labels in
there, but I don't know how to do any of them:

1) Can I skip printing data in the first column so I can put 13 columns
total and put my labels in column 1?

2) or Can I put labels in the left hand margin and just make the left
margin big enough to accommodate the labels?

3) or Can I somehow put spacing on the left hand side before the
columns start that lets me put labels in there?

I am using RB Version 10, but will upgrade to Version 12 if Version 12
makes this easy. Please let me know.

In any case, please direct me toward a solution.
I thank you very much in advance.

rbuser

p.s. Would using a crosstab be an approach even though I am not
summarizing data? I have not used crosstab before, so I'm not sure if
this would work.

rbuser

Hi

I have probably misunderstood the question, if so please ignore the
following....

For something similar I used a 2nd Table which has the following fields

LINE_NO MONTH_DESCR
1 JAN
2 FEB
3 MAR etc.

In my fields selection I linked MONTH_DESCR to the corresponding field my
main table and then sorted the report by LINE_NO

When I configured the Cross Tab I used LINE_NO as my first column and then
everything else fell into place.

Hope this helps

PLJ

rbuser

Philip L Jackson wrote:


Hmm. Not sure this is what I need because I have no experience using
crosstab, but I will look into it. Thanks for sharing.