Hello,
may be you’ve an idea.
I’ve a report with a field “from_date” (variable) on the output.
if the report has data I get the “from_date” in the report. but if the report doesn’t get data the field “from_data” is empty.
Any ideas to fix this problem ?
If you variable is dependent on data from your query, and there isn’t any data in the query, the variable will not contain any data.
You would need to have a variable that just uses something like CurrentDate() to return a date even if there is no data for the report.
Question Tip: When you are asking a question about a report variable, it will be helpful to those attempting to answer your question if you include the syntax of the variable so others know what you are doing.
So you mean that it’s not possible to get a report with no data where only the selected date is shown…?
You really need to provide more information. What is the “selected date”? Is it a field from the query, a prompt response, a variable – if so, what’s the syntax?
But the short answer is yes, if your query returns no data, it is absolutely possible to have a report with no data. There are options to hide objects if empty or based on formulas.
Are you using the an object from the universe to display the information of are you using the userresponse formula. Because the last should give you the information.
I can see the fields in “variables” and it’ s defined as “MAX starte date” and “MIN start date”…
Does this help ?
We would like to help you, but we need more info from you.
My initial thoughts are to use the UserResponse() function, TimeDim() function, or merge with a calendar table so all the dates in your range are represented whether there is data or not.
I could work up an example showing how, but we need to know more about your situation so we know which one (or maybe something else) fits.
Can you provide any screenshots with proprietary data blurred? Or could you work up a generic example that still shows your issue?
The TimeDim() function will fill in the empty periods in your time data. The problem with that though is if it is the end of your date range that is missing data those dates will not show up. Let me show you what I mean. Here is my sample data from 12/01/2021 through 12/26/2021 (note missing dates) in the table on the left. The table on the right is the my Var Data Date TimeDim variable defined as…
=TimeDim([Data Date]; DayPeriod)
So we have our missing dates in the middle, but not at the end (12/25/2021 and 12/26/2021). To get those dates you need a query to return all the dates in your specified range. If you have a universe based on a calendar you could use that. Free-hand SQL based on a calendar table would suffice as well.
If you have neither of those we can still get it to work using free-hand SQL with a CTE. This is SQL Server syntax. You will have to modify this SQL to work for whatever database platform you have if it isn’t SQL Server.
Here is the SQL…
–Source: Generate a Date Table via Common Table Expression (CTE) | Data and Analytics with Dustin Ryan
;with dates ([Date]) as (
Select convert(date,‘2021-12-01’) as [Date] – Put the start date hereunion all
Select dateadd(day, 1, [Date])
from dates
where [Date] < ‘2021-12-26’ – Put the end date here
)select [Date]
from dates
option (maxrecursion 32767) – Don’t forget to use the maxrecursion option!
Here is a demo.
Now that you have a query returning all of the dates in your range you can merge the date from that query to your Data Date.
You can then put the date object with all of dates or the Merged Date in table with any measures from your pre-existing query and there you have it.
If you need to add dimensions from you pre-existing query I think you will need to create variables for them with Qualification set to “Detail” and the Associated dimension set to “Merged Date” (or whatever you called it). And if you do that I believe you will also need to check “Avoid duplicate row aggregation” check box within the Format Table properties.
Let us know how it goes.
No, it really doesn’t help.
Is your query returning no data so you want a blank report displayed?
Or are you pulling data based on a date range but have some dates where there is no data for that date and you still want to see the date on the report?
Please provide us with a screen shot of a sample of the data you have and what you want the output to look like. Even if you mock it up in Excel with fake data, that’s fine. But without understanding what it is you need to do, we can only guess and that may send you down a wrong path.
