Problem with group result

[Former Member]

Hello,

I had a report that was grouped on two fields from two tables, by work location and by period (month & year). I get the desired result in the design view perfectly.

Now when I pass a parameter values from a SQL statement by providing the work location and the period I get a different result. I used two different statements to see the result but the result is undesirable. It also works perfect in the database.

Here's the result that I want in the design mode:

Here's the result I get when using this statement, and it also gives results of the 5th month result in page 2.

SELECT * FROM tblPerDiem pd INNER JOIN tblEmployee emp ON pd.employeeID=emp.employeeID
WHERE MONTH(pd.[monthIssued])='4' AND YEAR(pd.[monthIssued])='2015' AND emp.workLocation='Head Office'

And here's the result when using this statement. I used it from the query built when linking the desired tables in the database expert just to see the result.

But it is different from the first one, no duplication of records but it displays the other month's records.

SELECT tblEmployee.employeeID, tblEmployee.firstName, tblEmployee.middleName, tblPerDiem.perDiem, tblPerDiem.accomodation, tblPerDiem.numberofDays, tblPerDiem.advanceDeduction, tblEmployee.workLocation, tblPerDiem.monthIssued
FROM  tblEmployee tblEmployee INNER JOIN tblPerDiem tblPerDiem ON tblEmployee.employeeID = tblPerDiem.employeeID WHERE MONTH(tblPerDiem.[monthIssued])='4' AND YEAR(tblPerDiem.[monthIssued])='2015' AND tblEmployee.workLocation='Head Office'

What seems to be the problem that I haven't noticed? Please I need some insights.

Thank you

[abhilash_kumar · Accepted solution]

Hi Nebil,

If there are multiple rows in the tblPerDiem for each Employee (for the given time period and location), then you will see multiple rows being returned.

You'll need to take a look at the tblPerDiem table to extract the right rows.

-Abhilash

[Former Member · Accepted solution]

Here's the structure of the table. I mean I chose the right feilds from both tables,joined them on employeeID. And filtered them using a period. I don't know, I need your detailed advice, I might be confused right now.

[abhilash_kumar · Accepted solution]

Are these the only fields in this table? If you filter by one of the Emp IDs for the month of Apr and a particular location, how many rows do you see?

-Abhilash

[Former Member · Accepted solution]

I get only one row but the record for May gets populated too. What should I do?

[abhilash_kumar · Accepted solution]

So, this query:

SELECT tblEmployee.employeeID, tblEmployee.firstName, tblEmployee.middleName, tblPerDiem.perDiem, tblPerDiem.accomodation, tblPerDiem.numberofDays, tblPerDiem.advanceDeduction, tblEmployee.workLocation, tblPerDiem.monthIssued 

FROM  tblEmployee tblEmployee INNER JOIN tblPerDiem tblPerDiem ON tblEmployee.employeeID = tblPerDiem.employeeID

WHERE MONTH(tblPerDiem.[monthIssued])='4'

AND YEAR(tblPerDiem.[monthIssued])='2015'

AND tblEmployee.workLocation='Head Office' 

returns records for the Month of May as well?

-Abhilash

[Former Member · Accepted solution]

Yes it does.

[abhilash_kumar · Accepted solution]

Try getting rid of the single quotes around the numbers in the where clause:

WHERE MONTH(tblPerDiem.[monthIssued])=4

AND YEAR(tblPerDiem.[monthIssued])=2015

AND tblEmployee.workLocation='Head Office'

-Abhilash

[Former Member · Accepted solution]

I actually did that a moment ago but it keeps populating both the month's results.

[Former Member · Accepted solution]

Hi Abhilash,

I don't know what happened to the report. I need some help please.

[Former Member · Accepted solution]

Before losing hope,

I used the last version of CR and ended up like this. But I also tested it with previous a project.

It's giving me the same result as this one. I made the exact copy of the project, with the relationship between tables,query being the same but got a different result.

It just displays all the records which I don't know and haven't faced any problem like that before.

I'm a little frustrated to a degree that I may quit seriously.

Do you have any suggestions regarding the version difference. The previous project was made with 3 versions down compared to the latest but both for visual studio 2010.

Thanks

[former_member183750 · Accepted solution]

Hi Nebil

I am pretty sure that Abhilash assumed you were using one of the "stand-alone" versions of CR. E.g.; CR 2013. It is only in the last post that you mention Visual Studio 201 and thus - presumably 'SAP Crystal Reports, Developer Version for Visual Studio .NET"...

It was also never really clear that when you used a previous SP, this worked and now with what ever SP you are on it does not work. So, what SP are you using?

Can you duplicate the issue with a "stand-alone" version of CR? You can download CR 2013 eval from here and try that:

SME Free Trials | SME Software | SAP

Finally, if this is a critical issue, you do have the option of creating a phone case here:

http://store.businessobjects.com/store/bobjamer/en_US/pd/productID.98078100

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow me on Twitter

[Former Member · Accepted solution]

I used service pack 11 for my previous project. And now I'm using SP 13, the last one.

[Former Member · Accepted solution]

I removed and installed the SP 11 and it installed runtime engine for .NET  Framework(64-bit) and version for visual studio. But when I try to run a report it keeps saying load report failed. Why is that happening?

[former_member183750 · Accepted solution]

Three possibilities:

1) The report is not where you are telling the engine it is.

2) The app does not have permissions to the folder where the report is.

3) The %TEMP% folder does not have read / write rights.

- Ludek

[Former Member · Accepted solution]

I wanted to create a way using a command table with parameters for the user to select values (location,month and year). This is more better than writing SQL statement from the program and not having the desired result.

But I have some questions to raise:

• I think this is the pain-free way to manipulate the records right with out grouping the report since it only shows results based on the values provided by the user. Is there any other option better than this?
• Is there a way to provide the user to choose from a drop down list having the month name and year separately? And also to provide the user to select a location that's populated from a SQL statement that is to be used as a parameter.
• How do I populate the selected parameters to a sub report if I have one?

Thank you

[DellSC · Accepted solution]

See my blog - for information about working with commands.  It includes info about how to work with parameters in commands.  Basically you have to create the parameters in the Command Editor, but you can then edit them in the Field Explorer after you've saved the command.

-Dell