Running Totals in manual crosstab 2

[cully651]

I'm using CR XIR2 and an Oracle DB with native connection.

I have a report that I created which is basically a manual crosstab. The report structure is very simple; there's just one group and one detail line.

I have it grouped by Project Type. On the top I have manually broken it into twelve columns, one for each month of a fiscal year. I've made 12 formulas that resolve the project numbers in each month as such (July as an example)

if 
({PROJECT.START_DATE} >= Date("7/1/" + {?FiscalYear})
and 
({PROJECT.START_DATE} < Date("8/1/" + {?FiscalYear}))
then 
{PROJECT.PROJECT_NUMBER}

On each detail line, one project number will show up under the corresponding month that it starts. Then they wanted an on-demand subreport for each project so they could see a lot of the details by clicking on the project number. That wasn't hard, either.

Now they want a count of the projects for each month. No matter what I do, a running total will count every detail line for every month, instead of just the ones that have projects that month.

Any help?? I just can't see the forest through the trees today.

 

[synapsevampire]

Where do you intend to place the summaries?
You put 90% of your effort describing what works, and one sentence describing what you want and where.

You can insert a crosstab underneath with the month as the column, project as the row, and a distinct count of the projects as the summary field.

Or you can write out 12 more formulas using your if clause to do it.

if
({PROJECT.START_DATE} >= Date("7/1/" + {?FiscalYear})
and
({PROJECT.START_DATE} < Date("8/1/" + {?FiscalYear}))
then
1
else
0

Now you can perform a sum against that formula.

-k

 

[lbass]

Or you could try a trick picked up from Ken Hamady. Create a formula {@Null} by going to formula->new-> and then saving without entering anything.

Then you could change each of your formulas to:

if
({PROJECT.START_DATE} >= Date("7/1/" + {?FiscalYear})
and
({PROJECT.START_DATE} < Date("8/1/" + {?FiscalYear}))
then
{PROJECT.PROJECT_NUMBER} else tonumber({@null})

Then you should be able to right click on this and insert a count or distinctcount and get the correct figure.

-LB

 

[cully651]

Thank you both for your responses!

lbass, that's a very interesting solution. Truly a great idea!