Posted: 13 Dec 2016 3:27 EST Last activity: 15 Dec 2016 3:46 EST
Issue in joining sub report in main report
I am creating one Report Definition and calling one sub-report inside it. In the sub report I am taking count of a column. When I am running the sub-report stand alone then it is giving the correct count. But when running from the main RD, it is showing all the counts of the main report, not particluar count of that sub-report. I have changed the joing conditions in the sub-report configuration as all rows from sub report, but still not wokring. Could you please help me.
***Updated by moderator: Lochan to add Categories***
I tried using Class Join, but as too many records are present so when I am running that 30 mins timeout issue is coming. That's why I tried to use Sub-report.
My scenario is like in one report one column is present and in another report all details are present associated with that column. I have to take count of that particular column from that other report.
I am still unable to follow your use case. This is my understanding from what I read: The sub report returns only 1 column (count?) and you want to show the count along with other columns in the main report. Since the main report returns multiple rows and the sub report is returning only 1 row, the same count is getting repeated for all rows of the main report? If yes, that is expected.
Alternatively, I suggest you provide an example with data on what your expected output should look like or the direct SQL query that you would have executed against the database to get this data (but are not able to get the same by configuring reports).
My scenario is like in one table customer name is present, which is getting saved in three different tables based upon some condition. Now I have to get the count from each of the three table for each customer. One customer can reside in more than one table. So to get the count I am using sub-report, but it is giving whole count from main table not from individual table. If I am running sub-reports individually then count is coming right.
Based on the above, are you expecting your query to look like this?
SELECT cust.customer_name AS "cust_name", cust.other_property AS "other_prop", (SELECT count(*) FROM schema.table1 "t1" WHERE t1.name = cust.customer_name) AS "count_t1", (SELECT count(*) FROM schema.table2 "t2" WHERE t2.name = cust.customer_name) AS "count_t2", (SELECT count(*) FROM schema.table3 "t3" WHERE t3.name = cust.customer_name) AS "count_t3" FROM schema.customer "cust"
For one column I have added distinct count, but in the very first row it is coming as a sum, though when I am expanding it is showing distinct count only. Do in the first row usually it shows as sum of all the values in that column?