Dynamic Columns in CR 9?

Hi...

I'm using Crystal Reports 9 with a SQL Server database. I'm attempting to create a report from a query that returns a dynamic set of columns based on a users input. The report displays the number of products sold to date for a user-specified date range (by month). For example, my if a user selects Products A, B, and C and then selects the date range from Jan 2004 to July 2004, the report will contain the following column headings:

Product
JAN_2004
FEB_2004
MAR_2004
APR_2004
MAY_2004
JUN_2004
JULY_2004

I'm creating the final output table using temp tables and actually adding columns (through the ALTER command) dynamically based on the date range specified.

Is there any way in Crystal to display this data dynamically so that the column headers are determined by what is exactly returned in the query?

I'm new to Crystal so please be detailed in your suggested solutions.

Thank You!
LVL 1
SuperLeonAsked:
Who is Participating?
 
frodomanConnect With a Mentor Commented:
Thanks, that's clear, but unfortunately that's going to be tough to do because to setup the crosstab you have to place the specific columns onto the report.  Do you have to use the temp table and/or does it have to be in this format?

If instead you can have your data come through like this:

- Product ID  
- Product Name  
- Product Group  
- Period    (this would be "Jan 2004", "Feb 2004", etc.)
- Qty

Virtually the same as you're using now except the field "Period" would be fixed and it would contain the dynamically determined months.

Data in this format will enable you to create the basic report in a few seconds.  Otherwise the only way you're going to be able to do it is by using an application langauge to create the report on the fly which isn't something I'd consider for a crystal newbie (it also requires either advanced or developer edition of CR9 -- standard / professional edition doesn't allow).

Let me know...

frodoman
0
 
frodomanCommented:
Instead of a 'standard' report you can create a crosstab report - this is exactly the situation a crosstab is intended for.

If you select your 'Product' field for the rows and your 'Date' field for the columns then the crosstab will automatically be created with the rows and columns corresponding to the data returned.  I assume that you have a quantity or value field that you're summarizing and this would go in the summary section of the crosstab.  

The date range parameters that you're using now would still work the same way - by filtering the records coming into Crystal they would filter the crosstab also.

The drawback is that the customization of crosstab reports is limited so if there are more complex things happening in your report that you didn't mention then the crosstab may not help you (and there's probably not much you can do differently than what you're already doing).

HTH

frodoman
0
 
SuperLeonAuthor Commented:
I'm not sure I understand...here's a sample of my table:

Product      JAN2004     FEB2004     MAR2004    
A               10             15              20
B                3              12              18
C                23            28              39
D                14            23              31

The totals are running totals of sales to date. So in the above table, the company sold 5 units of Product A in Feb (15-10).

I want that represented EXACTLY the way I have it in my query. Am I doing too much work in the query? Should I let Crystal do the work in the cross-tab? Again, I am brand new at CR and not familiar with Cross-Tab reports. If you could shed some light on all of this, it would be greatly appreciated.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
frodomanCommented:
>>> Should I let Crystal do the work in the cross-tab?

Yes!

Just so I understand, your table layout contains four fields - Product, Jan2004, Feb2004, Mar2004.  In the product field you have text values (A, B, C, etc.).  In Jan2004 you have number values (10, 3, 23, etc.) - same for Feb2004 & Mar2004.

Is this correct?  I just want to tell you how to do this w/out the confusion of guessing at your file layout...

frodoman
0
 
SuperLeonAuthor Commented:
Yes...that is :almost: correct. My table actually contains more columns:

SET COLUMNS:
- Product ID
- Product Name
- Product Group

DYNAMIC COLUMNS
- {month}_{year}
- {month}_{year}
- {month}_{year}
- {month}_{year}
- {month}_{year}
- etc.........

Again, the month columns are determined by the dates selected by the user before the report is generated. There can be any number of columns and the names of the columns are dynamic (month_year).

Please let me know if I'm not clear.  Thanks!
0
 
SuperLeonAuthor Commented:
Thanks Frodoman...

I'm gonna rework my query to output the data in your format...once that is done, I'm guessing your suggestion is to throw that data in to a cross-tab. If that's the case, can you help me determine what data to put into which areas of the cross-tab wizard (columns/rows/sum fields)?

Thanks again for your guidance!
0
 
frodomanCommented:
Sure, something like this for your crosstab:

Row:
{Product ID} {Product Name}

Column:
{Period}

Sum:
Sum of {Quantity}  (when you drag the Qty field in it should default so 'sum' but if it doesn't just change the dropdown).

HTH

frodoman
0
 
SuperLeonAuthor Commented:
You the man (or woman)! Worked like a charm!

Thanks Again!
0
 
frodomanCommented:
Glad I could help - frodoMAN

not frodoWOMAN :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.