Dynamically displaying specific fields based on a parameter

[jpalmer150]

I have 24 fields in my database. One for each month for 2 yrs with numeric values.

JAN03 FEB03 MAR03 .... NOV04 DEC04

It was necessary to setup the database this way to minimize the record volume.

I'd like the report to display the last 12 months depending on a selection that the user makes. For instance, if the user selects the current period as FEB04 I'd like Crystal to show just 12 fields ending with FEB04.

Is there a way this can be done considering the way the database is setup? I already tried using a Union query to arrange the data vertically instead of horizontally. But when I did this the record count shot up over 10Million.

I guess I’m looking for a why to dynamically select fields instead of records.

Has anyone done this before?

 

[synapsevampire]

The design of the database is certainly a problem, and you're not limiting the size of the database by denormalizing the data in this fashion.

So to extract data dynamically you'll probably end up hardcoding some massive conditional formulas as the data has no intelligence now.

Fire the dba.

An alternative would be to create a View or a Union query to be used as a reasonable data source, normailizing the data into something like:

Date
Value

A union might look like:

select date(1/1/2003),jan01 from tabel
union
select date(2/1/2003),Feb01 from tabel
union
etc...

This would be done on the database side using whatever syntax the database uses.

You might also do this directly from Crystal using SQL in an ADO statement.

-k

 

[jpalmer150]

synapsevampire,

Your advice is good, and it's the first thing I tried (After trying to fire the DBA). The problem is that the DB is already over 1M records with the period fields combined into one record. When I separate them through a SQL Union query (Using the Crystal SQL designer) it expands the table to over 10M records. Crystal doesn't seem to be able to handle it.

Do I have any options for working with the fields separately? Or am I screwed?

 

[tektipdjango]

My suggestion :
Make a report with 12 detail sections each one with 12 fields

detail1
feb03 mar03...jan04

detail2
mar03 apr03...feb04

...

In each detail section put a suppress formula based on the value the user enter in the parameter ({@month} from a list of values)

if{@month}<>'jan04' (in detail1)
if{@month}<>'feb04' (in detail2)
...

So the report will show you the desired months

 

[tektipdjango]

excuse me, there is no need for 'if' in the suppress formulas

{@month}<>'jan04'

 

[tektipdjango]

another solution
assuming JAN03...DEC04 are the fields in the DB

make 12 formulas

formula1:
if {@month}='jan04' then FEB03 else
if {@month}='feb04' then MAR03 else
...
if {@month}='dec04' then JAN04

formula2:
if {@month}='jan04' then MAR03 else
if {@month}='feb04' then APR03 else
...
if {@month}='dec04' then FEB04

...

formula12:
if {@month}='jan04' then JAN04 else
if {@month}='feb04' then FEB04 else
...
if {@month}='dec04' then DEC04

put the formula fields in the report the way you want, your 12 months will be displayed!

This manner is a bit more complicated in formulas but it leaves more freedom for the aspect of the report

 

[synapsevampire]

How could you possibly have 1 million rows of 24 month period data? This must be one of the worst database designs I've come across in ages.

What is stored in these fields?

Assuming that it's a value, if you had instead set up a period table which contains dates, with a key to a table which had the values, you'd find that the overall size of the database would be comparable, but performance would imporve.

I don't even want to think about how the rest of the data is stored... I'd hire a consultant to assist you with the database.

I think the only way to get reasonable performance, reusability and simplified maintenance out the current design is to write Stored Procedures.

But I'd redesign the table structures. Or you can continue hard coding around the bad design whenever you need to use your data.

I try to reinforce to my DBAs the importance of desgining for data extraction, not optimal storage.

If you aren't going to use it, why store it.

-k

 

[jpalmer150]

tektipdjango I'm going to give your idea a try. It sounds like what I was looking for. Thanks!

synapsevampire you're definately right about the DB design. The data comes from an SAP B/W extraction pulled into an AS400 table. It holds the complete sales data for our fortune 500 company at the transaction level. I'm curious as to how the BW cubes store the data and in what format the DB receives it. I plan to find out why the AS400 table was designed this way and I'll try to update this thread im case you're curious.