Walkthrough of creating a report with Crystal Reports 2008

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.

Could I pull data from the POS software database, and import XML data that I exported from the TrafflcFlow server?

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?

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.

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?

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

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.

The saleline table has the timestamp and the date.  The tblCountData has only the time.

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?

Can you explain the groups a little more? What are they for? How do I create them?

No, there is a seperate time field, I believe it is a RecTime field.

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.

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?

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.

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

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

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.

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?

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

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

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.

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.

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?

Also, how can I display fTotalRate as a percentage?

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.

Its giving me the total closing ratio, but it just displays it multiple times.

can you paste your formula so I can take a look at them?

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})

In the report I have the StartDate and EndDate fields, I have the fTotalRate field, and I have the fRatePerDay field which is supressed.

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.

Where should the StartDate and EndDate go? in the Details section?

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?

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

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?

Instead of multiplying it by 100 in the formula, can we convert it to a percentage in the formula?

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%"

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?

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?

I also need to subtract 23 from each day of counting from the traffic counter.

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?

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.

Ok, it looks like I got everything else done.  Thanks for all the help.  It's much appreciated.