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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

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

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.
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)
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.
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Oh, and you will need to include supression formulas for each field when they are null.
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... )
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.
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.
Put them all together in a text field and crystal will dynamically set the spacing for you.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.