?
Solved

Mail Label Report Format. Want a single record details to be printed N no. of times.

Posted on 2005-04-05
16
Medium Priority
?
404 Views
Last Modified: 2008-02-01
Hi !!
I have created a Mail Label report format in a crystal report 8.0
Its purpose is to print a particular product details, like "Code", "Description", "MRP", etc in a label sheet in 3 Cols X 8 Rows.
The problem I'm facing now, is that it shows the details of all the products in the table and if I give a filter for displaying that product alone, it gets printed only once.
What I need is that it should print for all the 3 cols and 8 rows.
Is there any way to make it print repeatedly ?

***************************************
And if not, it brings me to the other side of the issue.
How do I display in a single select query, a particular row of a record, N no. of times.
To explain better :
A Table "Employee" has fields "Name" and "Salary"
What I need is that a record of a particular employee, "John Doe" to be displayed 20 times (or N times) when querying a select statement.
How could we do it ?

AND I'm using MS SQL SERVER 2000.

Thanks and Regards
fuchsiasoft

PS: Since I have only 145 points, I could give only 100 points for this. If I had more than 500, I would have given that much itself.
0
Comment
Question by:fuchsiasoft
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 10

Expert Comment

by:Spykair
ID: 13706423
You can use subreport. Place the subreport in it's own detail section.
Group that section on Product code and instead of using the record selection, you can link the subreport on "Code".

Hth,
Spykair
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13706432
Yes.

I'll try to find the example.

mlmcc
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13706449
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.

 

Author Comment

by:fuchsiasoft
ID: 13706949
Hi SpyKair and mlmcc !!
Thanks for the input.
The example zip's description (from the forum link provided by mlmcc) promises to be the exact solution I'm searching for. Particularly this file : "CR9_labels_looping_basic_syntax_link.rpt" in the example zip. But the irony is I have Crystal Reports 8.0 and this is a V9.0 example. ;)

Right now I'm doing as mentioned in the earlier forum. Creating a temp table to populate n no of times and display it in report.

But I couldn't understand the method explained by SpyKair. If I were to use subreport and link the subreport with the main report's code, still it is displaying only one label of the particular product, which I'm sending as a parameter.

Can any of you please explain it further more. If you know the working of the file "CR9_labels_looping_basic_syntax_link.rpt" given in the link "http://support.businessobjects.com/communityCS/FilesAndUpdates/cr9_label_samples.zip.asp?recDnlReq=Record&dnlPath=cr9_label_samples.zip"
 could you please explain it to me.

Thanks again !!

PS: If  I couldn't find an answer, I guess I have to split the points for trying to help me.( Measly 100 points though, that's all I have right now !!)




0
 
LVL 9

Assisted Solution

by:Outin
Outin earned 75 total points
ID: 13706975
Regarding the second part of you question:

In some other situation I had the same problem, which I solved by first creating a table (or view) in the db containing just numbers 0 to 99999.

After that any query result can be multiplied using a query similar to this:

SELECT
a.field1
FROM   view_with_data a,
view_with_numbers b
WHERE  b.number < 5

This will repeat a.field1 5 times.

Greetz,

Outin
0
 

Author Comment

by:fuchsiasoft
ID: 13707439
Hi Outin !

How do you populate the numbers from 0 to 9999 in a View ? I do not want to create a temp table. Not a policy. Just wanted to know, how long we could manage without hacks and temporary solutions.

And thanks for the input.
Your example works great if the number is populated previously in a table and called as you have mentioned in your example.

Regards
fuchsiasoft
0
 
LVL 9

Expert Comment

by:Outin
ID: 13707677
I myself did use a query based on information already in the db (a substring from order-numbers),
but query below does the trick too....

Example counts up to 9999 (btw I use Oracle, so query might need adaption..hope you have some void table with only one result row as 'dual' is in Oracle...):

SELECT to_number(d.nr || c.nr || b.nr || a.nr) numbers
FROM  
(SELECT 0 nr FROM   dual
UNION
SELECT 1 nr FROM   dual
UNION
SELECT 2 nr FROM   dual
UNION
SELECT 3 nr FROM   dual
UNION
SELECT 4 nr FROM   dual
UNION
SELECT 5 nr FROM   dual
UNION
SELECT 6 nr FROM   dual
UNION
SELECT 7 nr FROM   dual
UNION
SELECT 8 nr FROM   dual
UNION
SELECT 9 nr FROM   dual) a,
(SELECT 0 nr FROM   dual
UNION
SELECT 1 nr FROM   dual
UNION
SELECT 2 nr FROM   dual
UNION
SELECT 3 nr FROM   dual
UNION
SELECT 4 nr FROM   dual
UNION
SELECT 5 nr FROM   dual
UNION
SELECT 6 nr FROM   dual
UNION
SELECT 7 nr FROM   dual
UNION
SELECT 8 nr FROM   dual
UNION
SELECT 9 nr FROM   dual) b,
(SELECT 0 nr FROM   dual
UNION
SELECT 1 nr FROM   dual
UNION
SELECT 2 nr FROM   dual
UNION
SELECT 3 nr FROM   dual
UNION
SELECT 4 nr FROM   dual
UNION
SELECT 5 nr FROM   dual
UNION
SELECT 6 nr FROM   dual
UNION
SELECT 7 nr FROM   dual
UNION
SELECT 8 nr FROM   dual
UNION
SELECT 9 nr FROM   dual) c,
(SELECT 0 nr FROM   dual
UNION
SELECT 1 nr FROM   dual
UNION
SELECT 2 nr FROM   dual
UNION
SELECT 3 nr FROM   dual
UNION
SELECT 4 nr FROM   dual
UNION
SELECT 5 nr FROM   dual
UNION
SELECT 6 nr FROM   dual
UNION
SELECT 7 nr FROM   dual
UNION
SELECT 8 nr FROM   dual
UNION
SELECT 9 nr FROM   dual) d

will return

NUMBERS
-----------
0
1
..
9998
9999


Hth,

Outin
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13713643
I believe the basic idea in the example will work in CR8.  I may be wrong.

mlmcc
0
 

Author Comment

by:fuchsiasoft
ID: 13714346
Hi mlmcc !!

Of course (at least I'm sure of it ) that the code inside the example would be much similar to the one I'm looking for in CR8. But the problem is, since it is a CR9 file, it could not be opened in Crystal Report 8. It is saying invalid file. If some one (having CR9) could convert it into a CR8 file or just explain it to me to working in the example, I will be really grateful.

Thanks !!
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13719827
0
 

Author Comment

by:fuchsiasoft
ID: 13735710
Hi !!
I'm sorry ! But I guess you have missed out the line that I had mentioned, that I have the same example file, but couldn't open it since I have CR8 ,
and this particular examples are in CR9. ( Date: 04/05/2005 06:39AM PDT )

;)

I wanted to know the working of the example file. All I have to know the formula usage / format usage / layout usage, etc for displaying
this mail label format.
**********************************
I will explain my problem again !!

How do I display in a single select query, a particular row of a record, N no. of times.

A View "QryProduct" has fields "Code" ,"Description", "Category" & "MRP"
What I need is that a record of a particular product (One product only, selected by the user), "APPLE" (for eg) to be displayed 20 times (or N times) when querying a select statement.
How could we do it ?

**********************************

Thanks for the effort !!
fuchsiasoft

PS: And this could be still complicated, by setting a parameter, such that the printing starts from a particular row and column. For eg, if I want it to start displaying from 4th row - nd column, it should start from there.
The other previous columns and rows are suppressed.

But that's for another topic. I want the answer to this particular question. Sorry for taking up so much of your valuable time.
( I really appreciate that a "Genius", is interested in solving this problem, though the points are low and it is taking up so much time)
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 225 total points
ID: 13742053
>>( I really appreciate that a "Genius", is interested in solving this problem, though the points are low and it is taking up so much time)
Thanks for the compliment.  How do you think I earned that certificate.  Certainly not by ignoring the low point q's.  I even help out on 0 point q's in some of the TAs.


Here is another idea
http://support.businessobjects.com/library/kbase/articles/c2001548.asp

Starting point idea
http://support.businessobjects.com/library/kbase/articles/c2004915.asp

An idea for CR7
http://support.businessobjects.com/library/kbase/articles/c2000250.asp

mlmcc
0
 

Author Comment

by:fuchsiasoft
ID: 13759585
Sorry for the delay !! [ (De)Pressing problems ]

Hi mlmcc !
Thanks for the links ! The first one was not the one I was looking for !

The second and third are definitely the kind of solution I was looking for.
But the problem in the third is that it allows to duplicate in row wise, but not column wise.
i.e., If I had 8 rows X 3 Columns, it is duplicating in the 8 rows but not in the columns.

The starting point idea was great (second example); but that was for starting rows only.
I guess we are just about there. I tried my hand in that site's search options. But couldn't hit any
mother lode.

I'm going to try using the last two techniques further and find if I could hit upon a solution !!
Thanks
fuchsiasoft

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13767948
Good luck.

mlmcc
0
 

Author Comment

by:fuchsiasoft
ID: 13798613
Hi !!
I guess, that is it !!
I couldn't find a solution for this.
The only way option (I could perceive) was to create a temp table and insert the particular value for "n" no of times and display it as in label format. And use the technique mentioned by mlmcc as in
*******************
Starting point idea
http://support.businessobjects.com/library/kbase/articles/c2004915.asp

An idea for CR7
http://support.businessobjects.com/library/kbase/articles/c2000250.asp
******************

If I do not get any ideas or suggestions in the next week, I guess I have to award points to mlmcc and outin for helping me.
Regards
fuchsiasoft
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13923623
Glad i could help

mlmcc
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

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 …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

762 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