Hi,

I seem to be revisiting my nemesis yet again.

Throughout my development, I come across the need to produce data in a
crosstab type result. In the past I have used a component called
TxQuery, but unfortunately this component has a few issues that can
cause problems from time to time.

I've come across the "cross tab result sets" article that Tim has posted
at
http://www.elevatesoft.com/articles?action=view&category=edb&article=cross_tab_result_sets_elevatedb_scripts,
but unfortunately in my case I need a dynamic result set. (As I will not
know the number of fields for the result set until it's actually ran,
unlike the example that has a fixed result of 12 - one for each month).

I was just wondering what other people might be using themselves. I know
that my report writer has a component that puts a result on the report,
and devexpres has pivotgrid, but I'm really looking for something that
will give a result in a TDataset format if possible, and that is native
to Delphi?

Just wondering if anyone here has any suggestions or experience with this?

Thanks & Regards

Adam

"Adam H." wrote:

>>I've come across the "cross tab result sets" article that Tim has posted
at http://www.elevatesoft.com/articles?action=view&category=edb&article=cross_tab_result_sets_elevatedb_scripts,
but unfortunately in my case I need a dynamic result set. (As I will not
know the number of fields for the result set until it's actually ran,
unlike the example that has a fixed result of 12 - one for each month).<<

So why can't you just do a Group By on the table column that comprises the columns across the top of the crosstab to determine how many columns you are going to have, then build a temporary table accordingly?

I haven't seen any crosstab components for for Delphi that leaves the result in a TDataset so you will have to roll your own. The commercial crosstab components usually link the results to a visual grid so the user can interact with the summarized results and also produce multiple summary levels.

The only 2 that come to mind is FastCube from FastReport http://www.fast-report.com/en/product/fast-cube-2/?pk_campaign=torry&pk_kwd=fc2 and DevExpress's PivotTable https://www.devexpress.com/Products/VCL/ExPivotGrid/

Barry

Hi Barry,

Thanks for your reply...

> So why can't you just do a Group By on the table column that comprises the columns across the top of the crosstab to determine how many columns you are going to have, then build a temporary table accordingly?

That's one possibility. Normally I like to keep things as simple as
possible. Results like this I prefer to have all in a SQL statement too
(so I can replicate for debugging outside of Delphi later, etc), but it
is an option if I can't find anything else...

> I haven't seen any crosstab components for for Delphi that leaves the result in a TDataset so you will have to roll your own. The commercial crosstab components usually link the results to a visual grid so the user can interact with the summarized results and also produce multiple summary levels.
>
> The only 2 that come to mind is FastCube from FastReport http://www.fast-report.com/en/product/fast-cube-2/?pk_campaign=torry&pk_kwd=fc2 and DevExpress's PivotTable https://www.devexpress.com/Products/VCL/ExPivotGrid/

Thanks for that. I already have DevEx components, but unfortunately I'm
looking for something in a TDataset - as you've mentioned the others use
visual grids.

The TxQuery component I've came across seems like a perfect solution
(and it's Open Source Free), except for the bug that I've found, that no
one seems to know how to address.

(On rare occasions it doesn't pivot and a particular record will end up
having a new record for each record that should be pivot'd). I've been
able to replicate the issue on demand, but so far no one seems to know
why it occurs.

http://code.google.com/p/txquery/issues/detail?id=44

Best Regards

Adam

Dear Adam H

I write a totally vanilla query which just returns all the rows of the table and then use a Fast Report third party component called "FastCube" to display the result as a cross-tab/decision cube format.

This may not be what you want at all ... but it is a powerful component, cheap & worth a look

Adam Brett,

If you don't mind me asking, why did you go with FastCube instead of DevExpress PivotGrid? Is there much difference between the two products (besides price)? Is one faster than the other? Or offers features you can't live without?

TIA
Barry

Adam,

<< but unfortunately in my case I need a dynamic result set. (As I will not
know the number of fields for the result set until it's actually ran, unlike
the example that has a fixed result of 12 - one for each month). >>

That shouldn't matter.  All you need to know is how to label them.  Since
the EDB SQL/PSM language is 100% dynamic, you can construct all SQL
on-the-fly, just as if you were doing so in Delphi code.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Adam B,

Thanks for your suggestion. Unfortunately FastCube operates in a similar
way to Devexpress (which I already have) and does not populate to a
TDataset.

I'm really looking for something that Populates to a TDataset, but
thanks anyway.

Best Regards

Adam.

Good Morning Tim,

> That shouldn't matter.  All you need to know is how to label them.
> Since the EDB SQL/PSM language is 100% dynamic, you can construct all
> SQL on-the-fly, just as if you were doing so in Delphi code.

Thanks for your reply! From the example given:

   EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE MonthlySalesCrossTab
(
   SalesPerson VARCHAR(60),
   January DECIMAL(20,2) DEFAULT 0.00,
   February DECIMAL(20,2) DEFAULT 0.00,
   March DECIMAL(20,2) DEFAULT 0.00,
   April DECIMAL(20,2) DEFAULT 0.00,
   May DECIMAL(20,2) DEFAULT 0.00,
   June DECIMAL(20,2) DEFAULT 0.00,
   July DECIMAL(20,2) DEFAULT 0.00,
   August DECIMAL(20,2) DEFAULT 0.00,
   September DECIMAL(20,2) DEFAULT 0.00,
   October DECIMAL(20,2) DEFAULT 0.00,
   November DECIMAL(20,2) DEFAULT 0.00,
   December DECIMAL(20,2) DEFAULT 0.00
)';



.... it appears to be something that you've written manually, and hasn't
been dynamically created within EDB?

Likewise, all other scripting afterwards seems to reference to the
fields that need to be dynamically created by actual name.

I was wondering if there's another example that I should be looking at
that I've missed that shows how to dynamically create a cross-tab
result, or whether there's something I've missed?

Thanks & Regards

Adam.

Adam,

<< ... it appears to be something that you've written manually, and hasn't
been dynamically created within EDB? >>

Yes, but it's just a string, so you can create it any way that you want,
populating the column names dynamically.  Of course, you'll just need to get
the column names before you start any actual processing, and then do some
mapping when doing the fetching, etc.  But, it's all very do-able.

<< Likewise, all other scripting afterwards seems to reference to the fields
that need to be dynamically created by actual name. >>

Yes, but again, they are all just string values for the column names, so you
can populate them dynamically.

<< I was wondering if there's another example that I should be looking at
that I've missed that shows how to dynamically create a cross-tab result, or
whether there's something I've missed? >>

Send me an example of what you want to do, and I'll work something up and
post it here.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

> Send me an example of what you want to do, and I'll work something up
> and post it here.

Thanks for your offer. I have sent you an email.

Unfortunately I'm still trying to get my head around writing scripts
with memory/temporary tables without referring to existing examples.

Thanks & Regards

Adam.