Variable number of columns

[mgakhar]

hi,
Im using a crosstab to display a number of columns in my report. I have a query which can return a variable number of columns. For example, the input parameter is Demand Upto - where the user will input a date upto which I need to display weekly demand. So if the user enters 12/30/2006, I need to display 47 weeks of demand. However, if the user enters 3/31/2006 then I need to display just 8 weeks of demand. As a result the number of columns in my report becomes different based on the users input.

How can I achieve this in a crosstab?

Im using CR-10 to develop the report and display the output. The database is a proprietary OODB.

Thanks,
MG.

 

[lbass]

What is the beginning date? It looks like you want weeks starting in February. You could just hard code that in your record selection formula, and use the parameter for the end date, as in:

{table.date} in date(year({?enddate}),02,01) to {?enddate}

In the crosstab, you would add {table.date} as your row or column field and then click on "Group options" and choose "Print on change of week".

-LB

 

[mgakhar]

hi lbass,
Actually the problem is that since the date range is variable, the number of columns returned by my query is also variable. So Im not sure how to put them on the report. So if I run the report it returns say 10 columns and if I re-run the report for different dates, it might return 15 columns.

How can I can put this on the report?

 

[lbass]

Crosstabs will automatically accommodate different numbers of columns, creating virtual pages if necessary, so I'm unclear on what the issue is.

-LB

 

[mgakhar]

Well Crosstabs can accomodate different number of "CrossTab columns". However, what Im referring to is the no of fields that the query returns.

MG

 

[synapsevampire]

Place the date field in as a column or group within the cross-tab and select options->For each week.

The report will dynamically expand, which is why LB (and I) don't understand what you are concerned about.

Are you saying that this query is increasing the number of columns returned?

If so, that's a good design for a bad reporting tool, but a bad design for a good one.

Crystal will also be confused by such a thing because the data source is changing. so I think that we have a basic misunderstanding here.

-k

 

[lbass]

As SV says, I still don't see what the issue is. Maybe you could provide an example.

-LB

 

[mgakhar]

Well I already have a crosstab and it already has a field added to the column.

So the output of my query is as follows -

Item Main Item Qty Per Demand1 Demand2
A XYZ 1 100 200
B XYZ 1 150 200
A GGG 2 100 200
C GGG 3 100 100

So Item is a component which goes into making the Main Item. So I basically need 1 of A to 1 of XYZ and I need 2 of A to make 1 GGG. Also Demand1 is my total demand for the component for week 1 and Demand2 is my total demand for week 2.


Using a crosstab my report looks like this -

Item Demand1 Demand2 XYZ GGG
A 100 200 1 2
B 150 200 1 0
C 100 100 0 3

Here the Main Item is my column, Qty Per is my Summary and Item, Demand1, Demand2 is my rows in the Crosstab.

Now my query is such that the user could either ask for Demand for the next 10 weeks or maybe just the next 4 weeks. So basically the output of my query could have Demand1, Demand2 ... Demand10 or just Demand1 .. Demand4.

Now how do I create the report such that I can accomodate for a variable number of Demand fields.

Thanks,
Manish.

 

[lbass]

Maybe try a formula for your row field that concatenates the Item and the Demand fields like this, where
{?Demand} is a number parameter in response to the prompt "Demand for the next n number of weeks":

{table.Item} +" "+
(if {?Demand} >= 1 then totext({table.Demand1},0,"") else "") +" "+
(if {?Demand} >= 2 then totext({table.Demand2},0,"") else "") +" "+
if {?Demand} >= 3 then totext({table.Demand3},0,"") else "") //etc. up to the maximum number of demand fields.

-LB