For a couple of hours I have been banging my head against the wall (still not literally, but I will start soon if I don't find a solution). For me, the problem I am facing actually is not a problem, cannot be a problem, because I use subreports successfully in another menu of the same Silverlight project, that I am working on. I even cannot define what it is - an issue, a problem, an evil... Crystal Reports sometimes can be ... well, interesting. Including the fact, that I have been working with CR for only 2 weeks (still learning while working) and the situation is just wonderful. Anyway.
First of all, I am trying to basically have two copies of a report per document. In the reports' Command (via Database Expert) I simply have select * from Documents where {?filter}. I give the where clause from C#, before I call ExportToDisk() method. There is an <sdk:DataGrid></sdk:DataGrid>, where I take all marked rows (docs) from, their RowIDs to be precise, create an 'in ()' clause, give it to the ?filter param of the report and it is working as I expect.
I searched on the web and the best I could find was to have a 'dummy' main report with its Section divided into two parts and insert my report as a subreport into those parts. So far, so good. I did the 'linking of params' (Change Subreport Links... option), as I did in the other menu and ... no data. Only the labels are showing. I checked 3 docs to be reported and i got 3 * 2 = 6 pages in total, but all with empty fields.
Final conclusion after more than an hour of testing: if I have a report and set it's Command via Database Expert to select * from Documents where RowID = '<someGuid>' or select * from Documents where {?filter}, then I always get all fields of the documents (like Customer, Code, etc.) - in the first case only one page for the particular doc, in the second case - one page per doc.
If I try as subreport and select 2 docs from the grid, I get four 'empty' pages - two per doc. No need to say, that if the subreport's command is: select * from Document where {?filter} and I link the filter with the main report, it's not working.
To my surprise, it seems to be working!... in one particular use case: if I set the command of the subreport to select * from Documents where 1 = 1. In this case, I get say 2000 pages for the 1000 docs I have in the table - all with all fields filled with data.
So this is my 'problem'!? It's so weird, that I even don't know should it be called a 'problem' or something else. Any help would be greatly appreciated.
edit: To summarize a bit: I have a main 'dummy' report with its command set to select * from Documents where {?filter}. I divide its Details section into two parts and I insert a subreport in each part. The command of the subreport is select * from Documents where doc_RowID = '{?filter}', where the RowID should come from the main report via linking. I tried in the subreport: select * from Documents where doc_RowID = '<someGuid>' and again I get pages with labels only. It 'works' if I have where 1 = 1 in subreport.
In main report, I even tried to group the docs by doc_RowID column and put the subreport in the GroupHeader and GroupFooter section. No success.
edit from today (21.12.2016): I tried to set the Command of the subreport to filter the data by some field of a left-joined table docItems as di, which shows the items bought: where di.Price > 5. When I run the query with this where clause in SQL Server Management Studio, I get a result of 7 records in 3 documents. In the report, I get 260 pages, which means, that it returns 130 documents within CR. WTF is going on here... :O
edit3: The customer's SQL server is down, so I decided to keep testing on our server. The problem disappeared. This may sound like a good news, but eventually, I will have to deploy my work to their server...
I have found the Magic for this. I call it 'Magic', because I don't know neither how exactly did it help in the situation, nor would it help next time I face a similar issue. I hope it doesn't happen.
Read this thread - there I found the solution. Just in case it gets dead, I will post it here:
Open the report in the CR XI designer.
Select Database -> Set Datasource Location.
Set Datasource Location window will appear and in the replace with section connect to the database and replace these tables with the tables in the current datasource by clicking the update button.
After updating close the Set Datasource Location window.
Go to 'Database menu' and Click 'Verify Database' and click on ’OK’.
Preview the report and the save the report.