Dear Support,

I have 2 tables in my report. Both tables have multiple columns in common. I have 3 models in this report. The first model is linked to the first table and the other 2 models are linked to the other table. I need to set restrictions on the data. However, when I set the filter on the common fields for each model, each filter prompt is presented twice, one for every table. For example, I have a field called Date that is present in both tables. If I decide to filter on Date on the 3 models, when I execute the report, I have 2 fields called Date to filter on, each for every table.

Is there a way to combine these 2 fields, so that I prompt the user only for 1 Date and the values are applied for both tables?

Thanks,

Jean-Yves

Dear epf,

Thank you for your reply.

i was able to implement what you suggested but I reached the error you were talking about:

12:11:50 AM Error in model 'model productivity': Got unexpected error when building the SQL statement:
Unable to link all elements using the joins defined...
Add Joins to your Data Source
OR remove elements or restrictions in your model
OR add relevant elements or restrictions in your model.

How do I bypass this issue?

Thanks,

Jean-Yves

Thank you for your help. What you suggested work. However, it created a set of other issues:

1- In case of list items, not date, it only works for 1 value. Multiple or all values crashes
2- The report is now very slow, most probably due to the join, because tables shouldn't be joined since their data is rather different

I have a feeling i am heading in the wrong direction in this.

To solve my issue differently, is it possible to filter the data with a field that is not listed in the view? To make things clearer, I will give you an example:

The database consists of sales data. The table contains a list of customers, grouped by country, region, city and classification. Each customer has multiple types of freezers, and multiple freezers of each type. Therefore, the calculation of average sales is a weighted calculation, based on the number of freezers per type. Also, the table contains records for every month. Therefore, this is the content of my table:
- Country
- Region
- City
- Class
- Date (Month and Year)
- Customer
- FreezerType
- NbFreezers
- Sales

To calculate the average of Sales per customer, i have to
select Country, Region, City, Class, Date, Customer, FreezerType, Sum(Sales*NbFreezers)/Sum(NbFreezers)
from table
Group By Country, Region, City, Class, Date, Customer, FreezerType

On the other hand, I have to create another report that shows the percentage of invoiced customers versus non invoiced customers. An invoiced customer is a customer that has an average sales > 0 for the selected period, regardless of freezer type. So in this case, the view looks like this:

Select Country, Region, City, Class, Customer, CASE WHEN Sum(Sales) > 0 THEN 1 ELSE 0 END IsInvoiced
From table
Group By Country, Region, City, Class, Customer
Where Date between @DateFrom and @DateTo

Originally, I need to put both views in the same report. I tried with the technique you mentioned but I got the problems mentioned above. Moreover, I need to filter by Date also but the Date is not part of the list of the fields in the view.

Therefore, my questions are:

1- Is there a way to include both tables in the same report?
2- Is there a way to filter on a field that is not part of the field list?
3- Is there a different way to do it than the way i am doing it?

I know this is a lot of information but I am really stuck at this point.

Thanks a lot.

Jean-Yves

How would this beta solve my problem? Which part would it address? Would it solve all my issues (list selection, speed, filter from a field not in the list ...)?

The thing is i am deploying this for one of my customers. Therefore, I don't want to solve this issue by generating other issues due to an unstable version. How far off is the release of version 1.7? If i can bypass my issue for now by generating the second table in a standalone report, as long as i can filter by date, even though the date field is not part of the field list, until 1.7 is released, i can work with that.

Thanks,

Jean-Yves

Ok great. Thanks for the beta version. I will test it.

On the other hand, you haven't answered my other question.

How can I make a report with a similar query:

Select Country, Region, City, Class, Customer, CASE WHEN Sum(Sales) > 0 THEN 1 ELSE 0 END IsInvoiced
From table
Group By Country, Region, City, Class, Customer
Where Date between @DateFrom and @DateTo

In other words, how can I create a query with a parametrized where condition? Because, I cannot add the Date in the select columns. The query will group by date also, which i don't want to.

Thanks,

Jean-Yves

What i mean is that i cannot put the date column in the global select:

Select Country, Region, City, Class, IsInvoiced
from (
Select Date, Country, Region, City, Class, Customer, CASE WHEN Sum(Sales) > 0 THEN 1 ELSE 0 END IsInvoiced
From table
Group By Date, Country, Region, City, Class, Customer
) masterTable
Where Date between @DateFrom and @DateTo

The query should be:

Select Country, Region, City, Class, Customer, CASE WHEN Sum(Sales) > 0 THEN 1 ELSE 0 END IsInvoiced
From table
Group By Country, Region, City, Class, Customer
Where Date between @FromDate and @ToDate

Because if i put the date in the group by, then the invoiced customers will be by month and not for all the defined period.

Is it clear?

Ok Thanks.

I will give it a try and let you know if it worked out.

Regards,

Jean-Yves

Normally you can control exactly the SQL you want with the Custom SQL properties...
So build up the final SQL you want, then just do it with the report.

Another option is that you create a view
create YourView as
Select Date, Country, Region, City, Class, Customer, CASE WHEN Sum(Sales) > 0 THEN 1 ELSE 0 END IsInvoiced
From table
Group By Date, Country, Region, City, Class, Customer

then import it as a Source table in your Data Source.

Ok, to have double aggregate in the same select and if you are using SQLServer or Oracle,
you should investigate using SQL aggregate function: over(partition by category order by)

You have some samples posted in the Advanced Tricks section of the forum, and a lot on the internet

It would looks like (no tested):

Select Country,
sum(CASE WHEN Sum(Sales) over(partition by Country) > 0 THEN 1 ELSE 0 END) ) Invoices
From table
Where Date between @DateFrom and @DateTo
group by Country

Try to make in SQL first, then convert it to a report....

Edited by user Monday, April 20, 2015 8:43:40 AM(UTC)  | Reason: Not specified