Solved

Walkthrough of creating a report with Crystal Reports 2008

Posted on 2008-10-28
43
1,039 Views
Last Modified: 2013-11-15
Hello,

I am using Crystal Reports 2008 developer edition.  The store I am working with has a door counter which puts its count data into an access database controlled by the TrafficFlow software.  TrafficFlow also has the ability to export the data in xml format.  In the end, I want the user to be able to specify a date range, and have the report pull the count info from TrafficFlow and pull the sales information from there POS software which is in an access database, and give the closing ratio.

The closing ratio calculation would be "number of sales" dvided by "number of walk-in traffic".  I would also like it to calculate the average amount spent per customer.

I am brand new to Crystal reports, but am MCSE certified.
0
Comment
Question by:seanrhudy
  • 26
  • 17
43 Comments
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22828395
Hi, you can't use crystal to pull data from 2 separate databases at the same time without using sub reports which I don't think would be that useful to you in this situation. However, you could create a new access db and link the required tables from the 2 mdb files (file/get external data/link tables) and use that as your datasource for your crystal report. Once you've got that set up, you can get started with your report creation. The help from within crystal is pretty useful and should enable you to get started fairly quickly.
0
 

Author Comment

by:seanrhudy
ID: 22828410
Could I pull data from the POS software database, and import XML data that I exported from the TrafflcFlow server?
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22828507
Crystal would still treat that like 2 separate datasources, so not directly. You would need to import your xml into the same db that you want to get your POS data from.
Are you using crystal alone to develop your report, or building the report into an application developed in .Net for example?
0
 

Author Comment

by:seanrhudy
ID: 22831281
Just using crystal reports by itself, what's the easiest way to join the data?  They only need to be able to break it down by day, so all I really need, is the date, the number of transactions, the denomination of those transactions, and the traffic count, at the end of each day.  So I don't need too much data here.
0
 

Author Comment

by:seanrhudy
ID: 22835354
Hello, I had some extra time today, so I played around with Crystal reports.  It allowed me to select the 2 data sources, and it displayed them in the field explorer.  So I made the report header and the page header.  Then, it also allowed me to enter a field from each of the 2 data sources in the detail row.  Does this mean  it will work?
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22836803
Hi, now that is something I did not realise 2008 supported. I apologise for misleading you with my response. It does look like you can bring data from the 2 mdb files and the only consideration seems to be that you ensure you don't use server side grouping - the option should be greyed out and unavailable in any case.
Now that you have the data fields available, you will be able to start putting your report together. Let us know if you need any help,
Cheers
0
 

Author Comment

by:seanrhudy
ID: 22836962
Ok, I'm fine with creating the headers and footers and all of that.  But I need to manipulate the data before I display it, and I'm not sure where to start.  This is what I have.  A database called "edge" The edge database table I am using is Saleline, the SlWhen field holds the date of the transaction, the SlAmount field holds the amount of each transaction, and the Slcustkey holds the customer ID for each transaction.  The other DB is called the TrafficFlowDB.  The table we are using in this db is called tblCountData.  The only fields we need in is are RecDate and InCount, which provide the date and number of people.

Now right now, the counter is reporting the count every hour, but to make it easier, I can change that to once a day.  That way it eliminates one of the formulas.  So I need an option for the user to select the date range.  Then when the report runs it needs to run a formula to get the count of how many unique slcustkey records there are for that date range.  This is because some customers have 5 transactions in one trip.  Then it needs to add up the value of all of the records in the InCount field for the user specified date range.

The values of each of these can be displayed on the report.  The number of unique transactions needs to be divided by the Count value and displayed in a percentage.  I may want to do another simple calculation, but if you can walk me through some of this, I'm sure I'll be able to figure out the rest.
0
 
LVL 3

Accepted Solution

by:
furrybootsistheanswer earned 500 total points
ID: 22837484
ok, sounds good so far.
Are your date fields just dates, or do they hold time valus as well? If it's just dates you can link your tables by the date fields.
You'll want to create 2 groups in your report (report/group expert), one for each day (Saleline.SlWhen) and then one for each Slcustkey (Saleline.Slcustkey).
You can then insert summaries for your field values on your report (insert/summary). For SIcustkey, create a summary for distinct count and insert it onto the group footer for each day (Group #1 :Saleline.SlWhen). This will give you the number of distinct customers each day.
For InCount (assuming you are using 1 record per day) you can insert a summary for this field - use maximum this time.
Your report will now have (in GF1) the Day, number of distinct customers and the number of people coming in. To get the percentage you require, create a new formula (call it fDayConversionRate) and enter this :

if Maximum ({tblCountData.InCount}, {Saleline.SlWhen}, "daily") <> 0 then
100 * DistinctCount ({Saleline.Slcustkey}, {Saleline.SlWhen}, "daily")/Maximum ({tblCountData.InCount}, {Saleline.SlWhen}, "daily")
else
0

Now save your new formula and drop it onto your report in group footer 1 and format it as a percentage.

To limit your report to the date range selected by the user, create 2 Parameter fields - StartDate and EndDate with data type 'date'. To do this, right click Parameter Fields in Field Explorer and select New. You can edit the prompts when you create the parameters. Once you have done that, you need to tell your report to restrict its record selection to records between these dates. Go to Report/Select Expert and click Show Formula. Enter this in the box, making sure that record selection is selected;
{Saleline.SlWhen} in {?StartDate} to {?EndDate}


This should get you started in the right direction, you'll need to add more formula and calculations to get the figures for your report footer.
0
 

Author Comment

by:seanrhudy
ID: 22837549
The saleline table has the timestamp and the date.  The tblCountData has only the time.
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22837559
Is there another field in the tblCountData that indicates which day the count is for or another way for you to establish which day the count has occured on?
0
 

Author Comment

by:seanrhudy
ID: 22837561
Can you explain the groups a little more? What are they for? How do I create them?
0
 

Author Comment

by:seanrhudy
ID: 22837579
No, there is a seperate time field, I believe it is a RecTime field.
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22837589
The groups allow you to break down your report into logical sections - in this case it is beneficial to have a group for each day and a second group within each day for each unique customer id. This allows you to insert summarized data into the group footers such as the count of unique customers each day.
Groups can be created when you are using the report wizard to create your report from scratch, or after the fact when you decide that you want to add a group. The wizard part is straightforward enough - you'll find details in crystal help to explain the details. To add a group after your report is initially created, go to Report/Group Expert. You can pick from your available fields whichevere field(s) you wish to group by.
0
 

Author Comment

by:seanrhudy
ID: 22837609
Oh ok, but should I be creating a group for each day even if it will be possible for the user to select an entire year of data? Would that effect the way the data looks?
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22837662
If the user doesn't want to see a conversion rate for each day, then you can just suppress the 'day' group (report/section expert - pick your section and click suppress.) Same for details etc.

It may be simpler to try using a linked table mdb as you will have difficulty in joining your tables in crystal due to the different date/datetime data you have in your datefields. That way you can create a query in access that will summarize your data prior to bringing it into crystal - it depends on how comfortable you are with queries in access, but it could save you a lot of work in crystal.
0
 

Author Comment

by:seanrhudy
ID: 22837698
Could I just have a date range parameter and then create 2 seperate statements for the number of sales and the count number.  So if the date range parameter is called date range, could I have one that is Select * From Saleline Where
     SlWhen = daterange

And then have a seperate one for the TrafficFlow date.
Select * From tblCountData Where
    RecDate = daterange


0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22837801
You still need to be able to tell crystal how to link your 2 recordsets. In order for crystal to display your records correctly, it essentially needs to build a view of the data as 1 recordset. I don't think you'd be able to link the 2 date fields in crystal - 01/10/2008 12:30:00 will not be join to 01/10/2008. You may have another field that you could join the 2 recordsets on?
This is the part that I think makes more sense to do prior to bringing the data into crystal. You can use a query created in access in the same way you would a table in crystal. Have a look at the mdb's attached and see if they make sense to you.
link.mdb
edge.mdb
TrafficFlowDB.mdb
0
 

Author Comment

by:seanrhudy
ID: 22838108
Ok, so I changed the database paths to meet mine on the network.  The cust_per_day query looks fine, I assume that is reporting the number of unique customers per day correct? What is report_source supposed to do, when i try to run it, it asks for a value for parameter cust per day.sldate.  If I enter todays date, it runs the query but returns over 146,000 records, which is obviously not correct.  Am I doing something wrong there?  The salesline query runs fine, but I want to verify the data is correct.  It has a slcustkey and an sldate, there is about 2 or 3 unique custid's for each date, but the cust per day query shows 20 unique customers purchased something today.  Let me know.  Thanks.
0
 

Author Comment

by:seanrhudy
ID: 22847550
I figured out the problem.  In the report source query, you had cust per date.slWhen.  But it should be cust per date.Expr1 because that is what you named the date field in that query.  So that is all working.  So how do I put the date ranger picker in the report?  And how do I connect the date range picker to our new db?
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22847674
hi, sorry it's been a bit hectic and I've not had time to get back to you.
Great, so we've now got the report source query working and it's giving you the data you need? Like this:
RecDate      InCount      CountOfSlcustkey      SlDate
1/10/2008      10      7      1/10/2008
2/10/2008      13      3      2/10/2008
Start a new crystal report and create a connection to the new access file (link.mdb). You'll see tables and views in the available datasources. Expand views and select report_source. With the query returning one row per day, you won;t need to put groups in your report, so just pick the fields you want to show on the details area (remember you can suppress these if you only want to show the report total).
You'll need to create 2 formulae, 1 for rate per day and 1 for total rate to display your conversion rates.
Per day (called fRatePerDay)
if  {report_source.InCount} <> 0 then 100 * {report_source.CountOfSlcustkey}/{report_source.InCount} else 0

The other one relies on you inserting summaries in the report footer as described before, but this time just SUM both fields - incount and countofSlCustKey. The formula looks like:
if Sum ({report_source.InCount}) <> 0 then 100 * Sum ({report_source.CountOfSlcustkey})/Sum ({report_source.InCount})

In the report, create 2 date type parameters as described before and edit you select formula as before.
When you go to preview the report, you will be prompted for a startdate and an endate - just pick them from the calendar.
The attached file needs renamed to a .rpt extension - it should illustrate what I've explained here.
count-report.rpt.txt
0
 

Author Comment

by:seanrhudy
ID: 22847746
When I go to Select Expert, it gives me the option of record, group, or saved data, if i choose any of them, it asks me to choose a field from my database
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22847757
Yes, that is expected. You want to select record option and then pick the date field from your database. Your parameters then form part of the SQL command that crystal sends to the db to get the report data.
0
 

Author Comment

by:seanrhudy
ID: 22847807
Ok, i created the date fields, added the formula to restrict the select statement to the dates selected.  I added to daly rate formula and the total rate formula.  I put the startdate and enddate fields on the report, and i also dragged over the total rate formula to the form.  When I run it and choose only one day, it looks like its working fine.  But if I choose more than one day it does the calculation correct but it shows the same information as many times as the amount of days were selected.  So if I choose 10/27/2008 to 10/30/2008 it shows the same data on the report 4 times.
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22847825
If you just open the report_source query in access, are your figures for each day different and for any given day do they correspond to what you see when you run your crystal report for that day?
0
 

Author Comment

by:seanrhudy
ID: 22847828
Also, how can I display fTotalRate as a percentage?
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22847846
select the field on your report and click the percent symbol on the formatting toolbar. you can increase/decrease decimals using the command on the same toolbar. If you cant see the toolbar, right click in the toolbar area and make sure that 'formatting' has a check next to it.
0
 

Author Comment

by:seanrhudy
ID: 22847848
Its giving me the total closing ratio, but it just displays it multiple times.
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22847853
can you paste your formula so I can take a look at them?
0
 

Author Comment

by:seanrhudy
ID: 22847862
Date formula: {report_source.Expr1} in {?StartDate} to {?EndDate}

fRatePerDay: if  {report_source.InCount} <> 0 then 100 * {report_source.CountOfSlcustkey}/{report_source.InCount} else 0

fTotalRate: if Sum ({report_source.InCount}) <> 0 then 100 * Sum ({report_source.CountOfSlcustkey})/Sum ({report_source.InCount})
0
 

Author Comment

by:seanrhudy
ID: 22847866
In the report I have the StartDate and EndDate fields, I have the fTotalRate field, and I have the fRatePerDay field which is supressed.
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22847877
The fTotalRate will have one value on the report and should be in the report footer. If this is in your detail section, it will have the same value for each row.
the fRatePerDay should be in your detail section and it will be calculated for each day. It should not appear in the report footer or anywhere outwith the details section on your report.
0
 

Author Comment

by:seanrhudy
ID: 22847885
Where should the StartDate and EndDate go? in the Details section?
0
 

Author Comment

by:seanrhudy
ID: 22847897
Ok, it looks like that is working, Is there any way to:
1. Change the fTotalRate format to a percentage?
2. Display a Pie Graph from the fTotalRate?
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22847910
1) select the field on your report and click the percent symbol on the formatting toolbar. you can increase/decrease decimals using the command on the same toolbar. If you cant see the toolbar, right click in the toolbar area and make sure that 'formatting' has a check next to it.
2) I'm not sure off the top of my head how you would show one figure as part of a pie chart - take a look at crystal help and see if there's any info there, also maybe take a look at the dashboard sample reports that ship with crystal.
I'm away for the weekend, but if you get stuck I can probably pick up again on Monday,
Cheers
0
 

Author Comment

by:seanrhudy
ID: 22847934
It's not letting me select percent, i think because it's a formula, is there a way to format it to a percentage in the formula?
0
 

Author Comment

by:seanrhudy
ID: 22847957
Instead of multiplying it by 100 in the formula, can we convert it to a percentage in the formula?
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22864136
If the result of the formula is a number, you should be able to format it as a percentage.
If you want to return a string with a percentage as the result of your formula, you can do this;

if  {report_source.InCount} <> 0 then
CStr(100 * {report_source.CountOfSlcustkey}/{report_source.InCount}) + "%"
else "0%"
0
 

Author Comment

by:seanrhudy
ID: 22868446
If I highlight the fTotalRate field and select the percentage symbal in the toolbar, it does not select it.  And if I make a pie and choose the fTotalrate as the data source it always shows 100% in the pie when I run the report.  Does the pie have to be in the details section?  Or do I have to calculate the percentage of people who do not buy and add that to the pie as well?
0
 

Author Comment

by:seanrhudy
ID: 22868523
Also, the counter company is suggesting I do not only pull onnce per day.  So how can I change it to add up all of the counts for each day?
0
 

Author Comment

by:seanrhudy
ID: 22869883
I also need to subtract 23 from each day of counting from the traffic counter.
0
 
LVL 3

Expert Comment

by:furrybootsistheanswer
ID: 22889912
I would do the aggregation in the access query so that you still get 1 count per day. You can easily subtract 23 from this number in the query as well.
I'm not certain how you would get a pie to show anything other than 100% when you only have 1 number - perhaps open another question regarding the graphs.
How did you get on with the % formatting problem?
0
 

Author Comment

by:seanrhudy
ID: 22890795
How can I change the access query to sum up all the counts for each day and subtract 23 for each day?  I will open another question for the pie graph, i'm not too worried about that.
0
 

Author Comment

by:seanrhudy
ID: 22892118
Ok, it looks like I got everything else done.  Thanks for all the help.  It's much appreciated.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now