Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 643
  • Last Modified:

Dynamic table in Crystal Reports XI

This is the problem...

I need for CR to display a table in dynamic layout. Idealy I would use Stored Procedure that would return data. Table field headings would be equal to SP fields (they will change), amount of table colums will change as well.

As far as I know - CR does not directly support such functionality.

Alternativellly, I would have to build CR Report from script/code, give it current data, build table, run report and .... all at runtime.

It has to work with Classic ASS and IE. CR XI

I have tried to build a report in such way but could not get it working all the way...

I can add TextFields to report, but not the FieldObjects (could not bind them to data or formula fileds(formula filds work)).

Once again, it can get it working in VB but not in asp. And I cannot use .NET

Most of all, I am looking for a way to get such table in a report. Any Ideas, samples or ways?

Please, do not give links to http://support.businessobjects.com/...  CR Support site has changed and all old links do not work... )

Thanks to all
0
rreimund
Asked:
rreimund
  • 6
  • 3
1 Solution
 
dro_lawCommented:
the problem is that Crystal will define the report based on the defaul output it gets from teh sp. So, if you create a report based on your SP, then all the columns will reflect the default ouput. So, if you have two differing outputs in the sp, the report will die if you try to run the second output.

To get around this:

1. Make your SP with parameters that will determine the output.
2. Use a subreport for each separate combination of parameters. Each subreport will show the output/columns for those parameters only.

For example (this is SQL server)

create procedure test_report
@par1 int,
@par2 int
as
begin

If @par1 = 1 and @par2 = 1 then
    begin
       select name, id from sysobjects
    end

If @par2 = 2 and @par2 = 2 then
    begin
        select name, getdate() [todays date] from syscolumns
    end
end

Then make a report with two subreports. The first one passing 1,1 for the parameters, teh second passing 2,2. You will see the column definition changing for each of the outputs. In the first report, your columns will be name and id, in the second they will be name and date.

That's the simple way to do it. The other way to do it, is to define a default output that has the same number of columns with the same alias names. That way the SP will always output the same number of columns. The difficult part about that is getting the column names, but you could always pass that as the first row of the data set and just parse it out in the report.

Anyway, hope that helps.
0
 
rreimundAuthor Commented:
The biggest problem is that I have so many variations of data returned....  I will not know the amount of colomns returned - that is the big thing... sp can return from 1 to 15 colomns (different every time)
0
 
dro_lawCommented:
In that case, I would use a work table and populate it with your SP. So, create a table with 15 columns in it, called column1,column2, etc. Then use your sp to populate that table. Then report from the table.

Your only problem will be column names. You could try passin that as the first row of your data set, or creating a separte table to hold column names. Then you just report on those fields as your column header.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dro_lawCommented:
Oh, and you will need to include supression formulas for each field when they are null.
0
 
rreimundAuthor Commented:
As an option, but then SP has to bring the same amount of coloms every time + there is a problem with layout - the table should look nice every time... )
0
 
dro_lawCommented:
No, the sp can bring back a variable number of columns because you don't have to populate all of the columns in the table every time. If you then use supression formula to hide the fields that are null in the various outputs, you should be able to format the report with a professional look since the end user will never see the null fields.

So, to clarify:

SP inserts into work table-->Table is populated-->Report runs from table --> All null fields are supressed in the report.
0
 
rreimundAuthor Commented:
But then how can I programatically, in the report, not only supress the fields but to modify their width.
So that 5 or 15 (for excample) fields would form a table (100% width). - As I would have to reasign position and width of not supressed fields.
This is CR XI.
0
 
dro_lawCommented:
Put them all together in a text field and crystal will dynamically set the spacing for you.
0
 
dro_lawCommented:
Sorry, I just read your comment again. I didn't see the part about the table being 100% width. In that case, you may be better of just making separate reports. The only way I can think of do to that is setting fixed width on the fields depending on length. That's possible, but getting them to be a 100% wide table every time would be time consuming and a bit of a hack. Does the table have to be 100% every time?
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now