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

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?


Who is Participating?
>>> 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,

jfgkAuthor Commented:

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?

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.


jfgkAuthor Commented:
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!
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.