| Author |
Message |
mak101
Newbie
Joined: 28 Nov 2011
Online Status: Offline
Posts: 13
|
   Posted: 07 Dec 2011 at 5:55am |
Now I removed client grand opening balance from table and just kept two regions opening balances and works fine for region ending balance and client ending balance. But how do I get grand opening balance for the client?
|
 IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 07 Dec 2011 at 7:50am |
when you insert that field onto the clinet level group header it will diospaly the the value fromt he first row of that data subset.
you can also use a maximum(field,groupfield) to display.
Most of my design was under the impression that you were bringing in the starting balances so they were a singluar value on every row of dat afor each cline, not null values.
|
|
IP Logged |
|
mak101
Newbie
Joined: 28 Nov 2011
Online Status: Offline
Posts: 13
|
Posted: 08 Dec 2011 at 5:55am |
OK I got the solution by creating two formulas. First formula stores only opening balance looking into type of transaction
Name: RegionOpBalance
IF {TrxTitle} = 'OPREG' THEN {Amount} ELSE 0
second sums RegionOpBalance for each client.
Name: ClientOpBalance
And I put ClientOpBal on client group header and works like a charm.
Now my last thing to do on the report is group by month. Should I create extra columns in stored procedure for Year, monthno and monthname or any easy way to use trxdate on crystal reports to group by month?
Thanks.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Dec 2011 at 6:30am |
you can group on the date field and in the grouping set it to 'for each month'.
easy and sorts accurately
|
|
IP Logged |
|
mak101
Newbie
Joined: 28 Nov 2011
Online Status: Offline
Posts: 13
|
Posted: 08 Dec 2011 at 6:35am |
|
Where do I change grouping set
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Dec 2011 at 6:50am |
when you insert the group it is in the GUI there at the bottom
or if the group already exists you can
select the group expert,
select the group you need to edit in the "Group By" window,
click on Options button,
the control is in this screen
Edited by DBlank - 08 Dec 2011 at 6:51am
|
|
IP Logged |
|
mak101
Newbie
Joined: 28 Nov 2011
Online Status: Offline
Posts: 13
|
Posted: 08 Dec 2011 at 7:36am |
I followed exactly as you mentioned, but I can't see this options. I am running this in visual studio 2008, would that make any difference?
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Dec 2011 at 7:43am |
|
is the field you grouped on an actual date (datetime) field or a string that looks like a date?
|
|
IP Logged |
|
mak101
Newbie
Joined: 28 Nov 2011
Online Status: Offline
Posts: 13
|
Posted: 08 Dec 2011 at 8:17am |
|
In my database it is date type, and it returns yyyy-mm-dd format. I tried to convert with convert(char(10),TrxDate,101), but that did not work so I set back to original format.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 08 Dec 2011 at 8:23am |
in crystal, in the field explorer, if you expand the Databse Fields and your source (table) what type appears next to the field name?
I am guessing it will show as a string.
You can convert the field to a date type in a formula field
date(table.field)
and then group on the formula field instead of the original string field.
once you do that the option in the group set up will appear. the only time that option shows up in the group set up is if the field you are grouping on is a date or datetime field type. It does ot make sense for anyother field type so it is removed.
|
|
IP Logged |
|
|
|
