Search code examples
crystal-reportstransposesubreport

Crystal Reports - Create subreport with column range [col1...col60] as datasource?

I am adept in both SQL and CR, but this is something I've never had to do.

In CR, I load a table that will always contain 1 record. There is a range of columns (like Column1, Column2 ... Column60). (bad design, I know, but I can't do anything to change that).

Thanks to this old design I have to manually add each column in my report like this:

-----------
|  TABLE  |
-----------
| Column1 |
| Column2 |
| Column3 |
| ...     |
-----------

Now I would like to be able to create a subreport and create a datasource for it in such a way that [Column1...Column60] becomes a collection [Row1...Row60]. I want to be able to use the detailsection of the subreport to dynamically generate the table. That would save me a lot of time.

Is there any way to do that? Maybe a different approach to what I had in mind?

Edit

@Siva: I'll describe it the best way I can. The table exists out of 500+ columns and will only hold 1 record (never more). Because normalization was never taken into account when creating these tables (Objective C / DBF ages) columns like these: Brand01,Brand02,Brand03...Brand60 should have been placed in a separate table named "Brands"

The document itself is pretty straight forward considering there's only one record. But some columns have to be pivoted (stacked vertically) and placed in a table layout on the document which is a lot of work if you have to do it manually. That's why I wanted to feed a range of columns into my subreport so I can use the detail section of my subreport to generate the table layout automatically.

Solution

  • Ok got it... I will try to answer to the extent possible...

    you need to have 2 columns in report that will show the 60 column names as 60 rows as 1st column and 60 column data as 2nd column. For this there are two ways that I can think of.

    1. if columns are static and report need to be developed only once then though its a tough job manually create 120 formulas 60 for row names where you will write column names and 60 for data for respective columns and place in report since you have only one record you will get correct data. Like below:

    formula 1:

    column1 name // write manually

    Formula 1:

    databasefield for column1 // this has data for column1

    Above will be one row in report like this you will get 120 formulas 60 rows and you don't need sub report here main report will do the job.

    1. Since you are expecting dynamic behavior (Though columns are static), you can create view from database perspective or datatable (Please note I have no idea on datatable use it as per your convinience).

    Create in such a way that it has 2 columns in table and in report use cross tab that will give you dynamic behaviour.

    In cross tab column1 will be rows part and column 2 will be data.

    Here also I don't see any requirement for sub report you can directly use main report. If you want sub report you can use aswell no harm since you have only 1 record