creating Manual cross tab

[raj0927]

Hi,
I want to build the manual cross tab, The data in DB looks like below
Date test result
09/07/2010 test1 3.5
10/26/2010 test1 4.1
08/31/2010 test2 8.4
09/28/2010 test2 8.1
10/19/2010 test2 8.9

I want the report in the below format

tests 10/2010 09/2010 08/2010
test1 4.1 3.5 0
test2 8.9 8.1 8.4
I cant use the manual cross tab since i have add icons and text objects as columns to these reports
Is there any way to work around this?

 

[lbass]

You mean you can't use an inserted crosstab? Not sure why not. You could just insert a crosstab, add test as the row, date as the column (on change of month), and then result as the summary field.

For a manual crosstab, you would have to insert a group on test, and then create one conditional formula per column, e.g.,:

//{@lastmonth}:
if {table.date} in lastfullmonth then
{table.result}

//{@twomonthsago}:
if {table.date} in dateserial(year(currentdate),month(currentdate)-2,1) to
dateserial(year(currentdate), month(currentdate)-1,1)-1 then
{table.result}

//{@threemonthsago:
if {table.date} in dateserial(year(currentdate),month(currentdate)-3,1) to
dateserial(year(currentdate), month(currentdate)-2,1)-1 then
{table.result}

Place these in the detail section and then right click on each and insert a summary->sum(?) at the test group level. Drag the groupname into the group footer, and then suppress the detail section and groupheader.

-LB

 

[raj0927]

I cant use the crosstab options since i have to add extra columns to the report like text,icons

 

[lbass]

Where specifically are the text and icons going?

I did also show above how to do a manual crosstab. Text and icons wouldn't be a barrier for that.

-LB

 

[raj0927]

The text and icons go just beside the dates as the columns

 

[lbass]

So you are all set now, using the manual crosstab approach, correct?

-LB

 

[raj0927]

i am having an issue in here, in the monthly result there are couple of results but i need to consider only the latest result for that particular month.
I have grouped on the test date(monthly) but unable to show the latest result for that month.

 

[raj0927]

can i create a array or variable where i can store the latest values for each month and display those monthly values in the coulmns

 

[lbass]

Do you mean the latest result per test per month? Your sample showed only one result per test per month.

-LB

 

[raj0927]

yep its latest result per test per month