Display all the values of a dimension even with no data

[chr.93]

Hello to all ;D

I have a crosstab like the one attached. if i don't have data for any of the two years displayed for one of the days, that day doesn't appear in the crosstab.
Do you have any ideas how can i display all the days regardless of having data for them?

[BigChris]

It would probably depend on what you've got available to you, but I'd start with a table in your database with all dates in it. In my case that table is called CALENDAR and it's got things like Working_day, Start_of_Week, Period, etc. which has to be populated each year. From there you could create an outer join to your data and use the date from the calendar table for your crosstab.

[chr.93]

It would probably depend on what you've got available to you, but I'd start with a table in your database with all dates in it. In my case that table is called CALENDAR and it's got things like Working_day, Start_of_Week, Period, etc. which has to be populated each year. From there you could create an outer join to your data and use the date from the calendar table for your crosstab.

I did that and this error occured:
XQE-GEN-0018 Query Service internal error has occurred, please see the log for details.

[bus_pass_man]

And when you followed the suggestion and went into the xqe log file, what error did you see?

[chr.93]

And when you followed the suggestion and went into the xqe log file, what error did you see?

Ok I fixed that error. when i run the tabular for this query it shows me every Week Day.
But when i run the report, which has a repeater table ( and of course master link relationship) again those days that i don't have data don't appear. When I remove that they appear.
I need those relationships because i want my crosstab to be repeated. Do you have any idea why it does that?

[Andrei I]

I need those relationships because i want my crosstab to be repeated. Do you have any idea why it does that?

Do you mean you need multiple crosstabs?

[chr.93]

Do you mean you need multiple crosstabs?

I need this crosstab and another one from the same query to be repeated for certain fields.

[Andrei I]

Ok I fixed that error. when i run the tabular for this query it shows me every Week Day.
But when i run the report, which has a repeater table ( and of course master link relationship) again those days that i don't have data don't appear. When I remove that they appear.
I need those relationships because i want my crosstab to be repeated. Do you have any idea why it does that?

You need to make sure that you have all Days returned in the final Dataset for crosstab.
For that you should outer join your Query to some DaySet with all days.
I assume in your case that DaySet could be just as simple Union of 1,2,3,4,5,6,7.
Can you check a query generated for each Crosstab and confirm that it has the outer join to DaySet?

[chr.93]

You need to make sure that you have all Days returned in the final Dataset for crosstab.
For that you should outer join your Query to some DaySet with all days.
I assume in your case that DaySet could be just as simple Union of 1,2,3,4,5,6,7.
Can you check a query generated for each Crosstab and confirm that it has the outer join to DaySet?

the query returns all Days when i run the tabular.
The problem is the report itself that has the master detail relationships. :-\

[Andrei I]

the query returns all Days when i run the tabular.
The problem is the report itself that has the master detail relationships. :-\

Well,  Master-detail report runs a new Details query for each Master row.
You can't see an actual Detail query in the Tabular output.
or Report Designer.
You  would have to log SQL or enable tracing in the DB