Search code examples
ssrs-2008-r2crystal-reports-xi

SSRS 2008 R2: How to design students result spreadsheet using crystal report

I have a dataset with varying number of fields. The first 5 fields are static, while the rest change both in name and total number. How do i handle adding these unknown fields to my table? I thought of writing a code that loops through my fields and inserts a column into my tablix from the 6th field to the last, but i don't know how to go about it - i wonder if that's possible or if it's the best way to solve my problem. I'm also new to ssrs and have written no codes yet. Your ideas are highly appreciated.

Thanks in advance.

Solution

  • I lost some hair trying to solve this. For those who may encounter such problem, although i believe there could be a better, less stressful way, here is what worked for me. Before that, let me try to explain my problem: I wanted to create a report which has both static and dynamic columns (e.g a students grade spreadsheet, where the courses vary with the faculty, session and semester) using dynamic pivot queries. My major issue was how to get my dynamic results from the Database into the pre-designed crystal report. So, here's what i did:

    1. Add an empty DataSet to your app.
    2. Add 2 DataTables to the DataSet: an empty DataTable1 and a strongly-typed DataTable2 with the number of expected columns in your report - i.e if your report requires 10 fields, Datatable2 should have 10 columns. Also, the column names should be uniform for easy coding (e.g DataColumn1, DataColumn2, ...).
    3. Add a report control to your app (in my case, i used crystal report).
    4. Design the report using the columns in DataTable2.
    5. Write a code that gets the dynamic columns (in this case, the course codes) from your database and fill it in DataTable1.
    6. Write another code that gets the grades of the students per course code gotten from step5 above.
    7. Rename the text property of the dynamic text objects in the report with the name of the dynamic columns populated in DataTable1. This was done using a nested for...next loop. This is very necessary; otherwise, the report will not recognize the data passed to it and will display a blank sheet.
    8. Display your report.

    If anyone encounters any difficulties in implementing the above, or requires modification on this, I'm at your disposal.

    Regards.