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

[yasemin_kilinc]

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:

Date_Created	Document_ID	Amount_USD	Cuurency
01.01.2013	ABC123	243	USD
01.01.2013	ABC124	123	USD
02.01.2013	ABC125	154	USD
04.01.2013	ABC126	167	USD
04.01.2013	ABC127	126	USD
04.01.2013	ABC128	178	USD
04.01.2013	ABC129	912	USD
04.01.2013	ABC130	644	USD
04.01.2013	ABC131	187	USD
06.01.2013	ABC132	266	USD
06.01.2013	ABC133	152	USD
06.01.2013	ABC134	25	USD
06.01.2013	ABC135	458	USD

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