How can we consume single query in multiple tables

[VimMang]

My requirement is  to build report with two tables - one table contains all detail data and other table contain summary data. To achieve this what needs to be done? Does it needs to create two dataset? Can I consume single query in the both tables?

Generally in SSRS , This can be done in single query to consume in one or more tables.

Thanks in Advance.

[kkriplani]

Hi,
Just create one dataset and use it twice in the tables displaying the relevant / required fields.

Regards,
KKriplani

[VimMang]

Thanks It worked.  I see query executed multiple times.. Having said that I created a report with one dataset and with 3 tables. These tables are referred dataset query. When I checked database logs, It showed me dataset query executed three times before of it referred via three tables.

One more question, How can I add filter condition on table level? Lets say I have to define query on dataset level and requirement would be as below

1. Create two tables- first table should show all the rows from sql query.

2. In second table - It should show based on some condition. lets say date <= today

Thanks in Advance.

 

 

 

 

[kkriplani]

using the same query, you can implement the above requirement using either Conditional Parameters or if the field definition remains the same in both table, just display only the required fields in the table 2. Hope you are aware of the fact that a table need not display all the fields from the query.

[VimMang]

Thanks for response. Let's say as an example – DB table contains hundred records with Id and description. In first table have to display first 20 records (Id <=20) and in other table have to display next ( Id between 21 and  40) . How can I achieve this? Where do I need to use conditional filter to show selected data in table?

 

 

[kkriplani]

Your example clarrifies your requirement. Ignore my previous answer.
What I finally understand is that you want to display first 20 records in table 1 and next 20 records in table two.

The easient way would be to have two separate datasets.

But even without that, what you can try is give all the fields of table 2, a print when condition like ;
$P{Id}>20.

In that case, all the fields of table two will only be printed as and when the ID is greater than 20.

Hope this helps,
KKriplani
 

[VimMang]

It would be nice to put filter condition on table level instead of creating new dataset. Where do I put condition like $P{Id}>20 on table 2? 

[kkriplani]

Select the field and in the properties bar, add the "Print when Expression" with the required condition.
Basically, the fields will only print if the value of the parameter exceeds a certain value, in your case, 20.