This is a migrated thread and some comments may be shown as answers.

Parameter question

3 Answers 74 Views
General Discussions
This is a migrated thread and some comments may be shown as answers.
Emil Eriksson
Top achievements
Rank 1
Emil Eriksson asked on 05 Aug 2009, 11:58 AM
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


3 Answers, 1 is accepted

Sort by
0
Massimiliano Bassili
Top achievements
Rank 1
answered on 05 Aug 2009, 01:15 PM
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!
0
Emil Eriksson
Top achievements
Rank 1
answered on 06 Aug 2009, 02:24 PM
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
0
Emil Eriksson
Top achievements
Rank 1
answered on 07 Aug 2009, 10:23 AM
I solved it. Tanks anyway.
/Emil
Tags
General Discussions
Asked by
Emil Eriksson
Top achievements
Rank 1
Answers by
Massimiliano Bassili
Top achievements
Rank 1
Emil Eriksson
Top achievements
Rank 1
Share this question
or