Parameter question

[Emil Eriksson]

Hello

I need help. 

I have a report that displays Column "FullName" And Column "Antal" (quantity).

This is my query for that.

SELECT CONCAT(u.FirstName, ' ', u.LastName) AS FullName, COUNT(*) AS Antal

FROM Bookings b

INNER JOIN Users u ON u.Id = b.BookedById

INNER JOIN CompanyUsers cu ON (cu.Id = u.Id)

WHERE cu.CompanyId = 2

GROUP BY u.Id

ORDER BY Antal DESC;

I have added two parameter values from the ReportParameter-collection of the report, one for Year and one for Month. These values displays in two dropdowns. I have also added a filter. =Fields.Year = =Parameters.Year AND =Fields.Month = =Parameters.Month

This is my query for that.

SELECT DISTINCT MONTH(BookedDate) AS Month, YEAR(BookedDate) AS Year

FROM Bookings b 

INNER JOIN CompanyUsers cu ON cu.Id = b.BookedById

ORDER BY Year DESC, Month DESC;

The textboxes that displays FullName and Antal is empty when I choose a value from the "dropdowns". But if I remove the filter FullName an Antal displays with the overall quantity.

How can I get my Report to interact with the parameters value in my dropdowns. When I choose a month from the dropdown I want the quantity for exactly that month. Not the overall result. 

Excuse my English

Thanks

/Emil

[Massimiliano Bassili]

The setting of the parameters and filters seems to be correct. Two things you can pay attention to whoever:

• what is the purpose of the second query? Are you using it as datasource for the report parameters or are you saying that you're filtering on database level?
• It is possible that your report ends up empty because of the DateTime parameter. By default the datetime would contain the time part as well (12:00:00 AM), and as you can see the built-in datetime parameter handles date part only. So have that in mind while filtering.

Cheers!

[Emil Eriksson]

Hi again.

I have change my SQL question a bit. And deleted my second query.

SELECT YEAR(BookedDate) AS Year, MONTH(BookedDate) AS Month, CONCAT(u.FirstName, ' ', u.LastName) AS FullName, Count(*) AS Antal

FROM         Bookings b

INNER JOIN Users u ON u.Id = b.BookedById

INNER JOIN CompanyUsers cu ON cu.Id = u.Id

WHERE cu.CompanyId = 1

GROUP BY u.Id, MONTH(BookedDate), YEAR(BookedDate)

ORDER BY Antal DESC; 

What is the best way to make parameters of Year and Month. I want year to be in one dropdown and month in another and when I choose a different yearvalue and a different monthvalue, I want the result to be the quantity (Antal) from exactly that month.

Again, Excuse my English

Thanks

/Emil

[Emil Eriksson]

I solved it. Tanks anyway.

/Emil