I can't select two different records from the same table

[joelwenzel]

Hi,

I have a fact_main table that contains a value, a time id (which is joined with a time dimension), and a DescriptorID (which is joined with a dim descriptor table). The descriptor describes the value. So if the value is an invoice, then fact_main contains a value and an id associated with invoice. If the value is a sale, then fact_main contains a value and an id associated with sale. (now there are two records in my fact_main, one for sale and one for invoice)

I want to display all values as separate fields in my report. So, basically, I want to group my report by the date, and display the invoice value (record 1) and the sale value (record 2) for each date. I can copy the invoice value over fine, but when copy fact_main.value and use the Select Expert to set the descriptor to sale, all the values change to sale. I can only display the sale value, or the invoice value, but not both. Any idea how I can fix this?

 

[lbass]

Why don't you show us some sample data of how the two fields display after grouping and WITHOUT using the select expert. Also add the descriptor field to the details section for the sample.

If you use the select expert to set the descriptor to sales, you are limiting all records coming into the report to sales records.

-LB

 

[joelwenzel]

ok, here is some detailed information.

Table Fact_Main
Value
TimeID
DescriptorID

Table Dim_Time
TimeID
Year
Month

Table Dim_Descriptor
DescriptorID
FieldName


So, for data from 10/2005 to 11/2005 I have

GroupHeader: 10/2005
Value: 10.34

GroupHeader: 11/2005
Value: 5.6

So, it groups properly...however, the value shown is only the value for invoices. What I really want is

GroupHeader: 10/2005
InvoiceValue: 10.34
SaleValue: 12.4

GroupHeader: 11/2005
InvoiceValue: 5.6
SaleValue: 2.3

But what I end up getting is

GroupHeader: 10/2005
InvoiceValue: 10.34
InvoiceValue: 10.34

GroupHeader: 11/2005
InvoiceValue: 5.6
InvoiceValue: 5.6

Or

GroupHeader: 10/2005
SaleValue: 12.4
SaleValue: 12.4

GroupHeader: 11/2005
SaleValue: 2.3
SaleValue: 2.3

SaleValue and invoice value are two different rows in fact_main. Here is what the data in the tables is

Table Dim_Time
TimeID Year Month
1 2005 10
2 2005 11

Dim_Descriptor
DescriptorID FieldName
1 Invoice
2 Sale

fact_main
Value TimeID DescriptorID
10.34 1 1
12.4 1 2
5.6 2 1
2.3 2 2

 

[lbass]

If you remove any record selection criteria, I think you should get the display you want just by laying the value in the detail section. If you place the following in your detail section, what do you get for results?

{fact_main.value} {fact_main.descriptor}

-LB

 

[joelwenzel]

if I put

{fact_main.value} {fact_main.descriptorID}

I get

10/2005
10.34 1
10.34 1

11/2005
5.6 1
5.6 1

Ok, I gave you a simplified version of my problem. I can actually get the proper values

10/2005
10.34 1
12.4 2

11/2005
5.6 1
2.3 2

If I just put {fact_main.value} {fact_main.descriptorID} in the details section like you say, however, what i really want is shown below. Basically, I have a format in my details section and I want to copy multiple values from fact_main into it so that they can be put in the proper format. Having just the rows of fact_main repeated down my report is not the right format.

10/2005
"header"
"some other numbers"
...
$ 10.34 1

...
"Some other numbers and headers"
...
% 12.4 2

11/2005
"header"
"some other numbers"
...
$ 5.6 1

...
"Some other numbers and headers"
...
% 2.3 2

 

[joelwenzel]

I guess one possibility would be to import the table multiple times....but that is really messy

 

[lbass]

If you want help you need to share what these other numbers and "headers" are and where they are coming from. Without any additional information, one approach might be to additional detail sections_a and _b and then place the value field in detail_c.

-LB

 

[IdoMillet]

At level 1, group on Date.

At level 2, group on descriptor.

Cheers,
- Ido

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

http://www.MilletSoftware.com