Show Previous Balance value in Report.

[Former Member]

Hi,

I'm using Crystal Reports with Visual Studio 2010. I'm generating a report which shows columns Debit and Credit and SUM values on the buttom of each column. The report is filtered using a date range, which I specify in my connection via C# code (below).

            string sqlString = "";

            sqlString = "SELECT * FROM Ledger WHERE ([Payment Date] >= #" + FromDate + "#) AND ([Payment Date] <= #" + ToDate + "#)";

Now I need a "Previous Balance" value, which would be the SUM of Credit and Debit columns, which are prior to "Payment Date".  How do I accomplish this?  I've attached a sample of the report I'm looking for. Please be specific with your reply, because I'm new to Crystal Reports.

Thank you so much for your help.

Aron

[former_member292966 · Accepted solution]

Hi Aron,

There are a couple of ways to do this.  You could create a subreport that returns the Previous Balance based on a date range.  Very similar to what you are doing now.

Another way would be to create a view or a stored procedure that you can return the current values as well as the previous balance.  Then the values you need for the report are available to you.  This would be my preference because any other solution, like the one above, would require passing a second query for the information you want.

Good luck,

Brian

[Former Member · Accepted solution]

BH
Brian, thanks so much for your reply.  When you say "Stored Procedure", do you mean a stored procedure in the database?  Or is there a way to create a stored procedure in the crystal reports?  I prefer not to create any stored procedures in the database, so that I have an option  to change to a different database engine, if needed.

As far as the first suggestion.  I've tried to create a sub-report, but I cant seem to figure out how to link it specifically to the group to which I choose.   The same value in the sub-report seems to appear among all the groups.  What am I doing wrong?

Also, is there a way to use the parameter field to accomplish my task? I know how to get the value out of the c# code into the report, by using a parameter field.  However, I need that value to be based on the OwnerID (group) in the report.  So is there a way, where a parameter field sends a value into c# code, and than based on that value I can generate a new value and send it back into the crystal report? This way the value will be based on the group it is in.   I'm new to crystal reports, so I'm sorry if I'm totally going in a wrong direction.

Thanks

Aron

[former_member292966 · Accepted solution]

Hi Aron,

Crystal is database agnostic, meaning it will run a report against any database.  If you create a report using AS/400 it's possible to use that same report against an Oracle or SQL Server database as long as the recordset Crystal is expecting is the same.

So having a report run from a Stored Procedure will work on different databases as long as the metadata for the Stored Procedures between the databases match.

For the subreport, when  you drop the subreport into the Group Footer.  Right-click on the subreport and you have the option to Link Subreport.  You can select the OwnerID from the main report and link it to the OwnerID in the subreport.

Your c# suggestion may be going the long way around the barn.  If you link the subreport in your Group Footer 1, you should be able to get the Previous Balance for that OwnerID.  When you link the subreport, it creates a Record Selection Formula in the subreport that should return only the records for that OwnerID.

Once you have the subreport linked, edit the subreport and go to Report | Selection Formula | Edit Record Selection Formula.  You'll see what the linking created.  You can now edit that formula to include your date range that is outside the current period.

Hope this helps,

Brian

[JWiseman · Accepted solution]

hey Aron & Brian,

i'm not familliar with c# but is a subquery possible in the sqlString?

for a query example the following uses a from & to date and the subquery (in bold font) sums up all of the customers previous orders and returns it as the column "previousamounts"

SELECT DISTINCT
`Customer`.`Customer Name`,
`Orders`.`Order Date`,
`Orders`.`Order Amount`,
(
  SELECT SUM(`Order Amount`)
  FROM   `Customer` `Customer2` INNER JOIN `Orders` `Orders2` ON `Customer2`.`Customer ID`=`Orders2`.`Customer ID`
  WHERE `Orders2`.`Order Date` <  {?DateStart} 
  AND `Customer2`.`Customer Name` = `Customer`.`Customer Name`
) AS PREVIOUSAMOUNTS

FROM  
`Customer` `Customer` INNER JOIN `Orders` `Orders` ON `Customer`.`Customer ID`=`Orders`.`Customer ID`
WHERE
`Orders`.`Order Date` >=  {?DateStart} 
AND `Orders`.`Order Date` <=   {?DateEnd}
ORDER BY
`Customer`.`Customer Name`

-jamie