Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dachish
Starting Member
12 Posts |
 Posted - 2009-06-15 : 11:26:23
|
| All,I know this is a SQL forum but I'm hoping someone might have experience with MySQL and Reporting Services =).I'm trying to do a rather simple thing but since I'm still just getting started with all of those, I'm probably missing something small, yet critical. We use a ticketing system and I have made a basic report to show the top 5 reported issues per week.My query is simple:------------------------------SELECTcount(DISTINCT mantis_bug_table.id) as 'Total',mantis_bug_table.category AS 'Category',mantis_bug_table.date_submitted AS 'Date Submitted'FROMmantis_bug_table,mantis_user_tableWHEREmantis_bug_table.handler_id = mantis_user_table.id ANDmantis_bug_table.date_submitted BETWEEN '2009-06-01' AND '2009-06-05'GROUP BY mantis_bug_table.categoryORDER BY count(DISTINCT mantis_bug_table.id) DESCLIMIT 5------------------------------I have also built a report in VS2005 Reporting Services and it displays what I need. However, I was trying to be more efficient and instead of me running it each week and having to go in and update the date range, I wanted to add some report parameters so that when the report is run, you can simply select the dates from a calendar.To do this I have changed:mantis_bug_table.date_submitted BETWEEN '2009-06-01' AND '2009-06-05'tomantis_bug_table.date_submitted BETWEEN 'DateStart' AND 'DateEnd'I originally ran into the problem of making it:mantis_bug_table.date_submitted BETWEEN '@DateStart' AND '@DateEnd'but it turns out the ODBC does not allow named parameters. I managed to get around this by simply using :mantis_bug_table.date_submitted BETWEEN '?' AND '?'And it then allowed me to define them in the reports parameter menu.I defined them as- DateStart and DateEnd- Made them DateTime data types, and set their prompt.After defining those it is now:mantis_bug_table.date_submitted BETWEEN 'DateStart' AND 'DateEnd'Everything runs without an error now but it doesn't actually pull any data when I select from a date range.Does anyone have any idea what step I may have missed? This is my first attempt doing anything with parameters and I've done a bit of googling but so far have had no luck finding what I may have done wrong.Thanks! |
|
|
dachish
Starting Member
12 Posts |
Posted - 2009-06-15 : 11:31:40
|
I will note someone else pointed out the following to me:As you say, ODBC does not allow named parameters. However, if MySQL has an ODBC driver I would think it would be similar to other ODBC drivers especially as far as dealing with parameters. I could be wrong though. Anyway, looking at your query I have concern with how you defined the parameters. mantis_bug_table.date_submitted BETWEEN '?' AND '?' It should be this:mantis_bug_table.date_submitted BETWEEN ? AND ? RS should be automatically creating matching report parameters. Note that with unnamed parameters they get mapped in the order in your statement. Check the mapping in the dataset.I tried that with no luck.I will tell you that the reason I use '?' is that I learned when I first started using this database (this is my first db experience and I'm learning as I go) that if I did not use ' ', I would often get incorrect data. For example, if I do a query looking for dates, and I do: date_submitted = 2009-01-01, it will give me 0 results. If I use 'date_submitted = 2009-01-01' it gives me what I need. I'm not sure if this is normal. This database can act especially funny at times...I can never tell if it's the DB or me being new.Anyway, back to the topic!I tried using ? and ?...it gives me the same results.If I start the query and do ? and ?, it will give me:  Which I then define as: It seemed to make sense to me that you would simply use the names I defined instead of ? at that point. I tried it with both '?' and ? and when I defined them I tried it as both 'DateStart' and DateStart.Also, when I simply run the query instead of previewing the report, and I've used ? or '?' I get this prompt:  I've been leaving that blank.Also, here is my report. I'm not sure if it matters but there you have it. At the very least you can see how new I am =) |
 |
|
|
dachish
Starting Member
12 Posts |
Posted - 2009-06-15 : 16:55:52
|
| Well it looks I was trying to make things too difficult. It was one of things that I struggled with forever and as soon as I ask, I stumble upon the answer.If I do the query to begin with as:mantis_bug_table.date_submitted BETWEEN '?' AND '?' then it enters the two parameters as "Parameter1" and "Parameter2" in the Report Parameter menu. If I simply leave those names and change the rest, it works fine. So I guess now what I don't understand is how it stores/decides that info? Does it simply compare the parameters menu with the query and say "the first ? in the query matches with the first parameter in the parameters list" ? |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2009-06-15 : 17:09:19
|
| for mysql, why not try www.sitepoint.com/forums or www.dbforums.com |
|
|
|
dachish
Starting Member
12 Posts |
Posted - 2009-06-15 : 17:30:33
|
| I'll give those a whirl, thanks Afrika. |
|
|
|
|
|
|
|
|
Reporting Services, Report Parameters and MySQL