Hi,

In my opinion, i think there may not be a solution to my problem, but I want to pose my question up here to see if anyone can give me some idea, and i am willing to jump through hoops to come to a complete solution.

Report
Japanese Yen    1000.00   2000.00
Total Notes               5            100

Mexico Peso      10.00         20.00      50.00      100.00     200.00
Total Notes        320            150         200           450          200
  
US Dollars          10.00         20.00      50.00      100.00      500.00
Total Notes         451            321         233           590         42

Here's the problem, the report prints out a summary report for a financial company showing number of notes in each denomination category. Ex. The company carries three different currencies, Japanese Yen, Mexico Peso, and US Dollars. Currently, they only have 5 of 1000 Yen and 100 of 2000 Yen, 320 of 10.00 Peso and 150 of 20.00 Peso... etc, 451 of 10.00 and 321 of 20.00 USD etc...  The user printing out the report can clearly get a sense of how much money the company have in storage.

The database table that bind to the report looks like as follow

"Japanese Yen"      5          100
"Mexico Peso"         320      150      200     450    200
"US Dollars"            451      321      233      590   42

So, when the report is binded with the table at run-time, the report engine will loop through record by record and display it accordingly. Also, the report is group by currency. The report that I produced is half done, because I can't think of a way to include the denomination title for each currency....My report look like as follow, compare with the report above

Report
Japanese Yen   
Total Notes               5            100

Mexico Peso     
Total Notes        320            150         200           450          200
  
US Dollars       
Total Notes         451            321         233           590         42

Hi Hilfy,

The fields the report is currently reading are as such


CREATE TABLE [dbo].[invfmt] (
    [currency] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [den01] [decimal](14, 2) NULL ,
    [den02] [decimal](14, 2) NULL ,
    [den03] [decimal](14, 2) NULL ,
    [den04] [decimal](14, 2) NULL ,
    [den05] [decimal](14, 2) NULL ,
    [den06] [decimal](14, 2) NULL ,
    [den07] [decimal](14, 2) NULL ,
    [den08] [decimal](14, 2) NULL ,
    [den09] [decimal](14, 2) NULL ,
    [den10] [decimal](14, 2) NULL ,
    [den11] [decimal](14, 2) NULL ,
    [den12] [decimal](14, 2) NULL ,
    [den13] [decimal](14, 2) NULL ,
    [den14] [decimal](14, 2) NULL ,
    [den15] [decimal](14, 2) NULL ,
    [den16] [decimal](14, 2) NULL ,
    [den17] [decimal](14, 2) NULL ,
    [den18] [decimal](14, 2) NULL ,
    [den19] [decimal](14, 2) NULL ,
    [den20] [decimal](14, 2) NULL ,
    [den21] [decimal](14, 2) NULL ,
    [den22] [decimal](14, 2) NULL ,
    [den23] [decimal](14, 2) NULL ,
    [den24] [decimal](14, 2) NULL ,
    [den25] [decimal](14, 2) NULL ,
   
) ON [PRIMARY]
GO


denxx are denominations that report is reading, whenever there's a null value, the report supress it to be display. The max number of denomination belongs to a currency is 25. Usually, the number of denomination is around 8. This is not important but a good fact to know. When I generate the report, sql statement pulls out fields i need and put it into an array. Each element in that array provide information about a currency and its denomination, and to answer your second question, it includes value and type of note those number refers to.

I would like to hear your suggestion. Thanks.

Yes, you are absolutely right on.  Let's fabricate an example of 2 table records


Currency                    Den01      Den02       Den03     ...... Denxx
Canadian Dollars       642            50               300      ......  xxx
United Dollars            23               34               9         ......  xxx


You will notice that each record shows the denomination for a specific type of currency. The note count for each denomination is there as well. What's missing in the table are the face values of each denomination. Every country determines the denomination for their own currency. Refer to what i said before, i have an array that has the missing piece of information and that is the denomination for each currency. I was thinking the column title for each currency with an array. Picture the report being group by currency, the report will make a group for each record from the table. And I really would like to include the column title for every group.

do you mean linking two tables and bind both to the report? I can certainly change the table structure if it works. Can you elaborate more on your idea...

I think i know the way your idea works, but please correct me if i am wrong. I think you want me to join two tables together, denomination table and currency table linking by currency field.  It will result in a dataset with all those fields.

Now the thing which you probably know but i am not so sure of how to do.. the report groups the record by currency. How would i create column title for each group, what i mean by that, is how to create dynamic column title for a group, each group has different column titles

ex.

Japanese Yen         1000         2000
                                  45            34


United States Dollars      5       20         50      100  
                                       23      343      123      989