cancel
Showing results for 
Search instead for 
Did you mean: 

Crosstab Issue

Former Member
0 Kudos

Dear Experts,

I have a subreport that has a crosstab in the RH section. There are just 5 results on the crosstab which is generated by a SQL command that is not linked with the main query command.

The issue is my data (main query) is being multiplied by the number of records produced by crosstab results. Example: The total hours worked for an EIN should be 80 for a pay cycle. If my crosstab returns 1 record, there is no issue. If the crosstab returns 5 records, the total hours, in this case, is (80*5) = 400.

The hours, in this case, is @GF7.

Is there a way to fix the issue? I do not have the flexibility to take the crosstab to another section.

Thanks

-Sunil

Accepted Solutions (0)

Answers (1)

Answers (1)

abhilash_kumar
Active Contributor
0 Kudos

Hi Sunil,

Tables/Commands that are not joined, result in a cartesian product.

You'd need to separate the Crosstab in another subreport or find a way to create a single SQL query that can accommodate data for the Crosstab as well.

-Abhilash

Former Member
0 Kudos

Thanks, Abhilash.

My main report is simply a mask to pass parameters to the subreport, So I can't add a subreport on subreport.

I have never explored report parts. Is it possible to create a separate report and bring the results as a part of the subreport RH section? you think that's even an option?

Thanks

-Sunil

abhilash_kumar
Active Contributor
0 Kudos

Hi Sunil,

Wouldn't you be able to create a separate subreport to display just the crosstab?

You can then place this subreport above the existing subreport.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

Thank you for the reply. My main report is a mask and the actual report is a sub-report. The main report is modifying the parameters and passing it on the dynamic SQL in the sub-report. Subreport carries all the data.

I can get the crosstab query moved to the main report. The crosstab will never produce more than 5 records made of only 4 columns.

Is there a way to bring these values (all 5 records) from the main report to the sub-report? That will certainly work for me. I tried linking the fields to the sub-report parameter but it brings back the first record. Is there a way to pass multiple values from the main report to the sub-report?

Thanks

-Sunil

abhilash_kumar
Active Contributor
0 Kudos

Hi Sunil,

There isn't a way to move 'rows of data' into a subreport and display them in a crosstab.

I believe you should be able to move the crosstab's query in another subreport and display the crosstab in this subreport.

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

I created a new sub-report (say subreport# 2)that has its command and also created the cross-tab. I can add this cross-tab on the main report.

I am looking at the available fields in subreport #1 and the selected fields in subreport #2 are not available in the report fields or the command.

Here's how the setup looks like:

Not sure how do I get the crosstab on the subreport_with_data.

Thanks

-Sunil

abhilash_kumar
Active Contributor
0 Kudos

Could you elaborate on:

"I am looking at the available fields in subreport #1 and the selected fields in subreport #2 are not available in the report fields or the command."

You mentioned that the 'source' for the crosstab is an independent SQL Query. So, I'm not sure why you need to look at the fields in Subreport 2 (?)

-Abhilash

Former Member
0 Kudos

Hi Abhilash,

Sub-report#1 has all the data. Because of the cartesian issue and as you suggested, I created subreport #2 which has the command and also the crosstab. I did not remove the command and the crosstab from subreport#1, so currently it exists on both the subreports. I wanted to delete it from subreport1 after we could pass the crosstab from subreport#2 to subreport#1.


I was not able to insert the crosstab to subreport #1; it will only let me do it on the main report (Which is just a mask- no data). Since that didn't work, I thought now that the fields are used in crosstab2, it will let me use it in crosstab#1 and hence the statement -


"I am looking at the available fields in subreport #1 and the selected fields in subreport #2 are not available in the report fields or the command."


The screenshot below is from subreport #1 where I was expecting to find fields from subreport#2  which clearly did not work.


I am not sure how would I bring the crosstab from subreport2 into subreport#1.


Thanks

Sunil


abhilash_kumar
Active Contributor
0 Kudos

Fields from Subreport A cannot be 'seen' in Subreport B, and vice versa.

Now that you have the crosstab in another subreport, why do you need it back in the first subreport?

-Abhilash

Former Member
0 Kudos

You are right, Abhilash. I do not need the crosstab in subreportA and I intend to remove it from the subreportA.

But I need the data to go from subreportB to subreportA. As of now it works fine while the user is on the main report. The user would need to see the navigation tree and for that they need to go the subreportA where the data resides. A click on subreportA gets us the navigation tree but now the crosstab is missing.

Is there a way to get the data from subreportB to subreportA?

Thanks

Sunil

abhilash_kumar
Active Contributor
0 Kudos

Hi Sunil,

Unfortunately, there isn't a way to get rows of data into a subreport.

-Abhilash