• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 374
  • Last Modified:

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!
0
SuperLeon
Asked:
SuperLeon
  • 5
  • 4
1 Solution
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
frodomanCommented:
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
 
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now