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

Posted on 2004-08-17
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:

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
  • 3
  • 2
LVL 42

Accepted Solution

frodoman earned 500 total points
Comment Utility
>>> 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:

Hope this helps,


Author Comment

Comment Utility

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

Comment Utility
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:

Or it may be the timing of when the parameters are being set:

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

Comment Utility
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

Comment Utility
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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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 …
Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now