Line and Bar Graph Combined? 1

[shelby55]

Hi

I am using Crystal 9.0 reporting from an Access 2002 database.

Is it possible to create a chart with one series of data as the bar graph and the other represented as a line graph?

For instance, I want to show discharge hour as the bar graph and admit hour as the line graph - is this possible?

Thanks.

 

[lbass]

Set up a bar chart with the two series, and then run the report. Then right click on one of the series->series options->show selected series as: select "Line".

-LB

 

[shelby55]

Hi LB

Thanks for the speedy reply!

I guess I wasn't thinking on this though because I have 2 "on change of events" - one for disch hour and one for admit hour - but this doesn't facilitate having one series to select for line versus bar.

How would I do this? Thanks.

 

[lbass]

You would need to have an independent datetime field to use for the "on change of" field, and then you would use two formulas as summaries (using sum):

if {table.admithr} = {table.allhrs} then 1

if {table.dischhr} = {table.allhrs} then 1

If you don't have an independent field to do this, you could create a command that uses a union all to combine the two datetime fields into one allhrs field that you then use as the on change of field. You would have to also build in each datetime field and an identifier field, e.g., transactionID that is unique to admission/discharge. Then you would use distinctcounts on formulas like this:

if {command.admithr} = {command.allhrs} then
{command.transactionID} else
tonumber({@null})

...where {@null} is a new formula that you open and save without entering anything.

Repeat for the dischhr field.

-LB

 

[shelby55]

Hi LB

As always thanks for your reply!

However, you lost me on this one! Not sure what you mean by combining the fields. If one abstract has an admit hour of 09 and the disch hour is 12 what would the combined field look like?

I think I understand what you're saying, just not the concept. In other words you want to have a reference to all hours 00 to 23 and then if disch hour = this all hours then +1 else 0. But I still don't know how to link the abstracts to this all hours field.

Thanks.

 

[lbass]

Is the hours field literally a 2-digit string field or are you getting this from a datetime field?

If you have a separate table that has all hour instances, you should use that for the on change of field. If not, you can create a "field" that at least contains all instances used by the admit and dischg fields, by using a command with a union all, like this:

select table.admithr as allhr, table.admithr, table.dischhr,
table.transactionID
from table
union all
select table.dischghr as allhr, table.admithr, table.dischhr,
table.transactionID
from table

Then you would follow the earlier steps. The union all merges fields that are in the same ordinal position on each side of the union all, so both table.admithr and dischghr would appear in allhr.

-LB

 

[shelby55]

Hi LB

No the field is not literally a 2 digit string field from the database. AdmHour is derived from the admit date/time field and DHour from the discharge date/time field.

I could definitely create a table of "all hour" instances and I can also manipulate the main table to have AdmHour and DHour as fields on their own.

So this union all query is in Access, correct, not in Crystal?

 

[lbass]

No, it is a Crystal command (database expert->your datasource->add command) that you would use to return all necessary fields for your chart.

You can use datetime fields.

-LB

 

[shelby55]

Thanks LB

I'll give it a go and let you know how I do!!

 

[shelby55]

HI LB

I'm back! I've figured out all that you indicated except in creating this I get a message:
"more than one datasource or a stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group-by is performed". Is that a problem?

The command statement is:
SELECT Data.AHour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo from Data
Union ALL Select Data.Dhour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo from Data

I needed AcctNo and ChartNo because combined they are a unique identifier. I linked the Command generated "table" to the Data table (the only other data source in the report) using AcctNo and ChartNo.

AllHrs is used for "on change of" in the graph and I created the following 2 fields for counting:

if {Command.AHour}={Command.allhr} then {Command.AcctNo}+{Command.ChartNo}
else {@null}

if {Command.Dhour}={Command.allhr} then {Command.AcctNo}+{Command.ChartNo} else {@null}

When I run the report normally without date parameters there are 79218 in the database but with this new "command" there are 158434 so it almost doubled.

Why isn't this working? Thanks.

 

[shelby55]

Hi

Sorry but the report is really, really, really slow to process when I add a parameter field of discharge date. Is there anything to speed it up?

Thanks.

 

[lbass]

You should be returning ALL necessary fields IN the command, instead of linking it to other tables. The linking occurs locally and dramatically slows reports. You should also build any parameters into the command. There are twice as many records because of the union all. You should be using distinctcounts on your formulas.

-LB

 

[shelby55]

Hi LB

Thanks for replying!

After I posted I thought how it makes sense about the double records because of the union but thought I'd wait and get the answer from you!

Makes sense about returning all fields - thanks - but I don't know how to set parameters in this fashion. Is there a good reference for that on the site?

Thanks!!

 

[shelby55]

Hi LB

Okay I figured it out - this is awesome!! You never cease to amaze me LB!!

Thanks so much!

 

[shelby55]

Hi LB

Sorry to keep bugging but the report doesn't look right. If I check the Access database and look at hour 00 there should be 24 based on discharge hour and 261 based on admit hour. However, there are 28 discharges and 2264 admits for Hour 00. All others are off as well.

In the chart the group is on change of "allhour" and each formula is above based on distinct count.

What could the problem be?

Thanks.

 

[lbass]

I can't really troubleshoot this without more information. How are you limiting the data by date? I don't see that in your command.

You should put the fields in the detail section and then observe what is happening. You might need to build more fields into the command so that you tell what is going on.

-LB

 

[shelby55]

Hi LB

I'll do what you've suggested in the second part of your post but the code for the Command is:

SELECT Data.AHour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data
Union ALL Select Data.Dhour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data
WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date}

Thanks.

 

[lbass]

You have to build the where clause into BOTH sides of the union statement in this case, since both sides are intended to refer to the same dataset:

SELECT Data.AHour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data
WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date}

Union ALL

Select Data.Dhour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data
WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date}

-LB

 

[shelby55]

Hi LB

I didn't see this post but figured it out myself and came here to post the solution.

Thanks very much for your assistance!

 

[shelby55]

Hi

How would I use this same methodology and show average visits per year per hour?

Thanks.