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:

1.JPG

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:

2.JPG

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:

3.JPG

As we run the query we get:

4.JPG

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”.

5.JPG

Now when we run the query we get:

6.JPG

With this view we can show the graph as:

7.JPG

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

To report this post you need to login first.

40 Comments

You must be Logged on to comment or reply to a post.

    1. Yasemin ULUTURK Post author

      Hi Umashankar,

      I am very glad to hear that 🙂 The best way to show the master data use was to show it on a daily trend. I did not mention too much but this property may be used in many reports such as for example showing the absences of the employee. You can show whole employee list and using the related infoproviders add the absent times of the employees, then even if the employee has no absent time s/he may be seen on the report. In such cases we need to make sure that we don’t use zero suppression 🙂

      Have a great day

      (0) 
  1. Kumar S

    Hi Yasmein,

    Thank you for sharing a nice feature to show something in report from master data. Your blog not only informative but also fulfilling real time requirements 🙂 . Great.

    (0) 

Leave a Reply