Daily trend data on graph or table when there is not day by day data.

Sometimes we need to show on the table day by day trends (may be cumulative, may be non-cumulative). Consider you need to show a graph day by day for a week, and you have only data for a few days of the week. You need to show each day on the graph. I will try to explain how we can design it with the query designer. I will explain it with an example where we want to show the cumulative trend of amount each day. Suppose we have the data set:

Suppose you want to show a report where you need to show the day by day cumulative data for the first 10 days of the year. As you may see in the data set, there is no data for 03.01.2013, 05.01.2013 and the other days after 06.01.2013. Here is query view showing what cube includes:

For building the query, 0calday is added in rows and 0amount is added in columns. To show a cumulated value,  cumulative check in the calculations tab of the properties pane for 0amount is checked:

0calday is restricted for the first 10 days of year 2013. This is just for making it clear for a time period. You can use any other variable for restrictions. For simplicity of this example first 10 days are filtered:

As we run the query we get:

This is not we want to show. We want to show the data  for each day cumulatively. So whatwe do is we go to the characteristic properties pane of 0calday, select advanced tab and change the access type for result values  as “master data”.

Now when we run the query we get:

With this view we can show the graph as:

We can use this type of queries not only for daily or monthly trends but also for the tables where we want to show all master data values even if there is no related data in the data provider.

Hope it helps

Yasemin Uluturk