'How to display an OLAP Report with Crystal Report and VB.NET 2005 ?' Thread RSS Feed

How to display an OLAP Report with Crystal Report and VB.NET 2005 ?

Mon, 04 Jan 2010 08:34:34 -0700

I used Ms SQL Server 2005, Ms Analysis Service 9.0.
And I want to make an OLAP report with Crystal report and displaying the report into my form in VB.NET 2005.
Please tell me how, if you have any suggestion,
coz I havent found any working articles yet.

thanks

Re: How to display an OLAP Report with Crystal Report and VB.

Tue, 05 Jan 2010 11:09:47 -0700

Read and fully understand this:
http://www.aspfree.com/c/a/Database/Crystal-Report-from-OLAP-Data-Introduction/

Then read and fully understand this:
http://www.codeproject.com/KB/recipes/CrystalReports_in_VBNET.aspx

Once you read and fully understand those, you should now be able to figure out how to create a Crystal report for your OLAP data and display it in a Vb.Net form.

I can help you with code that isn't working, but I cannot teach you how to do this... Good luck!
Sean Campbell
firesickle.com

Re: How to display an OLAP Report with Crystal Report and VB.

Tue, 05 Jan 2010 22:31:41 -0700

thanks for your reply.
I've managed to display data from the cube in crystal report.

But I failed when displaying it to the vb.net
this is the code

Friend Function ViewReport(ByVal sReportName As String, _
        Optional ByVal sSelectionFormula As String = "", _
        Optional ByVal param As String = "") As Boolean

        'Declaring variablesables

        Dim intCounter As Integer
        Dim intCounter1 As Integer

        'Crystal Report's report document object

        Dim objReport As New _
            CrystalDecisions.CrystalReports.Engine.ReportDocum
ent

        'object of table Log on info of Crystal report

        Dim ConInfo As New CrystalDecisions.Shared.TableLogOnInfo

        'Parameter value object of crystal report 

        ' parameters used for adding the value to parameter.

        Dim paraValue As New CrystalDecisions.Shared.ParameterDiscreteValue

        'Current parameter value object(collection) of crystal report parameters.

        Dim currValue As CrystalDecisions.Shared.ParameterValues

        'Sub report object of crystal report.

        Dim mySubReportObject As _
            CrystalDecisions.CrystalReports.Engine.SubreportOb
ject

        'Sub report document of crystal report.

        Dim mySubRepDoc As New _
            CrystalDecisions.CrystalReports.Engine.ReportDocum
ent

        Dim strParValPair() As String
        Dim strVal() As String
        Dim index As Integer

        Try

            'Load the report

            objReport.Load(sReportName)

            'Check if there are parameters or not in report.

            intCounter = objReport.DataDefinition.ParameterFields.Count

            'As parameter fields collection also picks the selection 

            ' formula which is not the parameter

            ' so if total parameter count is 1 then we check whether 

            ' its a parameter or selection formula.


            If intCounter = 1 Then
                If InStr(objReport.DataDefinition.ParameterFields(0).
ParameterFieldName, ".", CompareMethod.Text) > 0 Then
                    intCounter = 0
                End If
            End If

            'If there are parameters in report and 

            'user has passed them then split the 

            'parameter string and Apply the values 

            'to their concurrent parameters.


            If intCounter > 0 And Trim(param) <> "" Then
                strParValPair = param.Split("&")

                For index = 0 To UBound(strParValPair)
                    If InStr(strParValPair(index), "=") > 0 Then
                        strVal = strParValPair(index).Split("=")
                        paraValue.Value = strVal(1)
                currValue = _ 
                    objReport.DataDefinition.ParameterFields(strVal(0)
).CurrentValues
                        currValue.Add(paraValue)
                        objReport.DataDefinition.ParameterFields(strVal(0)
).ApplyCurrentValues(currValue)
                    End If
                Next
            End If

            'Set the connection information to ConInfo 

            'object so that we can apply the 

            'connection information on each table in the report

            ConInfo.ConnectionInfo.UserID = ""
            ConInfo.ConnectionInfo.Password = ""
            ConInfo.ConnectionInfo.ServerName = "localhost"
            ConInfo.ConnectionInfo.DatabaseName = "IBU_OLAP"

            For intCounter = 0 To objReport.Database.Tables.Count - 1
                objReport.Database.Tables(intCounter).ApplyLogOnIn
fo(ConInfo)
            Next

            ' Loop through each section on the report then look 

            ' through each object in the section

            ' if the object is a subreport, then apply logon info 

            ' on each table of that sub report


            For index = 0 To objReport.ReportDefinition.Sections.Count - 1
                For intCounter = 0 To _
                    objReport.ReportDefinition.Sections(index).ReportO
bjects.Count - 1
                    With objReport.ReportDefinition.Sections(index)
                        If .ReportObjects(intCounter).Kind = _
                        CrystalDecisions.Shared.ReportObjectKind.Subreport
Object Then
                            mySubReportObject = CType(.ReportObjects(intCounter), _
                              CrystalDecisions.CrystalReports.Engine.SubreportOb
ject)
                            mySubRepDoc = _
                     mySubReportObject.OpenSubreport(mySubReportObject.
SubreportName)
                            For intCounter1 = 0 To mySubRepDoc.Database.Tables.Count - 1
                                mySubRepDoc.Database.Tables(intCounter1).ApplyLogO
nInfo(ConInfo)
                                mySubRepDoc.Database.Tables(intCounter1).ApplyLogO
nInfo(ConInfo)
                            Next
                        End If
                    End With
                Next
            Next
            'If there is a selection formula passed to this function then use that

            If sSelectionFormula.Length > 0 Then
                objReport.RecordSelectionFormula = sSelectionFormula
            End If
            'Re setting control 

            rptViewer.ReportSource = Nothing

            'Set the current report object to report.

            rptViewer.ReportSource = objReport

            'Show the report

            rptViewer.Show()
            Return True
        Catch ex As System.Exception
            MsgBox(ex.Message)
        End Try
    End Function

and this in my form load

 
Dim objForm As New Form1
objForm.ViewReport("C:\Report1.rpt", , "")
objForm.show()

Im not sure about this

ConInfo.ConnectionInfo.DatabaseName = "IBU_OLAP"

IBU_OLAP is my database name
I try replacing it with my Microsoft Analysis Service Project Name,
But neither worked

then I traced it, and I got "Invalid report file path"
But Im sure 100% that the path is correct

Do you have any idea about this?

thanks

Re: How to display an OLAP Report with Crystal Report and VB.

Wed, 06 Jan 2010 07:15:05 -0700

Did it throw an exception? (crash) If so, can you paste the Line of code it failed on, and the full text from the "Details >>" section of the Exception message? That will help me determine exactly what is happening.

Re: How to display an OLAP Report with Crystal Report and VB.

Wed, 06 Jan 2010 20:44:50 -0700

this is the exception message
objReport.DataDefinition.ParameterFields(0)

"Invalid index. (Exception from HRESULT : 0x8002000B (DISP_E_BADINDEX))"

thanks for taking your time to see my problem...

I want to ask one thing, Does Crystal Report Viewer in VS.net 2005 support the Multidimensional-Cube OLAP report?

Re: How to display an OLAP Report with Crystal Report and VB.

Thu, 07 Jan 2010 06:39:27 -0700

Is that the full text from the exception? I was expecting to see something like this:

Source File: c:\Documents and Settings\Marelizee\My Documents\Visual Studio 2005\WebSites\MSDNTutorials\Default.aspx.cs Line: 41

Stack Trace:

[COMException (0x8002000b): Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))]
CrystalDecisions.ReportAppServer.DataDefModel.Fiel dsClass.get_Item(Int32 Index) +0
CrystalDecisions.CrystalReports.Engine.ParameterFi eldDefinitions.get_Item(Int32 index) +77
CrystalDecisions.CrystalReports.Engine.ParameterFi eldDefinitions.get_Item(String fieldName) +98
CrystalDecisions.CrystalReports.Engine.ReportDocum ent.SetParameterValue(String name, Object val) +266
_Default.ConfigureCrystalReports() in c:\Documents and Settings\Marelizee\My Documents\Visual Studio 2005\WebSites\MSDNTutorials\Default.aspx.cs:41
_Default.Page_Init(Object sender, EventArgs e) in c:\Documents and Settings\Marelizee\My Documents\Visual Studio 2005\WebSites\MSDNTutorials\Default.aspx.cs:76
System.Web.Util.CalliHelper.EventArgFunctionCaller (IntPtr fp, Object o, Object t, EventArgs e) +31
System.Web.Util.CalliEventHandlerDelegateProxy.Cal lback(Object sender, EventArgs e) +68
System.Web.UI.Control.OnInit(EventArgs e) +89
System.Web.UI.Page.OnInit(EventArgs e) +28
System.Web.UI.Control.InitRecursive(Control namingContainer) +459
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1743

From what I see in your code, you have a try catch which is probably handling the error and you are copying what is displayed from Ex.Message.

Comment out your Try Catch Block, and allow the program to crash with an exception. Click the "Details>>" button and copy the text from in there.

-----
From the information you provided, it seems that objReport.DataDefinition.ParameterFields(0) does not exist, which is something you refer to in a few different lines of code. We need to find out exactly which line of code you are crashing on (it should be apparant once you remove the try catch block).

------
I am sorry, I cannot answer your question. I have no experience with Crystal Reports whatsoever, much less trying to connect it to OLAP data. I have a gut feeling that you are referring to SAP BW Cubes? I would think that a multidimensional cube would act like a single table of data (or more correctly a Sql DB View), but that is pure speculation, I really do not know the answer.

-Sean Campbell
firesickle.com

Re: How to display an OLAP Report with Crystal Report and VB.

Thu, 07 Jan 2010 07:38:02 -0700

this is what happen when I remove the try and catch block
I took the whole screen shot

Re: How to display an OLAP Report with Crystal Report and VB.

Thu, 07 Jan 2010 08:07:49 -0700

I am not quite following what you just said?

Re: How to display an OLAP Report with Crystal Report and VB.

Thu, 07 Jan 2010 08:13:22 -0700

this is the first image

Attachment: 1.png (25815 bytes | downloaded 106 times)

Re: How to display an OLAP Report with Crystal Report and VB.

Thu, 07 Jan 2010 08:14:46 -0700

and this is the second

Attachment: 2.png (18376 bytes | downloaded 92 times)

Re: How to display an OLAP Report with Crystal Report and VB.

Wed, 29 May 2013 12:33:24 -0700

Hi,
I do have the same requirement to render the Crystal Olap report in my asp.net web form crystal report viewer.
SAP people are saying it is not pooible to integrate with VS 2008 with CR 2008. Will you be able to guide me.
Any of you input is highly appreciated
Thank you,
Usha

Re: How to display an OLAP Report with Crystal Report and VB.

Wed, 29 May 2013 15:50:01 -0700