Dynamic table in Crystal Reports XI

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.

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)

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.

Oh, and you will need to include supression formulas for each field when they are null.

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... )

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.

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.

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?