Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1051
  • Last Modified:

Walkthrough of creating a report with Crystal Reports 2008

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
Sean Rhudy
Asked:
Sean Rhudy
  • 26
  • 17
1 Solution
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
Could I pull data from the POS software database, and import XML data that I exported from the TrafflcFlow server?
0
 
furrybootsistheanswerCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Sean RhudyPresidentAuthor Commented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
The saleline table has the timestamp and the date.  The tblCountData has only the time.
0
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
Can you explain the groups a little more? What are they for? How do I create them?
0
 
Sean RhudyPresidentAuthor Commented:
No, there is a seperate time field, I believe it is a RecTime field.
0
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
Also, how can I display fTotalRate as a percentage?
0
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
Its giving me the total closing ratio, but it just displays it multiple times.
0
 
furrybootsistheanswerCommented:
can you paste your formula so I can take a look at them?
0
 
Sean RhudyPresidentAuthor Commented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
Where should the StartDate and EndDate go? in the Details section?
0
 
Sean RhudyPresidentAuthor Commented:
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
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
Sean RhudyPresidentAuthor Commented:
Instead of multiplying it by 100 in the formula, can we convert it to a percentage in the formula?
0
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
Sean RhudyPresidentAuthor Commented:
I also need to subtract 23 from each day of counting from the traffic counter.
0
 
furrybootsistheanswerCommented:
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
 
Sean RhudyPresidentAuthor Commented:
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
 
Sean RhudyPresidentAuthor Commented:
Ok, it looks like I got everything else done.  Thanks for all the help.  It's much appreciated.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 26
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now