Viewing Records With SUBREPORT in Excel 2

[IbeKaba]

I'm working with Crystal Report 9.
When I export a report that has a subreport in it to Excel (Excel 2002)using the "Data Only" option, instead of printing on the same row as the record, the subreport value is printed in its own row. How can I keep this from happening, and keep this value on the same row as all other values for that record?

Example: Let's say the DATE value is return by a subreport.
ID NAME DATE
121 Joe
Dec. 21 2005

As you can see, "Dec. 21 2005" needs to be under DATE.

Any helpp will be greatly appreciated.

Thanks,
ibe

 

[synapsevampire]

Return the date value using a shared variable to the main report.

You don't state where the subreport is, nor where you are displaying the columns in the main report, so I can't be too specific, but assuming that the subreport is ni the details (which is a horrible idea), you'd create 3 formulas:

Main->Group Header formula:
whileprintingrecords;
datevar MyDate := cdate(1900,1,1) //reset it
// in case the subreport doesn't find a date

Subreport formula:
whileprintingrecords;
datevar MyDate := {table.date} // set it

Main->Group Footer formula:
whileprintingrecords;
datevar MyDate;
MyDate

Consider posting example data, and expecte output, and also include the database/connectivity used if you want solutions as opposed to discussing why something you tried did not work. Get the solution first, then if need be discuss theory.

-k

 

[kskid]

I experience the same problem in Crystal XI. The only way I know is through shared variables. I create and moved my subreport to a section ahead of the section being exported. I suppress all sections in the subreport and create a formula that pulls in the date you need

@table_date

shared datevar tableDt := {table.date};

In the main report, create another formula and place it in the date field you want exported

@table_date
shared datevar tableDt;

tableDt

You will still wind up with a blank row between each data row but you can easily sort the table to eliminate it.

-lw

 

[IbeKaba]

Again, I'm new with Crystal
I've never worked with shared variables before. But if I understand the concept, by using it, I can take a value from the subreport and display it on the main without having to "insert" the subreport directly into the main report....right? Cool.

However, it doesn't seem to be working for me for this report. From your suggestions and reading the reference guide, I was able to come up with this

In my subreport
shared dateVar ExpDate := {@ConvD};
//my field has multiple string and date entries in it, so after using select criteria to pinpoint the exact entry I want, I convert it to date using the Cdate function. That's how I have the formular {@convD} instead of the table field

In my main report
shared datevar ExpDate

I imagin by placing this new field (EXPDATE) in my main report it would return {@ConvD}. But it doesn't. Instead I get blank. Though {@ConvD} by itself does return a date. What am I doing wrong. I placed it in the Detail section, then the Footer, the Header, I even created a grouping and placed it in the group footer...but I still get blank.

SynapseVapire, you said placing the subreport in the Detail section is a horrible idea. Why? I do have it in there. And actually have it in the same place in many reports I've created in the past. Please help me avoid a dose of headache or worse yet, providing people with bad data.

Thanks guys (and gals)
ibe

 

[synapsevampire]

A subreport in the details is ineffiecient as the query fires for every row in the main report.

If the subreport is in the details, right click the details and select insert section below, and place the formula AFTER the section where the subreport fires.

Also note that your formula is not like my formula, if you decide to code differently, expect different results.

Try adding the WHILEPRINTINGRECORDS;

I also asked for the database and connectivity used, which you also ignored. I tend to bug out of posts where people ask questions and then ignore my responses, I'm sure you can understand this. Knowing this might allow us to help you avoid subreports by using REAL query to pull what you need, either using Views or Stored Procedures on the database side, or pasting in SQL in an Add Command for the datasource.

In general subreports are eeeeevuuuuuhl for performance reasons, not accuracy

-k

 

[lbass]

I was able to solve the problem of a subreport wrapping to another line on export by using a formatting feature in XI which might be available in 9. Select all the detail items in the row, including the subreport->format objects->make same size->same height. It also helps to snap the fields to both horizontal and vertical gridlines. Worth a try.

-LB

 

[IbeKaba]

ibass, that's exactly what I was thinking. I really thought this was such a small problem that there had to be an automated solution like the one you mentioned. But unfortunately I don't this this is available in Crystal IX, because it didn't work for me.

Synapsevimpire, sorry about not responding to your questions. I didn't mean to ignore them. My connection is ODBC (RDO) and the database is Explorer (some old thing I don't yet know much about since I just started working here. It's pretty old, that I know). And though I forgot to mention it in my previous post, I did put whileprintingrecords in my formular.

Anyway, late yesterday, when everyone else had gone home, I was still struggling with this report. At about 7pm, I discovered, with the formular I was almost there. All I needed to do (as you indicated in your post today) was "Right click the details and select insert section below, and place the formula AFTER the section where the subreport fires.) Worked beautifully! I finally was able to see my shared variable on my report. I'm making this work, and for now my manager is cool with it.

However, in Excel, since I had to keep the subreport visible, it still prints below the rest of the fields in the record. By sorting and deleting, my manager is able to get around this, but I was hoping I can I could save her the headache of doing any editing once she exports the report. So if you know of anything else I can do to ease this burden, please let me know. In the meantime, both she and I are making due with what I've got.

Thanks all,
ibe

 

[kskid]

You don't have to keep the subreport data fields visible. Just suppress all the sections. The formula creating the shared variable will still execute.

 

[IbeKaba]

Genius! I got to say, this is absolutely the best forum on the Net. Good, quick response, and best of all patience with a novice like me. I love it! I simply love you guys!
Thank you, thank you, thank you...
ibe