group by on Monthly from dailiy in Crystal

[karnd]

Hi,

I have the following aspect.

My mart has populated the daily data but the report has to be based on Monthly ie they are monthly reports in that case,how it would be possible in Crystal to roll up to get the Monthly data from daily.

Could you please throw some ideas!

Regards,

 

[synapsevampire]

This depends upon how your data mart is populated, of course.

If there are dates stored, then insert a group based on the date, and then select For Each Month in the Section Will be Printed by.

Now you can create standard Crystal formulas to roll up the data at that level.

-k

 

[karnd]

Hi,

Appreciates of your quick reply.

But i have to decide in such a way that with daily data so the Balance which involved in the report has to do based on this formula = (ActualBalance+credit-debit)
But,the Credit and debit values has to be rolled up to first 15 days(since it is Monthly report) and the ActualBalance has to be rolledup to the Month.

How should i achieve of the above scenario in Crystal.

I am stuck with this, could you please throw some tips.

Note:Is it easy to solve the above scenario if i supposed to have the Monthly Data sothat i can rolled down upto 15days from it(i dont think so , do you please!)

Thanks a lot,
Regards,

 

[synapsevampire]

Sorry, you lost me.

Try posting technical information:

Crystal version
Database/connectivity used
Example data
Expected output

Let the example dtaa demonstrate the different types of transactions, and show the intended output.

The language barrier makes your posting of descriptions complicated.

-k

 

[karnd]

Hi,

I am sorry about that.
The Actual DataMart table is:
=============================================
Date DeptName ActualBalance Credit Debit
=============================================
01-01-2005 Accounting $10000 $400 $200
01-02-2005 Accounting $2000 $100 $200
----
----
----
01-15-2005 Accounting -$1000 $100 $100
---
---
01-31-2005 Accounting $2000 $500 $100

and continues the data for the whole year='2005'

so at the report level when the prompt selected for the value = January,2005 then the report has to be displayed the rolledup of the above records and displays for the BeginBalance(Jan,2005)=ActualBeginBalance(rolledup value)+Credit(rolledup value)-debit(rolledup value).

I hope you got my point.

The Crstal Version is XI
DB Connectivity is ODBC(RDO) for SQL Server2000

Please let me know if it is not clear.

Thanks a lot
Regards,

 

[karnd]

Hi,

There was a small correction about the ActualBeginBalance data on each record

=============================================
Date DeptName ActualBalance Credit Debit
=============================================
01-01-2005 Accounting $10000 $400 $200
01-02-2005 Accounting $9800 $100 $200
----
----
----
01-15-2005 Accounting -$7000 $100 $100
---
---
01-31-2005 Accounting $1000 $500 $100

but the criteria for the BeginBalance=ActualBalance(on 01-01-2005)+Sum(Credit from 01-01-2005 to 15-01-2005)-Sum(Withdrawal for 01-01-2005 to 15-01-2005)

So it prompts for the Jan,2005 then BeginBalance calculates based onthe above formula.


Please let me know if it is not clear.

Thanks,
Regards,

 

[Madawc]

Just to check, you want to group by month, but each individual day has to have a calculation that involves just the first 15 days of that month?

If that's so, then I can't see any choice except a subreport in the header of the month-group, to find the figure for the first 15 days. A running total for the month could accumulate for the first 15 days, but would not be valid until the 15th day. The same applies to variables. Summary totals are available from the start of the group, but only select according to group and could not select just the first 15 days. (They could be used to find the whole month, and then a formula field could halve the figure, if that's acceptable.)

If a subreport in the month-group header is the only choice - and it will slow the report considerably - it would pass the value back using a shared variable. E.g.:

whileprintingrecords;
shared currencyvar SumSaved;
SumSaved:={#TotSaved};
SumSaved

And to access it in the main report, create another formula field with

whileprintingrecords;
shared currencyvar SumSaved;
SumSaved

Note that the shared variable is only available in the section after the section which contains the subreport. Since you appear to want it for individual days, that should be OK.

Madawc Williams (East Anglia, UK)

 

[lbass]

To accumulate the values from the first 15 days, you could also use a formula like {@inday1to15}:

if day({table.date}) in 1 to 15 then {table.amt}

Then you could use the following as part of another formula anywhere in the group:

sum({{@inday1to15},{table.group})

-LB

 

[karnd]

Hi,

It works,
Thanks a lot,
Regards,