Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Question about Cross Tab

Status
Not open for further replies.

devnaren

Programmer
Aug 23, 2007
49
US
Hi All,

I created three different subreports in which I inserted cross-tabs. Formulas which I'm using in sub-reports are as follows:

subreport1:

Rows: <Name>

Columns: @Summ1

Summarized Fields: Percentage of Distinct Count of <ID>

@Summ1 =

If not({field.value} in ["a", "b", "Null"]) and {field1.Name} in ["aa", "ab", "ac"]
then
"Nm1"

Result:

1 2 3 4

Nm1 Nm1 Nm1 Nm1

x 4% 4% 5% 3%

y 4% 4% 5% 3%

z 4% 4% 5% 3%

Total 12% 12% 15% 9%


subreport2:

Rows: <Name>

Columns: @Summ2

Summarized Fields: Percentage of Distinct Count of <ID>

@Summ2 =

If not({field.value} in ["a", "b", "Null"]) and {field1.Name} in ["aa", "ab", "ac"] and
{field2.Name} = "Sample"
then
"Nm2"

Result:

1 2 3 4

Nm2 Nm2 Nm2 Nm2

x 3% 6% 5% 3%

y 4% 7% 5% 3%

z 5% 8% 5% 3%

Total 12% 21% 15% 9%


subreport3:

Rows: <Name>

Columns: @Summ3

Summarized Fields: Percentage of Distinct Count of <ID>

@Summ3 =

If not({field.value} in ["a", "b", "Null"]) and {field1.Name} in ["aa", "ab", "ac"] and
{field3.Name} like "A%"
then
"Nm3"

Result:

1 2 3 4

Nm3 Nm3 Nm3 Nm3

x 4% 4% 5% 3%

y 4% 4% 5% 3%

z 4% 4% 5% 3%

Total 12% 12% 15% 9%


Now I want to create a Summary Table for all above three subreports and it displays as follows:


Result:

1 2 3 4

Nm1 Nm2 Nm3 Nm1 Nm2 Nm3 Nm1 Nm2 Nm3 Nm1 Nm2 Nm3

x 4% 3% 4% 4% 6% 4% 5% 5% 5% 3% 3% 3%

y 4% 4% 4% 4% 7% 4% 5% 5% 5% 3% 3% 3%

z 4% 5% 4% 4% 8% 4% 5% 5% 5% 3% 3% 3%

GT 12% 12% 12% 12% 21% 12% 15% 15% 15% 15% 15% 15%


My question is how to include above three If Conditions in one formula.



 
If each crosstab has only one column field, I don't think you would get the results you are showing. I think you would instead get one column "Num1" and a second column "", so is there a column #1 that is actually resulting in the numbers 1-4 that you show?

-LB
 
No. there is no column which resulting in 1-4 qtrs. But tht i declared if the condition satisfies then it show up as "Nm1" ...........

So I'm trying to put three different conditions within one formula so tht it should combined all three subreport values into the summary table.




 
Please show how you were able to create the four columns, because each formula by itself would not have done so.

Combining the conditions would not be a problem for the last two formulas, but the first formula would INCLUDE the records that are needed in the last two formulas, which would require referencing the same rows twice. You could do this if you created a manual crosstab using running totals. However, you would not want to have to get the data from subreports, as this would be quite complex.

-LB
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top