Hello,
I'm using CR9 and I'm fairly new to crystal.
I’m working on a report and one element that should be simple is causing me a lot of grief. My report is a comparison report where I return the data for all clients and groups through a Command query. I have parameters for client (a_client) and group (a_group) which are used to compare their data versus all client and group data. The report is basically done, but I have been asked to display the name of the client and group in the comparison in the report header. What are supplied in the parameters are the IDs of the client and group. For the group, in most cases the user will want to see all groups for that client so the parameter will have a value of “ALL” in that case. If the user selects “ALL” for group, I would like to display “ALL” instead of the group name.
The client name is stored in a table called Client_Information. The group name is stored in a table called Group_Master. The data is stored in SQL Server 2000.
All of the data for this report has been combined into one SQL query for aggregation and performance reasons. I’ve tried 2 approaches in trying to get this data to display:
Approach 1:
I tried adding the columns Client_Name and Group_Name to my query, but the problem is that this returns the client and group names for all clients and groups. I would need to find a way to get the appropriate client and group name from the command. I have tried a few formulas to try and get this information, but the problem is it seems as though I can only get these names if I drop the formulas in the report sections that display data (GroupHeader1, Report Footer, etc.). When I drop the formulas into the report header, I get nothing.
Approach 2:
I’ve tried 2 new Command queries (unlinked to the main Command) to retrieve the client name and group name using the following queries:
Command_1:
SELECT CLIENT_NAME
FROM CS_CLIENT_INFORMATION
WHERE CLIENT_NUMBER = {?a_client}
Command_2:
SELECT [NAME] GROUP_NAME
FROM GROUP_MASTER
WHERE GRP = {?a_group}
Both parameters are string values and the CLIENT_NUMBER and GRP fields are char(4) and char(6), respectively. The Command_1 query works, but Command_2 does not. As it’s displayed right now, Crystal gives me a hard time about Command_2. I have to put quotes around the parameter to get Crystal to accept it, and then it the command still returns nothing. I haven’t even attempted the case of displaying “ALL” for when all groups are specified. Also, this approach slows the report processing to a crawl (15 minutes for 321,000 records, about 10 times slower than what it was with just the one Command query). This report will contain millions of records, so performance is key.
If possible, I would like to use something close to Approach 1 as the performance of the report is way too slow with Approach 2.
Thanks in advance,
Mike
I'm using CR9 and I'm fairly new to crystal.
I’m working on a report and one element that should be simple is causing me a lot of grief. My report is a comparison report where I return the data for all clients and groups through a Command query. I have parameters for client (a_client) and group (a_group) which are used to compare their data versus all client and group data. The report is basically done, but I have been asked to display the name of the client and group in the comparison in the report header. What are supplied in the parameters are the IDs of the client and group. For the group, in most cases the user will want to see all groups for that client so the parameter will have a value of “ALL” in that case. If the user selects “ALL” for group, I would like to display “ALL” instead of the group name.
The client name is stored in a table called Client_Information. The group name is stored in a table called Group_Master. The data is stored in SQL Server 2000.
All of the data for this report has been combined into one SQL query for aggregation and performance reasons. I’ve tried 2 approaches in trying to get this data to display:
Approach 1:
I tried adding the columns Client_Name and Group_Name to my query, but the problem is that this returns the client and group names for all clients and groups. I would need to find a way to get the appropriate client and group name from the command. I have tried a few formulas to try and get this information, but the problem is it seems as though I can only get these names if I drop the formulas in the report sections that display data (GroupHeader1, Report Footer, etc.). When I drop the formulas into the report header, I get nothing.
Approach 2:
I’ve tried 2 new Command queries (unlinked to the main Command) to retrieve the client name and group name using the following queries:
Command_1:
SELECT CLIENT_NAME
FROM CS_CLIENT_INFORMATION
WHERE CLIENT_NUMBER = {?a_client}
Command_2:
SELECT [NAME] GROUP_NAME
FROM GROUP_MASTER
WHERE GRP = {?a_group}
Both parameters are string values and the CLIENT_NUMBER and GRP fields are char(4) and char(6), respectively. The Command_1 query works, but Command_2 does not. As it’s displayed right now, Crystal gives me a hard time about Command_2. I have to put quotes around the parameter to get Crystal to accept it, and then it the command still returns nothing. I haven’t even attempted the case of displaying “ALL” for when all groups are specified. Also, this approach slows the report processing to a crawl (15 minutes for 321,000 records, about 10 times slower than what it was with just the one Command query). This report will contain millions of records, so performance is key.
If possible, I would like to use something close to Approach 1 as the performance of the report is way too slow with Approach 2.
Thanks in advance,
Mike