Solved

Dynamic Columns in CR 9?

Posted on 2004-08-05
9
370 Views
Last Modified: 2008-03-04
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
Comment
Question by:SuperLeon
  • 5
  • 4
9 Comments
 
LVL 42

Expert Comment

by:frodoman
ID: 11727711
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
 
LVL 1

Author Comment

by:SuperLeon
ID: 11727826
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
 
LVL 42

Expert Comment

by:frodoman
ID: 11728621
>>> 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
LVL 1

Author Comment

by:SuperLeon
ID: 11728806
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
 
LVL 42

Accepted Solution

by:
frodoman earned 250 total points
ID: 11728922
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
 
LVL 1

Author Comment

by:SuperLeon
ID: 11729184
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
 
LVL 42

Expert Comment

by:frodoman
ID: 11729445
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
 
LVL 1

Author Comment

by:SuperLeon
ID: 11729667
You the man (or woman)! Worked like a charm!

Thanks Again!
0
 
LVL 42

Expert Comment

by:frodoman
ID: 11729690
Glad I could help - frodoMAN

not frodoWOMAN :-)
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question