Cross Tab - Show Values with multiple fields

[lissa1974]

Hi there, slight complex report I'm trying to create. Using Crystal 2008 with SQL. I am reporting off of one view, which makes this simple from a data point of view. What I have is this as lines of data.....

Company Name Product Revenue Month

Bob A £450 200907
Bob A £450 200908
Bob B £100 200907
Bob B £100 200908
Sid A £2000 201001

So, we have one line for each period of revenue. What I want to see grouped is.....

Company Product 200907 200908 etc...201001

Bob A £450 £450 £0
Bob B £100 £100 £0
Sid A £0 £0 £2000

Total £550 £550 £2000


So, it looks like a simple cross tab, using the period as the column header. This works well. However, I want all of the data to appear on the left as well (The company name and Product are two of several fields I also want to appear). It's to export into Excel, so I need a load of fields, and then on the end the finance information. It's almost like I need a standard report on the left side of the report listing all the fields and grouping on customer and product, but on the right a corosponding cross tab, but I need it on one line so the export to excel goes smoothly.

I obviously want to avoid creating formulas for each month, as I need to run this each month and so the first month would be the next months period.

Any help would be muchly appriciated.

Regards

 

[lbass]

If this is for export, I think you should use a manual crosstab. You can set it up so that the formulas calculate based on the date you are running the report. Is the Revenue Month field a number of string?

-LB

 

[lissa1974]

Hi there, thanks for the reply. I think a manual cross tab could be the way to go. Currently the date is a string (e.g. 200910, 200911, 200912)
So I can sort in to alphabetical and it would order correctly.

The problem I have, is that I have 12 lines for one item for each year, and some go until 2014. So across the top I'd need 60 months (formula's!!), and I need to run the report each month, so the dates would need to roll each month, making the formulas complex I'm guessing.

When I run the report it will always run from next month (being the starting date) until the very end date. Each item can have different end dates, so some might finish in 2009 Dec where as some might run until 2014, and as new items get added in the month this might extend beyond 2014.

Any ideas?

Thanks for your continued help.

 

[lbass]

I think you should still use a manual crosstab. You only have to write the formulas once if you set them up like this:

//{@currentmonth}:
if {table.RevenueMonth} = totext(currentdate,"yyyyMM") then
{table.amt}

//{@Month2}:
if {table.RevenueMonth} = totext(dateadd("m",1,currentdate),"yyyyMM") then
{table.amt}

//{@Month3}:
if {table.RevenueMonth} = totext(dateadd("m",2,currentdate),"yyyyMM") then
{table.amt}

//etc.

-LB

 

[lissa1974]

Hi there, it appears to work for the first formula, but the 2nd returns 0's. I think it's due to the period field being a number (200910 - actually a number field in DB), so how would I go about re-writing it to be a number formula, so it would need converting to a date to add the one month on?

Thanks

 

[lbass]

It wouldn't work for the first formula either if it is a number. And you wouldn't get zeros, you would get an error message, so I think it IS a string as you said in your first post.

Note that when a row doesn't meet the column criterion, a zero is returned. For a manual crosstab, you have to insert a group on some field and then insert summaries on the detail level formulas (like those I showed you) at the group level, and then suppress the detail section.

-LB

 

[lissa1974]

Hi there, the first formula did work, and the period field is a number. I did the grouping and the summed values, and for the first formula it works fine. This is the first formula....


//{@currentmonth}:
if totext({RPT_JXNARIS_ROLLOUT.Period}) = totext(currentdate,"yyyyMM") then
Sum ({RPT_JXNARIS_ROLLOUT.RevenueAmt}, {RPT_JXNARIS_ROLLOUT.Period})

2nd formula

//{@Month2}:
if totext({RPT_JXNARIS_ROLLOUT.Period}) = totext(dateadd("m",1,currentdate),"yyyyMM") then
Sum ({RPT_JXNARIS_ROLLOUT.RevenueAmt}, {RPT_JXNARIS_ROLLOUT.Period})

Am I right in thinking it won't work as it sees it as a string and not a number? As we span years would this also matter, as one item can have the records for....

200910
200911
200912
201001
201002

So would it know month 6 formula would be for 201001 etc...??

Your continued help on this is much appriciated.

Regards

 

[lbass]

You changed my formulas and your formulas are not appropriate for a manual crosstab. You could write them like this:

//{@currentmonth}:
if totext({RPT_JXNARIS_ROLLOUT.Period},0,"") = totext(currentdate,"yyyyMM") then
{RPT_JXNARIS_ROLLOUT.RevenueAmt}

2nd formula

//{@Month2}:
if totext({RPT_JXNARIS_ROLLOUT.Period},0,"") = totext(dateadd("m",1,currentdate),"yyyyMM") then
{RPT_JXNARIS_ROLLOUT.RevenueAmt}

You would place these in your detail section and then insert summaries on them at the group level. Your formula is just saying if the current row meets the criteria, show me the sum for all records, when what you really mean is: if the current row meets the criteria, return the amount (which then will be summarized) or else 0.

-LB

 

[lissa1974]

Hi there, that's worked a treat, thankyou for all of your help. The only query I have left is that if I wanted titles for those, how do I go about doing that. I'd need the format of the 200910 to move along with the formula's, so a formula for the headings?

 

[lbass]

//Month2Hdg:

totext(dateadd("m",1,currentdate),"yyyyMM")

-LB

 

[lissa1974]

Thanks again. Lastly (I hope), if I want to start it from a point in time rather than current date, so if I want the first month to be 200401 for instance, how do I amend the first formula to look for that date.

Once again thankyou for your support, I'm 95% there.

 

[lbass]

Replace currentdate with a date parameter.

-LB