Shared variable assistance required 1

[ITCPhil]

I am working with 2 different databases (identical for sites) using shared variables to pass data from one to the other. This is on Crystal Report 10 connecting to Sybase 12 databases.

The problem is not making the shared variables, but rather passing them along to the main report. the main report is grouped on the timestamp. I split the group header in 2 sections.

The first group header contains my subreport. It has the same selection as the main report and is also grouped by timestamp. It contains a formula which says :

whileprintingrecords;
shared numbervar calls := Sum({iApplicationStat.CallsAnswered})


The second group header contains the formula which calls the shared report, it goes :
whileprintingrecords;
shared numbervar calls

I linked the subreport based on timestamp and tried leaving/removing the checkmark for 'Select data for Subreport based on field' but it will not work.

My issue is that I either get 0's across the board or it prints the grand total in every line instead of the result for each month. I placed a formula in group header C to reset the shared variable but it still occurs.
whileprintingrecords;
shared numbervar calls := 0

Would someone be able to tell me how to get it working properly?

Thanks in advance,
Phil

 

[lbass]

Do you really want to require that only records that match the main report timestamp appear in the subreport? Usually both reports might be based on the same time range for example. If this is the case, then create the link, but go into the record selection formula of the subreport and change the link from and "=" sign to the operator that more accurately reflects your criteria.

If you are unable to create the link in the first place where "select data based on..." is checked, then it suggests that the two timestamps are not of the same datatype. I think even the formatting might need to be the same.

-LB

 

[ITCPhil]

Ooops, I might not have been clear enough in my request.

There is no matching data between the 2 reports. These are 2 identical symposium servers collecting stats from a different site each.

What I am tring to do is total a few items such as calls answered, abandoned, etc... from both sites.

The results are to be in a cross tab, but I have to pass the monthly totals from the subreport to the main report to be able to get a total number from both locations.

The link I am creating between the 2 is the grouped item which is a timestamp divided monthly.

Thanks,
Phil

 

[lbass]

Please explain exactly what fields or formulas (contents, please) you are linking, along with their datatypes and formats.

By the way, I don't think you will be able to use the variables in an inserted crosstab. Maybe you should try to explain in more depth what you are trying to do. There might be a different approach.

-LB

 

[synapsevampire]

Consider using a Command object and pasting in a UNION query to return the data in one recordset.

I'm not current on my Sybase syntax, but it's something like:

select 'table1' DataSource, f1,f2,f3 from database1.table1
UNION ALL
select 'table2' DataSource, f1,f2,f3 from database1.table1

Note that the first field is simply a column to demonstrate where the data came from.

Anotehr option would be either a VIEW or a Stored Procedure that returns everything.

-k

 

[ITCPhil]

Thanks for the answers, I will answer both.

SV, the UNION is good advice, it is what I was hoping to do but unfortunately I know little of database syntax and even less of Sybase , might be I will look online for some documentation or try it in MS Access. The VIEW is not possible I think as I have no access to the server, and the Stored Procedure I will look into but am not familiar with.

LB, I will explain my situation. The senior DG decided that he wanted to see a dashboard of our Help Desk operation. This is divided into 2 sites due to timezones. For the ticketing system, no problem, they both connect to the same Remedy database. However, for the telephony side some genius decided that both sites would be totally unlinked except at the highest level. This means that they both have their own Symposium server, which are identical in every way but unlinked. If one site crashes, the other works on without even noticing it.

What I need to do is make for him a chart (or a crosstab) where I can show to him the total number of calls answered/abandoned and calls abandoned/answered after our service threshold. So I need to make a sum of the results of both sites.

It looks like this basically, the timestamp is a datetime and everything else on my tables are numbers.

Site 1 : Site 2:
C.Answered 1000 850
C.Aband 10 6
Abd.Tresh 7 2
Ans.Tresh 28 12

I can't show him this, there is a large push towards union and I can't compared the two, so I need to show him :
C.Answered 1850
C.Aband 16
Abd.Tresh 9
Ans.Tresh 40

What I am linking on here is my group, which is the timestamp (DateTime) based on the Date reporting Monthly as it's a 4 month report.

Both reports are identical so what I am trying to do is show something like :
January 1000 850
February 1200 1340
....

and then I can print a sum of the two using a @Sites formula containing something like :
whileprintingrecords;
shared numbervar TotalFromSub;
numbervar GrandTotal:= Sum ({IApplicationstat.CallsOffered}, {IApplicationStat.Timestamp}, "monthly") + TotalFromSub

At which point, I would use the Timestamp as rows in a crosstab, leave column blank and enter @Sites as the summarized field.

The problem is when I pass data from the subreport to the main report, I cannot get it to print correctly. At this time on my latest attempt, it is printing the result of my last month 4 times instead of printing each month.

I will give the union and view a try now, thanks for the answers.

Phil

 

[synapsevampire]

You can certainly do this QUICKLY and easily in MS Access, just link the tables in to one database, then create the Union Query in Access, and use the Access query as the datasource.

Much cleaner, allows for maintenance and reusability across other products, and it will prove faster.

-k

 

[ITCPhil]

I will do just that, thank you.

Phil