CrossTab Report Query

[Former Member]

Hello,

I am trying to set up a crosstab report using the sample data below but want to avoid it counting the records below 14 times. For example if I insert the Int.CallType field into the crosstab, it will display 14 records but this is supposed to be just 2.

The problem lies with the decision table where 1 field (Decision.RuleName) has multiple values.

Customer.ID | Interaction.Number | Decision.RuleName | Decision.Decision | Int.CallType

1 | 455 | CoverageCheck | Accept | New

1 | 455 | SecurityCheck | Declined | New

1 | 455 | ExcludedCheck | Accept | New

1 | 455 | BureauCheck | Accept | New

1 | 455 | PreviousCustomerCheck | Declined | New

1 | 455 | LocationCheck | Accept | New

1 | 455 | ScoreCheck | Accept | New

2 | 456 | CoverageCheck | Accept | New

2 | 456 | SecurityCheck | Declined | New

2 | 456 | ExcludedCheck | Accept | New

2 | 456 | BureauCheck | Accept | New

2 | 456 | PreviousCustomerCheck | Declined | New

2 | 456 | LocationCheck | Accept | New

2 | 456 | ScoreCheck | Accept | New

If I create a detail report and group by Customer.ID the report picks and displays a seemingly random rule name and if I add a formula field (if Decision.rulename = "SecurityCheck" and Decision.Decision = "Declined" then "Declined") it remains blank for some of the records. If I remove the grouping the report is visible as per example above including the formula field result. This however will display the data as per sample above.

Any help would be appreciated.

Thanks,

WA

[Former Member · Accepted solution]

Wasim,

What Database are you using?

If you are trying to retrieve the latest 14 records from the database,

If Oracle then use rownum keyword in the where clause, for ex:

Select field1, field2,.... from table_A where rownum <= 14

and if SQL then top keyword, for ex:

Select top 14

I believe that I have understood your question correctly, if not please elobrate on this.

Thanks

-Azhar

[Former Member · Accepted solution]

We are using an SQL database.

I dont think the question has been understood. Within the crosstab I want to show the number of Int.CallType of "new" as 2, whereas it currently shows as 14. If I change the summary to a distinctcount it states 1.

Thanks,

WA

[Former Member · Accepted solution]

>>The problem lies with the decision table where 1 field (Decision.RuleName) has multiple values.

That is your answer..

otherwise you can have two groups, first on customerID and the second one is RuleName.

Hope this helps,

Thanks

-Azhar

Edited by: Abdul Rehman Mushtaque on Jun 2, 2009 1:07 PM

[Former Member · Accepted solution]

Where should I be grouping, within the crosstab or main report design?

If I group within the crosstab using the customer ID in the row colum, the report displays all the values down the left hand side whereas I want the total just like the format below:

campaign1 campaign2 campaign3 (campaign.name)

New (int.calltyp) 76 56 67

Further (int.calltyp) 56 12 34

coveragedeclines (dec.rulename) 34 20 34

securitydeclines (dec.rulename) 24 20 37

lead (int.callstatus) 56 28 30

On my existing cross tab report I insert the campaign field in the column box, nothing in the row, and int.calltype in the summary. As a default it will set the summary as a count, but multi count for each rule name which is the problem I am experiencing. I want it to count a calltype of new as 1 for each customerid record, not the 7 for each rulename. If I change the summary to a distinct count, it doesnt work as the count is too low.

How can I achieve this within a crosstab. It is possible to do this using a pivot table in excel so hope it can also be done in crystal.

Thanks,

WA