Crosstab report

[Arumugam]

Hi All,

         I have two crosstab report inside a table..When there is no data for first crosstab report,I want to hide/nullify the second cross tab report?How to achieve that?Please explain it detail.I tried to do with render variable but I am not sure how to write a query to get the output of the first crosstab report

[sdf]

can you provide a mockup of your requirements?
indicate what are included in your crosstab and table.

basically, what you need is a trigger for your render to work.
This will depend on your crosstab1, so you need to figure out a way to tell thr render variable that it has no values.
in particular, we need to know what measure are you using for crosstab1 and what are the possible output values for it.

[Arumugam]

Actually a crosstab1 report contains measure as current period and prior period in columns(based on the user selection in the prompt)..Some 25 dimension as rows..So what i need to know is,is there any way to get the output of the query(say for ex:  result of query1="No data available" then dont render crosstab2)

[sdf]

sorry, i still find it hard to  picture out your report.
do you think you can base it with Total?
or a count maybe, total(1 within set [Measure]) something like this.
if this returns 0, it means there are no data on the query.

[Arumugam]

I tried with count functionality,I am not getting that.I have used this query ([Qry_ExecutiveSummary_Demographics].[PER] is null between ParamDisplayValue('Period End') and ParamDisplayValue('Paid through') in string variable and passed as render in second crosstab but it is throwing an error.

Note : [Qry_ExecutiveSummary_Demographics] is query for first cross tab and "PER" is used as measure in column of the crosstab

[Lynn]

This is a bit of a kludge, but it works as illustrated in the attached spec against the sample package.

This report has Crosstab1 over query1 and Crosstab2 over query2. Both queries contain a dummy data item called "Results Returned" which is set to a hardcoded value 'YES'. If query1 returns no data then there won't be any result set, so there will only be a value of YES when results are actually retrieved.

Associate Query1 to the page and set a master detail relationship between it and Crosstab1. Do the same for Crosstab2.

Run the report and you will see that nothing comes back for either crosstab. Disable the filter in Query1 and run again to see that both crosstabs render when there are results for Crosstab1/Query1.

I did this rather quickly so your mileage may vary. Be sure to test thoroughly for your scenario.

[Arumugam]

Hi Lynn,

          Thanks for your suggestion,problem here is already page is associated with master query for bursting(query subject is pulled from stored procedure).Can I get the output of the query through any function,so that I can user render variable to choose whether render the second crosstab or not

[Lynn]

There is no output for the query as you explained the requirement.

[sdf]

maybe you can put index. as in row numbers. you can just hide this in the actual report.
So if the crosstab returns row >1 means it has value.

[Lynn]

Try using the same technique I already provided except rather than associating the query to the page for master/detail purposes, place the two crosstabs inside a list based on query1 to use for master/detail purposes.