[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


easy little crosstab formatting problems - and passing variable from VB.NET into Crystal Report

Posted on 2004-08-17
Medium Priority
Last Modified: 2008-02-01
I have made a little crosstab query - that is tied to a live dataset in VB.NET.  All of my data is coming through - totals are fine - etc...  I only have a couple little issues with formatting that I can't seem to get.  The information is production history - by day - for several different machines - all coming from a big database.

One of the main problems is the collumn titles - on the report itself.  The query is made to look for certain machine numbers (mach_nmbr) - and that is what makes up the collumns left to right in the report.  Only - it shows for each collumn header - the machine number.  I know what I want to call each one - I just don't know how to tell it to show for example - instead of 42   as a collumn header - I want it to show #10 Saw...etc  Would also like to center - both the collumn headers and the actual collumn line item detail.

Similar kind of kink in the chart.  I have a basic bar chart - in the page header.  It shows along the x axis each machine number - and along the y axis the total number of pieces for the month.  For both the legend - and the x-axis labels - it is showing the actual machine number - and I want to tell it to use a more commonly known name - such as #10 Saw...etc.

see example of what I'm talking about here:   http://www.fearonenterprises.com/report.JPG

Finally - it would be nice to add 1 little thing... to put the date range the report data includes - in a header or subheader.  The thing is tied to a live dataset - and the date convention that is commonly used here is YYYYMMDD - and there isn't much need to change it.  (would probably screw users up to change date scheme).  The date range could be virtually anything....  there are 2 date/time pickers on the VB.NET main form - and I have variables that keep track of the user picked dates - StartActyDate and EndActyDate.  Can I pass that into Crystal Reports and put it in the header?  How?


Question by:jfgk
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
  • 3
  • 2
LVL 42

Accepted Solution

frodoman earned 2000 total points
ID: 11827045
>>> I know what I want to call each one - I just don't know how to tell it to show for example - instead of 42   as a collumn header - I want it to show #10 Saw...etc  

Probably the best way to do this is to create a formula to translate from number to description and then use the formula for your columns instead of using the field that contains the number.  Your formula for example could be:

if {table.machinenumber} = 42 then
   "#10 Saw"
     if {table.machinenumber} = 50 then
          "Machine Press"
          "Unknown Machine"

Crystal also supports VB-like "CASE" structure if you have lots of items and the if..then is too cumbersome.

>>> Similar kind of kink in the chart

Really the same solution - use the formula in your chart instead of using the db table.  I'm guessing you have "On Change Of: {table.machinenumber}" for your chart now - just change it to "On Change Of: {@FormulaName}" instead and reference the formula from above.

>>> I have variables that keep track of the user picked dates - StartActyDate and EndActyDate.  Can I pass that into Crystal Reports and put it in the header?  How?

First you'll need to create two parameters in your report.  Make them the datatype that you're going to pass - so if you're sticking with yyyymmdd then make them numeric parameters.  Once you've created the parameters you can drag them onto your report just like any other field.  IN this case you'll probably want to drag them onto the report header or page header areas.  The final step is to pass the values from your application to the parameters - here's a code sample for passing numeric values: http://support.businessobjects.com/library/kbase/articles/c2010276.asp

Hope this helps,


Author Comment

ID: 11830362

Hey thanks so much for your help there....   I think I'm almost there.  I got a formula working and it translates the machine numbers to machine names great - just like you said...  but I'm still having a rough time of it here with the parameter passing.

I followed that example you gave the link to - and just to test it first off...   I just kind of copied it exactly... just like it shows...

Dim crParameterFieldDefinitions As ParameterFieldDefinitions
        Dim crParameterFieldDefinition As ParameterFieldDefinition
        Dim crParameterValues As New ParameterValues
        Dim crParameterDiscreteValue As New ParameterDiscreteValue
        crParameterDiscreteValue.Value = CInt(5)
        crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields
        crParameterFieldDefinition = crParameterFieldDefinitions.Item("NumberParam")
        crParameterValues = crParameterFieldDefinition.CurrentValues

(also added the 2 lines to import needed namespaces to the very top... otherwise get syntax errors...etc)

I went in to the report itself and I created a parameter just like it said - called NumberParam - and set it to number...etc...

But then when I actually run the program in VB - and it attempts to make the Crystal Report - it prompts me for a value for NumberParam.

I messed with everything I could possibly think of here - and it still doesn't seem to be passing the actual data from the VB code - to the Crystal Report.

Any guess what might be wrong here?

LVL 42

Expert Comment

ID: 11830464
Sounds like you may not be running the latest version - there was a known issue (i.e. "bug") that has since been resolved.  See this doc for more details: http://support.businessobjects.com/library/kbase/articles/c2010519.asp

Or it may be the timing of when the parameters are being set: http://support.businessobjects.com/library/kbase/articles/c2015079.asp

Finally, if neither of those apply then triple-check the naming and types of your parameters.  You'll see this behavior if you pass a number to a string parameter or if you mis-name the paramter, etc. -- basically anything that leaves an empty param on the Crystal side will force the prompt.



Author Comment

ID: 11830588
Thanks, but I think I found my problem... or at least a little way to get around it...

In the main code - I have a Sub to update the report with new data...etc...    one of the last lines of my report update sub - I wrote in so it would force it to refresh:


and I traced the promt to that line.   After turning off (commenting) that line - seems to be passing paramaters ok - and it still seems to refresh itself ok.  Was having a problem with it refreshing automatically - with the live - interactive choices user can make in the main window - but it seems to be ok now.

Thanks for all your help!  You rock!
LVL 42

Expert Comment

ID: 11831113
Glad to help.

Re your last problem, if you need to refresh you may be able to set "promptOnRefresh" to false - by default when you refresh Crystal looks for new parameters and discards the ones you've already sent.


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

656 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