none
Generate report programatically from a Cube

    Question

  • Hi

    Because my parameters are not static, I need to generate reports dynamicaly. I created rdlc using xml writer, but it is not taking MDX query and showing exception. 

    Another solution I am searching is , is there any tool, as flexible as excel that can be used with asp.net applications and also programmable, because we can't use excel itself for commercial purposes.

    Expecting valuable replys....It is very urgent....

    Thanks in advance

    Maju
    MJVP
    Friday, December 04, 2009 9:54 AM

Answers

All replies

  • Hi Maju,

    I would like to help you to solve the issue.

    However, could you please post some sample code and the exception? That will help us to solve the issue.

    By the way, I would suggest you using the Report Language Definition Language to create a report dynamic.

    For more information, please see:
    Tutorials for Reporting Services Development: http://msdn.microsoft.com/en-us/library/aa337423(SQL.90).aspx


    Thanks,
    Jin Chen
    Jin Chen - MSFT
    Monday, December 07, 2009 9:53 AM
  • Jin , very thanks for your kind reply !

    Actually I am trying to create rdl file as says in the link you provided. The limitation using rdl file is, we have to diploy the created rdl file to a report server in order to see the reports for the clients, I think that is not possible at run time, because we have to create and diploy the report according to the selected parameters each time.

    If I am using rdlc instead of rdl, is it possible to see the report without diploy ? But here also I failed to create a connection to a Cube.

    Ultimately I want to know whether it is possible to create a report generating tool using SQL reporting service like excel reporting ?

    Here I am giving the code for connection and command text to create the rdl file, 
         public void OpenConnection()
            {
                // TODO: Open a connection to the sample database
                // Create a connection object
                m_oledbConnection = new OleDbConnection();
                            
                // Create the connection string
                
                m_connectString = "Provider=MSOLAP.3;Data Source=Dev;Integrated    
                Security=SSPI;Initial Catalog=Var_Phase1;Location=Dev";
                m_oledbConnection.ConnectionString = m_connectString;
     
                // Open the connection
                m_oledbConnection.Open();
     
            }
     
            public void GenerateFieldsList()
            {
                // TODO: Generate a list of fields for a report query
                 OleDbCommand command;
                OleDbDataReader reader;
     
                // Executing a query to retrieve a fields list for the report
                command = m_oledbConnection.CreateCommand();
                m_commandText = "SELECT NON EMPTY { [Measures].[BOB NETAMOUNT] } ON 
                COLUMNS, NON EMPTY { ([BOB LOCATION MASTER].[BOB CITY].Ernakulam) } 
                ON ROWS FROM [Var Phase1] ";
     
                command.CommandText = m_commandText;
     
                // Execute and create a reader for the current command
                reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
     
                // For each field in the resultset, add the name to an array list
                m_fields = new ArrayList();
                for (int i = 0; i <= reader.FieldCount - 1; i++)
                {
                    m_fields.Add(reader.GetName(i));
                }
     
            }


    When I open this file in repor designer the following error is showing  

    Warning   -     " Data at the root level is invalid. "

    Expecting your kind reply...

    Thanks,

    Maju
     

    MJVP
    Monday, December 07, 2009 12:29 PM
  • Hi

    Can anybody tell me what is wrong in the above rdl generator code ?

    thanks

    MJVP
    Tuesday, December 08, 2009 11:49 AM
  • Hi Maju,

    "Data at root level is invalid" means the xml file is not invalid.

    Could you please open the created RDLC with XML Editor to check if the report is started with "Report" element?

    If possible, could you please send me a sample project? I don't think the issue is caused by the code you have posted before.
    You can send the file to sqltnsp@microsoft.com, and use the following syntax:

    Hi Jin,
    Thread URL: http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/531d6cf2-2ea8-4855-8a9e-df8c3d4287d0/
    XXX



    Thanks,
    Jin Chen
    Jin Chen - MSFT
    • Marked as answer by Maju V Poulose Thursday, December 10, 2009 11:39 AM
    Wednesday, December 09, 2009 7:03 AM
  • Hi Jin

    Thanks for your reply !

    As you said I checked and restructured the xml code, now the error has cleared out and I can open the rdl in report designer. But when I took the Data tab in the designer some error "The data extension MSOLAP.3 was not found in the confirguration file RSReportDesigner.config Add the extension to the configuration filed before proceeding or choose another data extension" is showing. Let me work on this and if if not getting corrected I will send you the code.

    Thanks

    Maju


    MJVP
    Wednesday, December 09, 2009 12:32 PM
  • Hi Maju,

    By default, the identity of the SQL Server Analysis Services is OLEDB-MD. So while using SQL Server Analysis Services as data source data entension, please use the name "OLEDB-MD" instead of "MSOLAP.3" in the code.

    Thanks,
    Jin Chen
    Jin Chen - MSFT
    • Marked as answer by Maju V Poulose Thursday, December 10, 2009 11:39 AM
    Thursday, December 10, 2009 6:40 AM
  • Hi Jin,

    Very thanks for your valuable reply !

    This solved my issue.

    Thanks

    Maju
    MJVP
    Thursday, December 10, 2009 12:42 PM