End-user who whishes to print Year info

rbuser

Hi all,

I got a database with the following tables `Relatie` ( this is where
information about the customers is stored ) and `deb_rek` this is basically
all the information about invoices ( totals, dates etc. )

Now does the customer want to print a report which show for all the
customers the total amount of sales ( forgive my bad english ) for this year
and last year ( if any )

He tried he failed, I tried I failed.

I only get one year to work, but I can't be able to make a 1on1 join.

If I would do this by program code I could do it easily, but I manage to
pull this one off with the report builder, I could sell the product !

hope anyone has a clue or tips or whatever.


Paul

nardmoseley

I think you need to create a query that uses the SQL Between operator:
Select deb_rek.Totals,
where deb_rek.Date BETWEEN '1/1/2001' and '12/31/2003'

You can do this via the RB Query Designer. On the Search page add the Data
field and then select the Between operator. Add the two dates separated by a
comma: 1/1/2001, 2/31/2003.



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Yes, but that would give me the totals of the two years. ( I might be
unclear about this )

But I want something in the style of

CustId. CustName Totals of year 1 Totals of year 2

Is this possible in one query ?

Paul

rbuser

Btw. I think the problem is that I only can use

`1 to many` or `many to 1` joins.

nardmoseley

Have you consider using a crosstab report? If your query result set looks
like this:

CustID CustName Year Total

Then you can create a crosstab report that summarizes the total information
by customer and year (i.e. in the style that you describe). When defining
the crosstab, you specify customer as a row dimension and year as a column
dimension.




--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Okay, got it working now.

However:

I find the options for the Crosstab somewhat limited.

I only can print one value ( in my example I would like to print and the
CustId and the CustName )
I would like to limit the rows process ( totals less then 1000 are not
interesting)
and
I would like to print the top100 customers.

any got an idea how I should deal with this.


Paul

nardmoseley

Other possible approaches that I can think of

1. Set Crosstab.Draw to False. Then the crosstab engine will build its
internal matrix but will not render it. You can access the matrix
programmatically and use the values in the report. There is extensive
documentation on the Crosstab in the RBuilder.hlp.

2. Another approach is to solve this on the data access side by writing some
pre-processing logic that can build a temp table that contains the
summarized information. Then build a query on the summary table inside RB.

3. You might be able to use the RB Query Designer to build multiple queries,
one for each year. Then link one of the queries to the other in a one to one
relationship so that that second query can used as a lookup to the first.
The report can traverse the first query, but you can use datafield values
from either query in the detail band.



--
Nard Moseley
Digital Metaphors
http://www.digital-metaphors.com