Detail-Data in Cross-Tab

[Former Member]

Hi,

i would like to add additonal data to the row-label in a cross-tab.

....................... C1 C2 C3

Customer1

.... add.Data1

Customer2

.... add.Data2

Customer3

.... add.Data3

How can i do that?

The link between the Customer-Table and the Extra-Data is the customer-id and a Date-Field (parameter of the report - used for filtering on a date-basis).

Table for extra data:

customer-id date amount

DATE is a parameter of the report.

Is there any way to do this?

(I use CR XI).

Result should look like this:

-

---

Selected Date (Parameter): 2009-02-02

...................................................... C1 C2 C3

Customer1 +

....amount on 2009-02-02 for C1

Customer2 +

....amount on 2009-02-02 for C2

Customer3 +

....amount on 2009-02-02 for C3

-

---

THX in advance,

Markus

[Former Member · Accepted solution]

Hi Markus,

Go in the the row fieldRight click on the fieldCommon TabDisplay strinGx-2 (formula editor) and write the following formula :

"....amount on"{?DateParameter}"for C1"+Currentfieldvalue;

This will insert your date parameter and also text.

Thanks,

Sastry

[Former Member · Accepted solution]

Hi,

the answer you suggest will display the text "amount on"...

Sorry if i phrased my question wrong, but i need the AMOUNT for the currenct customer from the second table (which stores only the amounts)

NOT the text "amount"

something like "Select b.amount from Amount_Table as b where b.id = a.customerID and b.date={@parameterDate}".

THX

Markus

[Former Member · Accepted solution]

Hi Markus,

Than place the field in place of text like :

{?DateParameter}"for C1"+Currentfieldvalue;

Before doing the above you place the table in your repot and join the table with other tables which is already in your report.

Thanks,

Sastry

[Former Member · Accepted solution]

Hi,

Lets phrase it differently again

i have a second table from which i would like to add additional information to the row-information in a cross-tab.

customer.... date ... amount

Cust1....2009-02-01.... 10

Cust1....2009-03-01.... 15

Cust1....2009-04-01.... 77

Cust2 ........

Cust3 ........

I need ONLY the first amount from the starting-date in the CROSS-Tab.

The starting-date is a parameter in the report.

If i put there the linked amount-field i get a row for each different date.

I need only one row per customer with the first amount from date 1. (the parameter-date-value)

So if i enter 2009-03-01 as the parameter, i would like a cross tab with a row-name of:

"Customer1 + ChrW(13) + 15 "

It works with a SQL-Expression (named "StartSaldo") like:

(Select Saldo from Goldbeck_Saldo where D_Date = '20090301'

and "Goldbeck_Saldo"."I_PERSID"="BaseTable"."I_PERSID")

and a row name of "Customer.Name + ToText({%StartSaldoSQL})

However the Date "20090201" is hard-coded there.

I can find no way to make the Date from the SQL-Expression soft-coded (pass the parameter to the scalar SQL-Expression). According to another forum-post this is not possible.

THX

Markus

Edited by: Cumulus7 on Apr 27, 2009 4:25 PM

[Former Member · Accepted solution]

Another way to achieve my wished result would be to insert a sub-report into the row-name-field of my cross-tab.

But i cant seem to achieve that either....

CU

Markus

[Former Member · Accepted solution]

I made a picture to show what i need.

Any way is appreciated.

[http://www.markus-gayda.de/cross-tab-problem.jpg]

Table1 generates the cross tab.

Table2 provides the extra information needed (in red), defined by the green parameter starting date.

CU

Markus

Edited by: Cumulus7 on Apr 28, 2009 8:46 AM

Edited by: Cumulus7 on Apr 28, 2009 8:47 AM

[Former Member · Accepted solution]

Hi Markus,

Thanks for your excel screen capture.

I have prepared a tables as per your excel screen capture and I could able to extract the required information in cross tab.

Please find below are the steps :

--Add table1 and table2 to your report.

--Make join as Inner on Customer

--Open cross tab and place

Table1.date on Columns:

Table1.Customer, Table2.Amount on Rows

Table1.Value on Summarized fields

Go in Customize StyleRows--select table1.customer and suppress subtotal.

--Remove check boxes from column totals on top, row totals on left, Repeat row labels.

--Check Suppress Row Grand Total, Suppress Column Grand Totals.

--Go in Record Selection and Table2.date > Startdate

Now you will get the desired output.

Thanks,

Sastry

[Former Member · Accepted solution]

Thanks for the solution Sastry!

It works (but only for Customers that actually HAVE an amount available).

I found another way to solve this problem by programming a stored procedure that accepts the ?StartDate Parameter as input and gives me a filled table with a NULL amount even for customers that dont have one.

But your solution is much more simple if this does not occur.

(I have to check back with MY customer to see if this can actually be the case)

Very helpfull support, THX

Markus