Cross Tab Formula 1

[SimonPetherick]

Hi,
I have data constantly entering our database every 10 minutes. I have set-up a report that enables the user to select data for a customer to display either Hourly, Daily or Monthly parameter data. I have set-up 3 simple cross tabs in 3 seperate headers (supressing the ones that are not chosen in the parameters). This works fine.

However, I also have a parameter that allows the user to view data in a different format "energy". The default information is in "volume". The calculation to energy is:
energy = (volume x 6amHV) / 1000

Our gas days are calculated from 6:01am to 6:00am.
HV = 6am reading (ie if I want the HV to calculate the energy for the 1st, I would use the 6am HV reading on the 2nd)

Is it possible to set-up a crosstab to display the energy (calculated) data if selected using the parameters? The format of my crosstabs are site names going across the top (columns) and gas days as the rows, with the sum of the values in the main section.

The reason I'd prefer cross tabs is because I find they run much quicker.

Thanks anyone for your help.....

 

[nagornyi]

Yes, crosstabs can display formulas, including those based on input parameters. Could you please specify more precisely what your difficulty is?

 

[SimonPetherick]

OK....

The parameters are:
- Gas Day (which days data from 6:01am to 6am)
- Data Type (either energy or volume)

We store the 'volume' data in our database for each 10 minutes. The report displays data as gas day totals. If the user selects energy, then a calculation needs to be applied.
Energy = (volume * HV(at 6am) / 1000

The HV is the figure directly from the databse at 6am (ie. if I selected gas day 10th December, the report will retreive data between 06:01am 10/12/2003 until 6:00am 11/12/2003. The HV needed to calculate energy is at 6am on the 11th)

Can I set-up a crosstab (one for energy and one for volume) that will display the data depending on which parameter is selected?

Thanks in advance....

 

[nagornyi]

You now have 3 crosstabs in separate sections and switch between them based on user parameter. So why not to have 6 sections and create two versions of each cresstab -one for volume and and for energy - and suppress those you don't need based not only on (Hourly, Daily or Monthly) parameter nut also on {enerty, volume) parameter.
If this is not answering your question, what do you think you are missing to accomplish the task?

 

[SimonPetherick]

I agree with setting up 6 sections.

However the problem I face is calculating the energy. For example, the data is coming in on a constant basis every 10 minutes. The data is stored in the databse as follows:
Volume HV
10th 6:10am 97 37.56
10th 6:20am 111 37.12
:
:
:
11th 5:50am 84 37.91
11th 6:00am 72 37.98

To calculate the energy, the volume needs to be multiplied by the 6am HV. ie. using the example above:
10th 6:20am energy = (111*37.98)/1000
11th 5:50am energy = (84*37.98)/1000
11th 6:00am energy = (72*37.98)/1000

Is there a way of doing this calculation in a cross tab?

Cheers.

 

[nagornyi]

OK, now I see.
For every time moment you need to be able to pull out the value of HV field corresponding to some other time moment in the future. Not sure this can be done right in the crosstab... The best way, to my mind, is to create a DB stored function that would return energy for a time moment. Another option is a view with two fields: time, energy. If the DB is absolutly not available for creating views or stored functions, the view can be created with Crystal Query. Of course, if the DB design can be updated, the best way would be to add a field like HV600AM and populate it with a trigger that would go off every time a new 6am data is available. What is the DB?
What options availabel?

 

[SimonPetherick]

Unfortunately I cannot manipulate the database. I can only use crystal reports to output the required data. I'm only a novice, but can I use some sort of global formula? At the moment I have the formula to retreive the 6amHV:
if time({DATA_VALUES.READING_DATE}) = time(06,00,00)
then {DATA_VALUES.REPORT_VALUE}

 

[nagornyi]

This formula will returm 6amHV for 6am reading time and will return nothing for all other times. That's not what you need, right?
As the DB not accessible for any design, you can create a view using Crystal SQL designer and then use this view in your report as a datasource along with the DB tables. The view should create pairs READING_DATE, HV6AM, that is the view would match each reading time with 6am HV according to the rule you described above. The SQL text in Crystal SQL Designer may depend on the DB you are useing. For ORACLE it will be someting like this:

SELECT
DV.READING_DATE,
(select reading_value from data_values where reading_date=trunc(dv.reading_date+.74999)+.25) HV6AM
FROM
DATA_VALUES DV

Some comments on the subquery:
for each date/time value we add .74999, this number in days means a bit less then 3/4 of a day, i.e. 18 hours, So if time is before or equal 6am the sum will be in the same day, if time is after 6am the sum will go to the next day. By trunctaion we get rid of fractional part of the day, and then we add .25 which means 6 hours. In this way for each reading date/time we get the date/time at which HV6AM to be read. So the internal sql brings the reading according to your rule. You can use this idea for any other DB.

 

[SimonPetherick]

Are you able to help me out with the SQL as I haven't used Crstal SQL Designer before. I'll try and describe the table structure.....

The report uses 3 tables:
DATA_POINTS
DATA_VALUES
SITES

The 6am HV data can be retrieved by:
if time({DATA_VALUES.READING_DATE}) = time (06,00,00) then
if {DATA_POINTS.DTY_ID} in [60] then
{DATA_VALUES.REPORT_VALUE}

To group the data into gas days I have used the following:
if month({DATA_VALUES.READING_DATE})=1
and day({DATA_VALUES.READING_DATE}) = 1
and time({DATA_VALUES.READING_DATE}) in time(00,00,00) to time(06,00,00)

then date(year({DATA_VALUES.READING_DATE})-1,12,31)


else if month({DATA_VALUES.READING_DATE})=2
and day({DATA_VALUES.READING_DATE}) = 1
and time({DATA_VALUES.READING_DATE}) in time(00,00,00) to time(06,00,00)

then date(year({DATA_VALUES.READING_DATE}),1,31)
:
:
:
:
else

select time({DATA_VALUES.READING_DATE})

case time(06,01,00) to time(23,59,59):
date({DATA_VALUES.READING_DATE})

case time(00,00,00) to time(06,00,00) :
date({DATA_VALUES.READING_DATE})-1

default: date(6666,12,31)

Thank for your help... I think we are close to nutting this out......

 

[nagornyi]

With your field names the SQL will be nearly the same as I wrote, just change reading_value to report_value. Also you need to verify that date processing functions used in formula are working with your DB (if it is not ORACLE).
Crystal SQL Designer is in the Crystal Reports Tools groop. Open it and select New Query , then Enter SQL statement directly. You can then save the query as .qry file and use it as datasource for your report.

 

[SimonPetherick]

nagornyi,

I entered the following into Crystal SQL Designer:

SELECT
DV.READING_DATE,
(select report_value from data_values where reading_date=trunc(dv.reading_date+.74999)+.25) HV6AM
FROM
DATA_VALUES DV

but received an error message: "single-row subquery returns more than one row"

We're using an Oracle database. Am I doing something wrong? Should I be typing something different in the SQL?

Thanks again.

 

[nagornyi]

That means there are more then one 6am record on each day. How is that possible? You can easily fix SQL problem by adding
and rownum=1
after .25, but I would really recommend to find out why there are multiple records. Maybe for different locations? Then this should be taken care of in the SQL.

 

[SimonPetherick]

I have been using the following SQL code:

SELECT
DV.READING_DATE,
(select report_value from data_values where reading_date = trunc(dv.reading_date+.74999)+.25) HV6AM
FROM
DATA_VALUES DV

What I think I need to do now is add another table to this code called "DATA_POINTS". From that table I need to state "DP.site_ID = 1" as well as "DP.dty_id = 60". I have been trying to include these but I'm having trouble. Can you please help????

Cheers.

 

[nagornyi]

Please give the structure of the tables
DATA_POINTS
DATA_VALUES
SITES
and explain the meaning of the fields you think should be used.

 

[SimonPetherick]

We have many sites (I think about 180) that all receive ongoing data each 10 minutes. Each site has a heating value (however this is exactly the same data for each site).

The reason I believe we need the "sit_id" listed in the "Data Points" table is to remove all the unnecessary records.

Each site is structured as below:

Site 1 DTY_ID
Volume 20
Pressure 44
Temperature 80
HV 60
:
:
:

The data_values.report_value will display all data (ie volume, pressure, temp, hv, etc) unless we state dty_id=60.

Hope this helps….

 

[nagornyi]

Please let me know which fields the tables
DATA_POINTS
DATA_VALUES
SITES
consist of.

Also try

SELECT
DV.READING_DATE, DV.SITE_ID,
(select report_value from data_values where reading_date = trunc(dv.reading_date+.74999 )+.25 and site_id=dv.site_id) HV6AM
FROM
DATA_VALUES DV

 

[SimonPetherick]

I finally worked out how to do it. The following is the answer I was looking for:

Select
dp.dty_id,
dp.sit_id,
dv.reading_date,
(Select
dvs.report_value
from
data_points dps,
data_values dvs
where
dps.dpt_id = dvs.dpt_id
and dps.dty_id = 60
and dvs.reading_date = trunc(dv.reading_date+0.74999)+0.25
and dps.sit_id = 1) HV6AM
from
data_points dp,
data_values dv
where
dp.dpt_id = dv.dpt_id
and dp.dty_id = 60
and dp.sit_id = 1
;

One final question. I have saved the .QRY file onto my c drive. Do I link the new query to my original tables? Every time I try to do this, crystal reports crashes.

Thanks heaps for your help....

 

[nagornyi]

Instead of linking the .qry file to the original tables, you can build the rest of the query in the same .qry file. If nothing better comes up, just keep in mind that you can use the entire query you showed above as a table in the FROM clause. So you can just copy existing report SQL and add the above query, connecting it the same way you were going to do by linkiing .qry file to tables.
Example:
select a.field1, a.field2, b.field3
from
table1 a,
(select field3 from ... some query here...) b
where ...

But now that you know how to get HV6AM, I think other ways of building the final query are also possible.