Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Complicated Report Help - Crosstab, subreports, sh Post Reply Post New Topic
Author Message
ksajovic
Newbie
Newbie


Joined: 21 Jul 2011
Online Status: Offline
Posts: 2
Quote ksajovic Replybullet Topic: Complicated Report Help - Crosstab, subreports, sh
    Posted: 21 Jul 2011 at 12:24pm
Hi,
I have been trying to create a report but not having a lot of success.  It seems complicated, but I feel there should be an easier soltion.  Here are the details:

I am using Access for my data and Crystal Reports XI R2 for reporting.  My VB experience is limited.  Essentially, I am trying to create an income statement.  I have a final table which houses most of the main data including balances, a grouping column, month end date, GL, and cost center.  I need to group and sum balances for particular line items and then be able to use their results in calculations.  For example, if I group and sum Fee Income and Other Operating Income, I then need to be able to sum both results to get Total Non-Interest Income (which would be used in a following calculation to compute Net Income).  

I currently have a parameter to select cost center, but I don't care if there is a parameter to choose or if each center just has it's own page.  I need to be able to show three most recent months worth of data in columns and the fourth column being a YTD column.  I currently have the report with one column (current month) using a subreport for each line item and passing variables to the main report for calclation purposes, but I am having a hard time figuring out how to convert to the multiple columns and whatnot.

I appreciate any help anyone can provide.  I only have a few days to build this before they pull the plug and band-aid it with a more manual Excel version.  I'm sure I have left out helpful details, so please let me know what additional info you need.

Thanks!
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 22 Jul 2011 at 3:25am
given that you are using Access, so my standard stored proc solution doesn't work, what i would suggest is getting the data into 4 tables using Command Objects.  For this part, you don't need to use a parameter, you can filter (if you want for the cost center in the record selection)
 
I am guessing that there is some common value that you are using for your summing, GL account is a likely candidate, so you would have something like  for the select:
select glaccount, debit, credit from ledgerTable
where entry date between '1/1/'+convert(varchar, year(getDate()) and getDate();   
 
this would get YTD (you probably need to adjust for Access syntax)
for this month it would be convert(varchar, Month(getDate())) + '/1/ + convert(varchar, Year(getDate())
 
now the more difficult, back a month
start date: datediff(M,-1,convert(datetime,convert(varchar, Month(getDate())) + '/1/ + convert(varchar, Year(getDate())))
ending would be :
DateDiff(d, -1, convert(varchar, Month(getDate())) + '/1/ + convert(varchar, Year(getDate()))
 
to go back 2 months would be similar.
 
you would then link all the tables by GLAccount, and you would probably link that to your GLAccount table so that you can get a description.
 
It might not be a full fledge solution, but hopefully it is a path to one.
IP IP Logged
ksajovic
Newbie
Newbie


Joined: 21 Jul 2011
Online Status: Offline
Posts: 2
Quote ksajovic Replybullet Posted: 22 Jul 2011 at 6:01am
Thanks for the reply!  Maybe I'm not understanding all of what you wrote, but my main issue is how to show the data in columns in Crystal and still do calculations.  Maybe this makes it more clear - I would like to see it like this:
 
Cost Center: 3010
 
Interest Income
Cash and Investments     [sum balances for several GLs]
Loans                               [sum balances for several GLs]
Total Interest Income       [Cash and Investments + Loans]
 
Interest Expense
Deposits                           [sum balances for several GLs]
Borrowings                       [sum balances for several GLs]
Total Interest Expense     [Deposits + Borrowings]
 
Net Interest Income         [Total Interest Income - Total Interest Expense]
 
That's a condensed version, but the basic idea.  I would need that in four columns - for example May, April, March and YTD
 
My main table currently looks like this:
 
Month End     Account     Dept     Balance
05/31/2011   266000     3010     $200.00
04/30/2011   266000     3020     $150.00
04/30/2011   266000     3010     $300.00
03/31/2011   266000     3010     $100.00
05/31/2011   325000     3060     $500.00
04/30/2011   325000     3040     $850.00
04/30/2011   325000     3060     $800.00
03/31/2011   325000     3050     $600.00
 
My GL table has a grouping column I use in order to put GLs into the correct line items.
 
Thanks!
 
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 22 Jul 2011 at 10:21am
yes, that is what I envisioned.
 
Create 4 tables, 1 for each of the 3 months and 1 for YTD.  Now all your data can be linked by GL Account and it will read all 4 tables at the same time.
 
If you don't do it this way, I can't see a way to get the data as if you filter by date, you will miss lots of transactions.  If you just read from the table, you can't do anything as the data will never align in a row (the columns would be in different rows) you might try a cross tabs report, but how to you get YTD and not get all the months?
 
Those were my thoughts, create a table for each time period you want, then you can use the aggregates or formulas to sum / do calculations on the values that you want.
 
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.