Problem With "LABELS"

[khan007]

I have a problem with labels. User wants only one report and wants to allow label type as parameters.
They want to choose from Avery 5160, Avery 5162, Avery 5260 and Avery 5261.

I was thinking of creating four sub reports.
Initially in each subreport I need to go to fromat section and Format with multiple colums and set the layout of each type.
In main report calling four different sub reports with each label type in four detail sections.
Then in main report use a formula based on user parameter to supress other three.

I got a suggestion from one instructer to avoid subreports by creating multiple detail sections, formatting each differently,
and suppressing all but one according to the parameter

When I tried to use multiple detail sections, the Format with multiple columns option is disabled, (even not appeared.)
So it means I cannot Format different detail sections in one report, second it doesn't have a option to show # of labels Across
the page, when i selected Avery 5160, it has default three columns but in my report only two appears.

I faced the same problem with subreports too. it is showing two columns in Avery 5160, which has basically three.

Please let me know, if possible how can I have a single report prompting multiple label types.

 

[synapsevampire]

It's time forthe student to snatch the pea from the instructors hand.

Create the different mail label reports. Insert them as subreports, and make sure that you've allowed enough width.

The theory works fine, I just tested it and different formats (3x6 vs. 2x7) showed up in different sections just fine.

-k

 

[doyledirk]

Hi
If you are using 8.5 (migrating to 9 and working on the problem )I have a sollotion for yyou. Try to stear clear from subreports inorder to display lables. The report will start to use up a large amount of resources. I have had the same problem as my reports have to be in Multiple languages and therefore the lables have to change depending on the default language the user has chosen.

To solve this I stored the labels on a simple table and passed them using asp to the report.

1.retrieve the labels and store in an array

<%'on error resume next
'session.timeout = 1


'***************************************************************************************************************
stradminlevel = session(&quot;adminlevel&quot
strconn = &quot;Provider=sqloledb; Data Source=IPPFSQLDEV\MAINTENANCE; Initial Catalog=IPPF_IMS; User Id=sa; Password=xxxxxx&quot;
conn.Open strconn

function getLabel(string,string) which uses the IMS_Label to return the language specific labels from the IMS_DATADIC Table filtered by table which is an attributre of the table used to group labels. It is used to build the Report by
'***********passing in the language specific labels. Public Function getLabel(TabName,FieldName)
'response.write TabName
strTabName = trim(TabName)
strfldName = trim(FieldName)
'strLancode = trim(LangCode)
set cmd = Server.CreateObject(&quot;ADODB.Command&quot
set cmd.ActiveConnection = conn
cmd.CommandType = adcmdstoredproc
strSQL = &quot;IMS_Label&quot; & &quot; '&quot; & strTabName & &quot;','&quot; & strfldName & &quot;','&quot; & strlan & &quot;'&quot;
'Response.Write strsql
'Response.End
Set RsLabel = CreateObject(&quot;ADODB.Recordset&quot
RsLabel.Open strSQL, Conn, adOpenKeySet, adLockPessimistic, adCmdText
If Not RsLabel.EOF Then
ssLabel = trim(RsLabel.Fields(0))
if trim(RsLabel.Fields(&quot;Mandatory&quot) <> &quot;&quot; then
getLabel = ssLabel & &quot;&quot; & &quot;<a name='asterisk' style='color: #990000'>&quot; & trim(RsLabel.Fields(&quot;Mandatory&quot) & &quot;</a>&quot;
else
getLabel = ssLabel
end if
Else
getLabel = &quot;&quot;
End If
RsLabel.close
End Function




%>


2. Pass variables to the report
<%@ LANGUAGE=&quot;VBSCRIPT&quot; %>
<!-- #include FILE = &quot;adovbs.inc&quot; -->
<!-- #include FILE = &quot;Crystal.asp&quot; -->
<%
' -------------
' -- New ASP --
' -------------
reportname = &quot;IMSReport2MCurr.rpt&quot;
strVB = trim(Request.QueryString(&quot;VB&quot)
strid = trim(Request.QueryString(&quot;strid&quot)

Set session(&quot;oApp&quot = Server.CreateObject(&quot;CrystalRuntime.Application&quot
Path = Request.ServerVariables(&quot;PATH_TRANSLATED&quot
While (Right(Path, 1) <> &quot;\&quot; And Len(Path) <> 0)
iLen = Len(Path) - 1
Path = Left(Path, iLen)
Wend
path=path&&quot;\&quot;&&quot;RPTFILES&quot;&&quot;\&quot;
If IsObject(session(&quot;oRpt&quot) then
Set session(&quot;oRpt&quot = nothing
End if

'On error resume next

Set session(&quot;oRpt&quot = session(&quot;oApp&quot.OpenReport(path & reportname, 1)
If Err.Number <> 0 Then
Response.Write &quot;Always Req *****Error Occurred creating Report Object: &quot; & Err.Description
Set Session(&quot;oRpt&quot = nothing
Set Session(&quot;oApp&quot = nothing
Session.Abandon
'Response.End
End If

session(&quot;oRpt&quot.MorePrintEngineErrorMessages = False
session(&quot;oRpt&quot.EnableParameterPrompting = False
session(&quot;oRpt&quot.DiscardSavedData

Set oMainReportTable = Session(&quot;oRpt&quot.Database.Tables.Item(1)

oMainReportTable.SetLogonInfo &quot;IPPFIMS&quot;, &quot;IPPF_IMS&quot;, CStr(Userid), CStr(Password)

strXRate = trim(Request.QueryString(&quot;XRate&quot)
strCurrID = trim(Request.QueryString(&quot;Curr&quot)
strlan=trim(Request.QueryString(&quot;Lan&quot)
strTransYear = trim(Request.QueryString(&quot;TYear&quot)
strTransType = trim(Request.QueryString(&quot;TType&quot)
strEntity = trim(Request.QueryString(&quot;Entity&quot)
strFundType = trim(Request.QueryString(&quot;FundType&quot)
strLocalXRate = trim(Request.QueryString(&quot;strLocalXRate&quot)
strReportName = trim(Request.QueryString(&quot;Title&quot)
strGroupBy = trim(Request.QueryString(&quot;GroupBy&quot)

'''' *****************************
'''' Assign name to label from the array
'''' *****************************
lblExpenses = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Expenses&quot)
lblIncome = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Income&quot)
lblHmnRec = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;HmnRec&quot)
lblOpCst = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;OpCst&quot)
lblGrants = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Grants&quot)
lblSTotDExpen = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;STotDExpen&quot)
lblOverhdRec = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;OverhdRec&quot)
lblExpOHRec = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;ExpOHRec&quot)
lblIndCstAll = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;IndCstAll&quot)
lblTotCst = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;TotCst&quot)
lblIppf = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Ippf&quot)
lblInt = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Int&quot)
lblNatIncSales = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;NatIncSales&quot)
lblLocDon = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;LocDon&quot)
lblFndTrans = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;FndTrans&quot)
lblTotInc = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;TotInc&quot)
lblSurDef = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;SurDef&quot)
lblAssets = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Assets&quot)
lblPjctNo = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;PjctNo&quot)
'Print
lblPrintDate = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;PrintDate&quot)
lblPrintTime = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;PrintTime&quot)
lblPage = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Page&quot)
lblSubTot = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;SubTot&quot)
lblGrandTot = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;GrandTot&quot)
lblTotal = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Total&quot)
'Header
lblFndType = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;FndType&quot)
lblCurrencyText = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Currency&quot)
lblEntity = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Entity&quot)
lblLedger = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;Ledger&quot)
lblTransTypePZ = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;TransTypePZ&quot)
lblCurrKExp = trim(getLabel(&quot;IMS_RPTLBL&quot;,&quot;CurrKExp&quot)


'''' *****************************
'''' pass labels to Crystal
'''' *****************************


Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;ColorPattern&quot.AddCurrentValue(Cstr(strColorPattern))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;PrintDate&quot.AddCurrentValue(Cstr(lblPrintDate))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;PrintTime&quot.AddCurrentValue(Cstr(lblPrintTime))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Page&quot.AddCurrentValue(Cstr(lblPage))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;SubTot&quot.AddCurrentValue(Cstr(lblSubTot))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;GrandTot&quot.AddCurrentValue(Cstr(lblGrandTot))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Total&quot.AddCurrentValue(Cstr(lblTotal))
'Headers
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;FndType&quot.AddCurrentValue(Cstr(lblFndType))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;CurrencyText&quot.AddCurrentValue(Cstr(lblCurrencyText))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Entity&quot.AddCurrentValue(Cstr(lblEntity))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Ledger&quot.AddCurrentValue(Cstr(lblLedger))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;TransTypePZ&quot.AddCurrentValue(Cstr(lblTransTypePZ))

Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Expenses&quot.AddCurrentValue(Cstr(lblExpenses))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Income&quot.AddCurrentValue(Cstr(lblIncome))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;HmnRec&quot.AddCurrentValue(Cstr(lblHmnRec))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;OpCst&quot.AddCurrentValue(Cstr(lblOpCst))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Grants&quot.AddCurrentValue(Cstr(lblGrants))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;STotDExpen&quot.AddCurrentValue(Cstr(lblSTotDExpen))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;OverhdRec&quot.AddCurrentValue(Cstr(lblOverhdRec))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;ExpOHRec&quot.AddCurrentValue(Cstr(lblExpOHRec))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;IndCstAll&quot.AddCurrentValue(Cstr(lblIndCstAll))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;TotCst&quot.AddCurrentValue(Cstr(lblTotCst))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Ippf&quot.AddCurrentValue(Cstr(lblIppf))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Int&quot.AddCurrentValue(Cstr(lblInt))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;NatIncSales&quot.AddCurrentValue(Cstr(lblNatIncSales))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;LocDon&quot.AddCurrentValue(Cstr(lblLocDon))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;TotInc&quot.AddCurrentValue(Cstr(lblTotInc))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;FndTrans&quot.AddCurrentValue(Cstr(lblFndTrans))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;SurDef&quot.AddCurrentValue(Cstr(lblSurDef))
'Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Assets&quot.AddCurrentValue(Cstr(lblAssets))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;PjctNo&quot.AddCurrentValue(Cstr(lblPjctNo))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;CurrKExp&quot.AddCurrentValue(CStr(lblCurrKExp))


'strCurrID = &quot;BRL&quot;
'strlan= &quot;Lang1&quot;
'strTransYear = &quot;2002&quot;
'strEntity =&quot;408&quot;
'strReportName = &quot;Test&quot;
'strGroupBy = &quot;U&quot;
'strFundtypeFrom = &quot;A&quot;
'strFundtypeTo = &quot;Z&quot;
'strTransTypeFrom = &quot;P&quot;
'strTransTypeTo = &quot;P&quot;

Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;LocalXRate&quot.AddCurrentValue(cstr(strXRate))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;Currency&quot.AddCurrentValue(Cstr(strCurrID))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;ReportName&quot.AddCurrentValue(Cstr(strReportName))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;CountryName&quot.AddCurrentValue(Cstr(strCountry))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;FundSelected&quot.AddCurrentValue(Cstr(strFundSelected))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;EntityName&quot.AddCurrentValue(Cstr(strEntityName))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;@lang&quot.AddCurrentValue(Cstr(strlan))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;@GroupBy&quot.AddCurrentValue(CStr(strGroupBy))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;@TransYear&quot.AddCurrentValue(Cstr(strTransYear))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;@Entity&quot.AddCurrentValue(CStr(strEntity))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;@TransTypeFrom&quot.AddCurrentValue(CStr(strTransTypeFrom))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;@TransTypeTo&quot.AddCurrentValue(CStr(strTransTypeTo))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;@FundtypeFrom&quot.AddCurrentValue(CStr(strFundtypeFrom))
Session(&quot;oRpt&quot.ParameterFields.GetItemByName(&quot;@FundtypeTo&quot.AddCurrentValue(CStr(strFundtypeTo))

'On Error Resume Next

session(&quot;oRpt&quot.ReadRecords

If Err.Number <> 0 Then
Response.Write &quot;Error Occurred Reading Records: &quot; & Err.Description
Set Session(&quot;oRpt&quot = nothing
Set Session(&quot;oApp&quot = nothing
Session.Abandon

Else
If IsObject(session(&quot;oPageEngine&quot) Then
set session(&quot;oPageEngine&quot = nothing
End If
set session(&quot;oPageEngine&quot = session(&quot;oRpt&quot.PageEngine
End If
if strVB = &quot;1&quot; then%>
<!-- #include file=&quot;SmartViewerActiveX.asp&quot; -->
<%else%>
<!-- #include file=&quot;SmartViewerJAVA.asp&quot; -->
<%end if%>

 

[synapsevampire]

DoyleDirk: I'd make sure that you apply the patches, I have 17 subreports in one report without excessive resourcemanagement problems.

-k