Generating Sub Reports in a Loop

[chrissypchy]

Hello All,

I have this table,ctdd, that lists other tables in the same database. I need to query each of the tables listed in ctdd. They need to be queried individually so I need a sub report for each table's result. Is this at all possible? I would appreciate any help.

-Chrissy

 

[Ngolem]

yes this is possible but I cannot tell you exactly how given the dearth of info you have shown

essentially what you do is create a main report that will generate a record set of values that you would link with the subreport

the subreport is placed in the detail section and you create a group based on whatever that will cycle through the records

Jim Broadbent

 

[chrissypchy]

Sorry for giving so little information. Here is more details:
I have a table called ctdd that lists other tables in the database and some details reguarding the tables. The name of the table is under the attribute t_name. Each table under ctdd has a customer_id attribute and an attribute called status. In each table under t_name, I want to run a query to check if the status is <> 0. If it is, I want the customer's ID listed. In other words, I want the query select customer_id from &quot;t_name&quot; where status <> 0 to run in each table separately and print the results in one report. Some tables will return no customer_id's and others will return many and therefore I need them to not be joined. Hence, I need the results from each table to be done in a sub report. I am guessing that I will need some sort of formula with a loop in it. Am I on the right track? Thank you so much for responding. Any input would be appreciated.

-Chrissy

 

[kenhamady]

What version of CR?
Is the number of tables in CTDD fixed or does the table list change?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[chrissypchy]

Hi Ken,

It is Crystal Reports 8.5 and the list of tables will change. Any thoughts?

-Chrissy

 

[Ngolem]

Actually I don't think this will work.

If I understand you properly you want a report from tables which are unknown except that the table names are listed in another table - ctdd

Subreport databases must be defined I think and cannot be linked like variables. You can manually change databases with &quot;Location&quot; but I don't think this can be done on-the-fly.

Jim Broadbent

 

[chrissypchy]

That makes a lot of sense actually. Oh well. Thank you for your help.
-Chrissy

 

[kenhamady]

It might work in v9 (I said might), because you can add a parameter in a command and this could be used to fill in the name of the table in SQL. Then you could pass a value to this parameter from the main report.

It could be done in v8.x only if the list were a fixed list of tables. It would take a subreport for each section.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Ngolem]

Ken - If you wanted to hard code the subreports by placing them in separate sections you can pretty well do this in any version.

But I think he wants to dynamically pick up the database table for the subreport from this main table - ctdd - and then generate a subreport based on this table....I ASSUME the reports are similar (ie. all column names are the same) it is just the table name that is different. I don't believe this can be done...though I confess to be ignorant of CR9

Jim Broadbent

 

[chrissypchy]

Jim - yeah, the column names are the same

 

[synapsevampire]

The simplest way would be to create a stored procedure to return the data set, and depending upon what the result set is, you might just use dynamic SQL (meaning read the table names and construct SQL on the fly) with a Union to build the data set.

You've shared nothing about the type of database being used, or the data structure, so it's difficult to know.

As for Crystal handling this, you might get away with some elaborate SQL in an ADO connection, but it's definitely not the best means to handle this.

You could also use external code, such as VB to build the recordset and then pass it to the report.

-k

 

[kenhamady]

Jim,

v9 allows parameters in any part of the SQL, including the table name. So I think you could do a linked subreport with a table parameter, and have it pass the table name down for each subreport.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[synapsevampire]

You're right, Ken, CR 9 might pull this off if the table structures are the same.

You might even use CR 8.5 and some fancy SQL in an ADO/RDO connection to do this too.

I'd still opt for an SP if possible.

Unfortunately we don't read much of you these days, too overworked or are you bored with tek-tips?

-k

 

[kenhamady]

Nope, just hit a busy patch.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Ngolem]

Well Chrissy I guess CR9 is the only route...though I do see a slight window in SV's approach...As I read it somehow you would query all of the tables at once in some kind of union structure and bring back all the records in a single record set and then rather than using subreports the CR would be rather trivial with groupings by DatabaseName

I have no clue how to do this though, SV is the stored procedure man

Jim Broadbent

 

[chrissypchy]

Hi Jim,
I tried making it in a single Union Structure but the problem with that is I will get a different amount of results for each table and because it is an unknown as to what table will return any amount of values, it's hard to know where to form the unions. This is why I used sub reports. They kind of destroy the unions between the tables and allow for an unequal amount of results for each table. If there is another way to do this without using sub reports, I'd love to know what it is. I've only been using Crystal Reports since late May so I'd love to hear any input. If not, that's okay. I really appreciate everyone's responses.

-Chrissy

 

[Ngolem]

hmmm....is there a limited number of tables involved?

If the list is not that big and not changing then a simple union query (multi union) may be all that is required... then the report is simple.

One of the returned values would be the database name

You would then Group on this field and create a normal report...

Jim Broadbent

 

[chrissypchy]

Hi Jim,
Actually, if this did work, I'd need to use this along different databases so the number of tables would change unfortunately. I hate telling my bosses that I cant do something but oh well. Thanks for your help.
-Chrissy