Column Formatting

[mmwalker]

I was wondering if anyone has come across this problem.
We have a report in Crystal Reports 9 running from a SQL stored procedure. In the report there are multiple fields but we would like these fields to be user driven, so that when a user picks certain fields from a parameter only those fields show on the report. This is not just a supression issue though, as we would like the fields to be dynamically formatted so that there is not a blank space where there is a supressed field. I was thinking along the lines of if the field is not chosen then its width is set to 0 so the other fields move closer together.
Any ideas?

 

[morriskc]

Here is a thought...

Since you are using a stored proc, have the proc return result set be for col1, col2, col3, etc. The value of col1, col2, col3, etc. can be returned from the parameter(s).

The Crystal report would then contain col1, col2, col3, etc. which would probably have to be generic widths. this would eliminate the blank columns, but would not necessarily look pretty.

morriskc

 

[putts]

If you dont want to touch the SP, here's something that might work, tho it's not as dynamic as I would usually use.

Let's say you have 3 possible fields that the user could select to be displayed in the report (field1, field2 and field3).
In your report, create a parm to represent each one of these fields (@parm1, @parm2 and @parm3).

When you go to load the report, send true or false to each one of these parms in respect to what the user selected.

In your report, build two formula fields: showField and showValue.
The basis of each one of these fields will be to find if sift thru each parm checking for true values, verifying that value/field name has not been displayed yet and then display the proper one (or none at all).

Here's the code for showField:

stringvar shownFields; 'array to store shown field names
stringvar shownow := ""; 'name to show this time around

if {@parm1} = true then
'not for sure on how to check to see if in array but
' it should look something like this
if "field1" not in join(shownFields) then
shownfields := shownfields & " field1";
shownow := "field1";

if shownow = "" then
if {@parm2} = true then
if "field2" not in join(shownFields) then
shownfields := shownfields & " field2";
shownow := "field2";

if shownow = "" then
if {@parm3} = true then
if "field3" not in join(shownFields) then
shownfields := shownfields & " field3";
shownow := "field3";

shownow

For the showvalues field, you'd just replace what you put into the shownow variable with the field's value from the database rather than the field's name.

This code hasn't been used before and, like I said, is clunky because it's dependant on the number of fields, but I think it should work if nothing else comes your way.

 

[putts]

Forgot the semi-important part of that you have to then put the field onto your report for the number of fields that could be possibly shown.

So, in our example, your page header would like:
{@showFields} {@showFields} {@showFields}

And, the details like:
{@showValues} {@showValues} {@showValues}