Accounting Column Style Report 1

[Keoki]

Does anyone have a sample of an accounting-style column report (not a cross-tab) where the data is grouped by month, such as:<br><br>Oct 1999&nbsp;&nbsp;&nbsp;Nov 1999&nbsp;&nbsp;&nbsp;Dec 1999&nbsp;&nbsp;Jan 2000&nbsp;&nbsp;Feb 2000<br><br>I have looked through all my books and Seagate's tech support site, but have been unable to find something to help create a report that allows me to summarize rows of data into monthly columns.&nbsp;&nbsp;<br><br>If you can help me figure out how to do this (in plain English or with pictures &nbsp;&nbsp, I would greatly appreciate it.<br><br>Thank you,<br><br>Keoki<br><br> <p>Keoki<br><a href=mailto:gb@cardinalhill.org>gb@cardinalhill.org</a><br><a href= > </a><br>

 

[dgaylor]

From my experience you will need to either use formulas for each of those columns (are each of the rows the same type of info?), OR if you are using a vb front-end or such then create your own ado recordset with the data already manipulated and create the crystal reports using active data (this works off of a ttx file), OR report off of a stored procedure that will generate a temp table (or select) with the data as you need it....&nbsp;&nbsp;

 

[Keoki]

Thanks for replying.&nbsp;&nbsp;In theory I know what I should do, but in reality I can't think of how to do this in Crystal Reports.&nbsp;&nbsp;For example, one user might want a six-month date spread and another user might want a 12-month date spread.&nbsp;&nbsp;How in the world would I create such a flexible document?&nbsp;&nbsp;Also, one report might start in August and go to May of the following year, while another might start in February and go to June.&nbsp;&nbsp;I don't know how I would possibly tell the data how to group.<br><br>I have created hundreds of reports with Crystal Reports, but for some reason this one has me baffled.&nbsp;&nbsp;I was hoping to find a sample with examples that would give me a way to start. <p>Keoki<br><a href=mailto:gb@cardinalhill.org>gb@cardinalhill.org</a><br><a href= > </a><br>

 

[dgaylor]

well, Just recently i needed to create a report that listed counts for the last 12 months, or for the last 13-24 months, or for 3 month / 6 month / 7-12 month / 13-15 month (this causes the headers and number of header columns to change for the same report). The report also had to be by department, and by description. I had to create a temporary table that is built by a stored procedure, with the following format:<br>Field:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Possible Value<br>---------------------------------------------------<br>DateRange A: Count Report 12 Months<br> B: Count Report 13-24 Months<br> C: Grouped Months<br>TypeID A: Last_Purchase_Date All<br> B: Last_Purchase_Date Core<br> C: Last_Purchase_Date Jewelry<br> D: Last_Purchase_Date Apparel<br>DesciptionID &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;A: 3 Line Address<br> B: 4 Line Address<br> C: Female<br> D: Male<br> E: Unknown<br> F: Zip4<br> G: Month<br>Month1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#<br>Month2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#<br>Month3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#<br>Month4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;# <br>Month5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#<br>Month6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#<br>Month7&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;# <br>Month8&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;# <br>Month9&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#&nbsp;&nbsp;&nbsp;<br>Month10&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#<br>Month11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#<br>Month12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;#<br><br>what i did in crystal was to use the sql stored procedure as my data source (or i could have created a table with the above format). I created 2 groups for DateRange and TypeID. Date Range causes a new page break, TypeID just causes a break. The DescriptionID and month fields are used as the detail lines..... What you can do is suppress the months that don't have any values in them, and also suppress the month-headers based on a formula if you like...<br><br>I don't know if this helps you but it might... Sorry if it doesn't.<br>

 

[dgaylor]

this might also help you out with the headings:<br><A HREF="

http://www.hammerman.com/techtips66.html"

TARGET="_new">

http://www.hammerman.com/techtips66.html</A>

 

[Keoki]

Thank you for getting back to me.&nbsp;&nbsp;You have given me some ideas of how to approach this.&nbsp;&nbsp;Thank you very much for all the help.&nbsp;&nbsp; <p>Keoki<br><a href=mailto:gb@cardinalhill.org>gb@cardinalhill.org</a><br><a href= > </a><br>

 

[HoustonGuy]

Hello my Crystal brother! Let me break this down into two sections for you.<br><br>1) Creating a report where the data is grouped by month will not be a problem. There is a Craze report that is installed as a sample if you have Seagate Info. I'm not sure if it is installed with Crystal Reports or not. The name of the report is Finstate.rpt and it shows a perfect example of a multi-column accounting style report. If you don't have it I will be glad to send you a variation of it that I have played with. <br><br>2) Creating a report where the data is grouped by all 12 months whether or not data exists for that month WILL be more difficult. Crystal doesn't allow you to dynamically show group names if no data is returned. If you created specific groups for each month, Crystal won't show the groups if no months are found that match the criteria. So, if I defined my first group to be &quot;January&quot; and the selection criteria for that group is <br><br>Month({MyDB.MyDateField}) = 1 <br><br>then Crystal MUST find a month that equals 1 to show the group name of &quot;January&quot;.<br><br>Now, you can hard code the month labels on your report and hide and show those sections depending on whether or not data was returned - but this isn't a true group, its just a label that will be hidden or shown. <br><br>To get a true group to appear, regardless of data returned, you will have to go back to the stored procedure level where you can &quot;trick&quot; the Crystal report into thinking there is data for each month when there really isn't. dgaylor appears to be doing this in his stored proc code above.<br><br>If you don't mind having missing months where data is absent then the report will be easier.<br><br>Hope this helps - and let me know if you need a copy of the report I mentioned.<br><br><br> <p>Cody ford<br><a href=mailto:codyford@yahoo.com>codyford@yahoo.com</a><br><a href= > </a><br>VB, VBS, Seagate Info/Crystal Reports<br>

 

[Keoki]

Cody,<br><br>I have a .mdb file called craze, but not a report.&nbsp;&nbsp;I would very much appreciate it if you could send me a copy of the report.&nbsp;&nbsp;I have never worked with Crystal Info.&nbsp;&nbsp;Will this report work with Crystal Reports?<br><br>Thanks,<br><br>Keoki <p>Keoki<br><a href=mailto:gb@cardinalhill.org>gb@cardinalhill.org</a><br><a href= > </a><br>

 

[Keoki]

Cody,<br><br>I have a .mdb file called craze, but not a report.&nbsp;&nbsp;I would very much appreciate it if you could send me a copy of the report.&nbsp;&nbsp;I have never worked with Crystal Info.&nbsp;&nbsp;Will this report work with Crystal Reports?<br><br>Thanks,<br><br>Keoki<br><br><A HREF="mailto:gb@cardinalhill.org">gb@cardinalhill.org</A> <p>Keoki<br><a href=mailto:gb@cardinalhill.org>gb@cardinalhill.org</a><br><a href= > </a><br>

 

[suemak]

I've done a similar report where they wanted to report by week, but the weeks would start backwards from the day they ran the report. I did this Crystal by using a form where they entered the date they wanted, and then pulled data in each column by using a date range: between today -7 and today -14 (not the code...just a description of the formula). Then for the headings, I used that same paramter form field to create the headings: Today - 7 to today..that idea.

You might be able to do something similar for accounting data by having them enter the date range, the increment of days or months, etc. Sue