Dynamic Reporting

[ker0k3n0]

Hello, I have a view table that contain 3 fields:

PROJECTID DISPLAYVAL RECVALUE
1 Airsealing TRUE
1 Attic insulation TRUE
1 Clothes washer FALSE
2 Airsealing FALSE
2 Attic insulation TRUE
2 Clothes washer FALSE
...
In my Crystal report (i use 2008), i would like to use the DISPLAYVAL as the heading and the RECVALUE would be the value. I would group them by Project ID and display the following:

PROJECTID Airsealing Attic insulation Clothes washer
1 TRUE TRUE FALSE
2 FALSE TRUE FALSE

It is like a cross-tab, but I need to join other view to display more fields so the cross tab would not work. Can you please teach me how to do that with formula?

Thank you in advance!
Phoebe

 

[lbass]

Are you sure you can use a crosstab? You could add other fields. What datatype is Recvalue? Is it a string or a boolean?

-LB

 

[ker0k3n0]

The datatype is Varchar(2).

 

[lbass]

I thought that meant it was a maximum of two characters in length--how would that return true or false? What datatype is shown if you browse the field (right click->browse)?

Also, I noticed your thread title mentions "dynamic" reporting, and a manual crosstab won't allow that the way an inserted crosstab would.

-LB

 

[ker0k3n0]

It is Varchar2(64) - it is only true or false. I guess my headings would be fixed not dynamic. Sorry for the confusion.

 

[lbass]

Insert a group on project ID, and then create one formula for each value of {table.displayval} like this:

//{@Airsealing}:
if {table.displayval} = "Airsealing" then
{table.recvalue}

//{@Attic insulation}:
if {table.displayval} = "Attic insulation" then
{table.recvalue}

If displayval can appear in different cases, use:

if ucase({table.displayval}) = "ATTIC INSULATION" then
{table.recvalue}

Place the formulas in the detail section and insert maximums on them at the group level. Drag the groupname into the group footer and then suppress the detail and group header sections.

-LB

 

[ker0k3n0]

Thank you so much LB you are the best!