DBCrossTabSource
Hi.
Can we use CrossTabSource inside Fast report 4?
For example. We have non agregated raw data printed in report.
At the end of report we need graph with agregated values (count) based in printed data.
How can we do this ?
Thanks for any hints.
Can we use CrossTabSource inside Fast report 4?
For example. We have non agregated raw data printed in report.
At the end of report we need graph with agregated values (count) based in printed data.
How can we do this ?
Thanks for any hints.
Comments
Search this forum for dbcross topics to find what cases it can be used in.
Maybe you do not need a crosstab at all. You can create one extra query to agregate your not agregated data and use columns of this query inside TfrxMemoView on summary band even without any connection between data rows and this agregated data from extra query. And yet - have you tried to use natural agregate functions on bands (summary ones) at the end of your report?
Mick
PS.
In my opinion DBCross is one of real pearls FR 4 offers to its users.
Hi Mick.
We are trying to create report with details data + cross tab statistic presentation + graphical presentation of that statistical data
without extra query. Standard TeeChart components uses TDBCrossTabSource. Can we use that in FR 4 ?
Thanks for your response anyway.
Edvin
Pure FR DBCrossTab doesn't create any graphics itself.
I had a look at Steema Software site - and I guess your question was about that.
DBCrossTab is just a matrix of values that you can easily manipulate using OnBeforePrint event fired for each cell. So if you want to prepare a chart based on data agregated by DBCross, you must do it manually filling special data for other TChart component.
If you plan to do so, then you should
- add series to TChart component in OnPrintColumnHeader (or OnPrintRowHeader) event
- add data to each of series in OnPrintCell.
Each of above event gives you info about RowIndex, ColumnIndex, CellValue and you can get text for Row and Col headers as well.
Mick
Hi Mick.
Yes I was talking about TDBCrossTabSource by Steema Software.
Your suggestion using DBCrossTab events is very good.
But, can be DBCrossTab unvisible and still fires events ?
Sometimes we just need graph without crossTab on report.
So we try to register TDBCrossTabSource into report scripter and then using it inside report code.
It works but not always !
We use code:
CrossData.GroupField := 'SomeField1';
CrossData.LabelField := '';
CrossData.ValueField := 'SomeField2';
CrossData.Formula := 0;
CrossData.Series := Chart1.Series[0];
CrossData.Active := True;
It works well if we dont put graph on footer. Repainting graph clears it.
Does anyone try that solution ? Something else ?
We do not use full TeeChart library as it destroys our Delphi components [img]style_emoticons/<#EMO_DIR#>/sad.gif" style="vertical-align:middle" emoid=":(" border="0" alt="sad.gif" /> We wait for FR 5 (some people say it was to be at the begining of 2011)[/img][img]style_emoticons/<#EMO_DIR#>/biggrin.gif" style="vertical-align:middle" emoid=":D" border="0" alt="biggrin.gif" /> So I don't have any experience above the standard graphs available in FR 4. Please tell a bit more about your report to let me think about possible solutions. First I think you use crossTab because there is a variable number of columns not known at design process, am I right? Second - is your report made for one main database row or do you repeat it (I mean graph and crossTab) for more database rows? Mick[/img]
I couldn't stop doing more [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> I attach result of my research. Yes you can make DBCrossTab unvisbile and still use its own engine to fire events. The goal is to make a report double pass, then use DBCrossTab event at first pass, and finally make band with DBCrossTab not visible at final pass. Mick[/img]
Hi Mick.
Thanks for your response and your time.
Some years ago we made application that generate 1 table with results data.
When data are prepared user select 1 template from list of possible templates.
Application open that template in show report.
In that years we made outputs with crystal report engine.
Because Crystal report VCL is dead now we decide to use FR VCL for our reports.
So all reports get 1 dataset with data from "outside" - we don't use inside report datasets.
Afterall this is not problem if we dont use graphs.
In graph there is not option to agregate this outside data.
Crystal graphs has that options [img]style_emoticons/<#EMO_DIR#>/sad.gif" style="vertical-align:middle" emoid=":(" border="0" alt="sad.gif" /> So we decided to use TDBCrossTableSource to agregate and populate this data into graph. Is maybe another way ho to group and agregate data without DBCross..... component ?[/img]
Nice trick with two passes ! [img]style_emoticons/<#EMO_DIR#>/rolleyes.gif" style="vertical-align:middle" emoid=":rolleyes:" border="0" alt="rolleyes.gif" /> I will try this tommorow. THAKS !![/img]
Edwin,
I'm confused a little bit as I don't see it clear. You say you don't use inside datasets. But you must use any TfrxADOQuery to get data you need for filling series in graph. The same or similiar data you need for DBCross. So if DBCross may be used in your report then you must use any query.
If DBCross is not the thing you really need then try to write your own agregate script like in classic Pascal. Yes I know this sounds maybe stupid but why not to test it. I would do it that way:
1. while not MyQuery.EOF do - here I would count how many different columns I have
2. alternate for the above - another query with select ColNames from ... group by ColNames
3. declare two dimensional array to put all ColNames and RowValues in
4. use for loop again - while not MyQuery.EOF do, to fill that array or fill data in series of graph
But all the above conception is nothing more than method of agregating data inside DBCross [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> But one can do more using FR script. Have a look at report example I attached with PDF files above. Mick[/img]
Hi Mick
We don't use inside dataset.
Datasets are defined outside report and assigned with code:
Report.EngineOptions.UseGlobalDataSetList := False;
Report.EnabledDataSets.Clear;
Report.EnabledDataSets.Add(DBData);
Report.EnabledDataSets.Add(DBSysData);
I just search solution for agregate this outside data and populate that data into graph.
As you wrote there are many possibilities.
Now it's my turn to ask you and (if you agree) to make my know-how a bit wider [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> Our standard end user apps use inherited ADOQuery (our own component) for SQL database. Very rarely we need to read other data - not from the main database our application manages. Then we use FR possibilities to comunicate by ODBC with that "foreign" data. This is not always a success story so we added some user functions to FR script. But it is rarely case so I didn't search for other solutions - until now ;-) Now I see you do something similar by manually Adding DataSets to the report. Can you show me the place where I can find more info about this (more than in frxClass) ? Mick[/img]
I am sorry for changing single v into double v (eg. w) in your name [img]style_emoticons/<#EMO_DIR#>/sad.gif" style="vertical-align:middle" emoid=":(" border="0" alt="sad.gif" /> Mick[/img]
Hi Mick.
I'm sorry [img]style_emoticons/<#EMO_DIR#>/sad.gif" style="vertical-align:middle" emoid=":(" border="0" alt="sad.gif" /> but only sources i have are - frxClass.pas - FR 4.6 Programmer manual (page 23, Multithreading) I hope this is helpfull. Afterall we are new in using FR. For all that years we are using CrystalReport. You said, that you wrote your own adoquery. Why did't you try to write your own frxDataset for FR ? Why you decide to use ODBC ? We are planing to write our frxDataset with TClientDataSet included. TClientDataSet is able to manage grouping and agregating. With that dataset we could manage graph's data. What is you opinion ? Edvin[/img]
Your info about Multithreading is OK [img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> Our inherited ADOQuery has one purpose - to simplify report design. We've been using ONE database engine for several years, so each FR query we need MUST know which DataBase it should be connected with - and without any extra job of report author. And when end user app starts it connects to DataBase engine automatically giving a kind of "link" to the DBEngine to FR queriues. Besides we have added some properties and methods above standard ADOQuery we need to simplify getting and manipulating data from a query. ODBC was used only in these rare cases when we met Excel or dBase files that our apps had to read, process and (sometimes) print its data. Instead of my opinion which is based on our long experience and may be useless for others, I can say what this experience is :-) All jobs that need any processing (including temporary tables) , filtering, master-detail relations, agregating or grouping data from database we send to queries. Hence FR bands and FR script have to make only page layout and fill objects with data from these well prepared queries. So if you plan to write own frxDataset you must be walking close to the road we walked[/img][img]style_emoticons/<#EMO_DIR#>/smile.gif" style="vertical-align:middle" emoid=":)" border="0" alt="smile.gif" /> Mick[/img]
Mick.
I agree with your opinion.
SQL Query is still best way to filter, agregating, sorting and ....
But, sometimes we report data stored in datasets in xml files or xml formated text streams.
In that case there is "clientdataset" only option to load that data, sort or aggregate them and then populate it to FR.
Agree ?
Yes, yes, yes....
Just need component ..... [img]style_emoticons/<#EMO_DIR#>/rolleyes.gif" style="vertical-align:middle" emoid=":rolleyes:" border="0" alt="rolleyes.gif" /> Let's go to work...[/img]