Union query in SQL, how do I in Crystal?

[qwert231]

I am new to crystal. I have a table with 10 sets of Product and Quantity fields. I want a formula which will look in all 10 sets, and give me totals for all the Products therein. Here's an example:

Table:
Pack1 Qty1 Pack2 Qty2 Pack3 Qty3
a 1 b 1
b 2
c 1 b 1 a 1

So the report will show:
Pack Qty
a 2
b 4
c 1

What's the best way to do this? I'm using Crystal Reports for Visual Studio.

 

[IdoMillet]

Create a VIEW on the DBMS or a Crystal SQL Query, or if you are using Crystal 9, a SQL "Command" as the data source for the report.

The SQL would look like this:
-----------------------------------------
SELECT Pack1 as Pack, Qty1 as Quantity FROM My_Table
UNION ALL
SELECT Pack2 as Pack, Qty2 FROM My_Table
UNION ALL
SELECT Pack3 as Pack, Qty3 FROM My_Table
etc.
-----------------------------------------

Cheers,
- Ido


CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[qwert231]

Yea, I know how to do that, but am unsure where to do that in Crystal. I am using a Dataset for the data. But no other DB Engine that could run the query, so I have to do it in Crystal somehow, but I don't know where to put that formula/query.

 

[IdoMillet]

Crystal SQL Query (or Command if Crystal 9).

The Crystal SQL Query Designer is an option that may have not been installed in the default install of Crystal on your PC. Check for it under 'Crystal Reports Tools' from the Start menu.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[synapsevampire]

I suggest avoiding the Crystal Query Designer.

How is this dataset created?

WHy not just do the Union at that time?

I suggest that you post specifics about how the data is being created.

-k

 

[qwert231]

The data is read into the dataset from and XML file. If can tell me how to do a union query on an XML file, I'd be happy to try it.

From the dataset, I would like to get this report.

 

[synapsevampire]

Ahhh, I see.

So you have multiple datasets being read from XML datasources?

Crystal doesn't work with multiple data sources very well, one inelegant solution might be to use subreports for each data source, or build a single data source from within you code prior to sending the dataset.

-k

 

[qwert231]

Well, all this data is already in one table in one dataset.

 

[synapsevampire]

Then why would you need a Union?

Is the above a real representation of the values?

If so, just create formulas for each containing:

whileprintingrecords;
numbervar Atotal;
if Pack1 = "A" then
Atotal := Atotal +Pack1;
if Pack2 = "A" then
Atotal := Atotal +Pack2
...etc.

In the report footer use:
whileprintingrecords;
numbervar Atotal

-k

 

[qwert231]

Yea, but I don't know what will be in Pack1, Pack2... Could be an infinate ammount of posabilities. In SQL I can do this with a Union and it works real quick. No matter what I put in for Packs numbers, Alpha, strings... it works real well.

 

[synapsevampire]

Unfortunately CR doesn't perform a Union, it can pass SQL to a database to perform one, but it is not a database engine.

The real problem for CR is that the dataset is poorly designed for your purposes.

Check this example of performing a Union in XML using pipe |:

http://www.vbxml.com/xsl/exampleunion_nodeset.asp

Otherwise you mkight have to leverage a real database, XML is useful, but in this case probably not very efficient.

-k