Skip to Content
Author's profile photo Former Member

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:

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

Assigned tags

      40 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Anshu Lilhori
      Anshu Lilhori

      Really helpful and useful information.

      Thanks for sharing .

      Regards,

      AL

      Author's profile photo CH Raman
      CH Raman

      Good to know this feature on bex. Useful doc.

      Thanks for making and sharing.

      Raman

      Author's profile photo Umashankar Poojar
      Umashankar Poojar

      very nice feature and useful too. will have thing on how to implement in current project ;-).

      Thanks for sharing and very well presented.

      Cheers!

      Umashankar

      Author's profile photo Former Member
      Former Member
      Blog 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

      Author's profile photo SRIKANTH GARBHAM
      SRIKANTH GARBHAM

      Good document of showing all master data values even if there is no related data in the data provider. Thanks for sharing.

      Srikanth.

      Author's profile photo Former Member
      Former Member

      Thanks for Sharing this.

      Regards,

      Prasad.

      Author's profile photo Former Member
      Former Member

      Very Nice Document

      Regards,

      Sushant

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi all,

      Thank you for all of your nice responses.

      Cheers

      Yasemin...

      Author's profile photo Former Member
      Former Member

      Good document..!

      Regds,

      Naveen

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you Naveen...

      Author's profile photo Former Member
      Former Member

      Good Document..and useful information

      Regards

      Naresh

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you Naresh...

      Author's profile photo Former Member
      Former Member

      Nicely explored.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thak you Venu...

      Author's profile photo Former Member
      Former Member

      Useful Info...Many Thanks.

      Best Regards,

      Naresh K.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you Naresh...

      Author's profile photo Kamal Mehta
      Kamal Mehta

      Thanks for sharing .

      Good to know this property of Bex .

      How does it calculates for amount .

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you Kamal,

      Since this example covers trend analysis, the amount is cumulative. You can use this property for all types of key figures...

      Author's profile photo Prashanth konduru
      Prashanth konduru

      Good job ... Really useful document.

      Thanks for sharing...

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you for your nice comment Prashanth.

      Author's profile photo Krishna Chaitanya
      Krishna Chaitanya

      Hi Yasemin,

      Useful info...thanks for sharing.

      Regards,

      Krishna Chaitanya.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you Krishna...

      Author's profile photo Phani KV
      Phani KV

      Its good docuement......

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you Kodanda...

      Author's profile photo Former Member
      Former Member

      Hi Yaswmin,

      Nice document, very useful option, after long i learned a quite new and interesting thing in BEx...

      Thanks,

      KD J

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you very much for your nice comment 🙂

      Author's profile photo Former Member
      Former Member

      Yasemin,

      Very nice and useful information.

      Regards,

      Venkatesh

      Author's profile photo Former Member
      Former Member

      Good Document

      Author's profile photo Former Member
      Former Member

      Useful Document.

      Author's profile photo Sandeep a
      Sandeep a

      helpful and useful information.

      Thanks for sharing .

      regards

      Sandeep

      Author's profile photo Former Member
      Former Member

      Helpful Yasemin,

      Thank you for your tip for sharing!!

      Regards!

      AA

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Antonio,

      I am very glad that helped to solve your issue 🙂 Thank you for your comment.

      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Hi Yasmein,

      I missed this doc, somehow 😉 Nice workaround. Good efforts by you.

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you for your nice comment Suman 🙂

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thank you Kumar. It is very nice to have such comments to start a beautiful day 🙂

      Author's profile photo Former Member
      Former Member

      Helpful INFO, Thanks for sharing with detail steps.

      Author's profile photo Former Member
      Former Member

      Again exploring the option gives a new idea of reporting. Thanks for sharing Yasemin.

      Kind Regards

      Manna Das

      Author's profile photo rajesh bethamcharla
      rajesh bethamcharla

      Hi Yasemin ULUTURK,

      Really helpful into... Thanks for sharing.... 🙂

      Regards,

      Rajesh

      Author's profile photo Modadugu Hemanth Kumar
      Modadugu Hemanth Kumar

      Useful Document.