# 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:

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

### Assigned Tags

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

Thanks for sharing .

Regards,

AL

Good to know this feature on bex. Useful doc.

Thanks for making and sharing.

Raman

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

Yasemin ULUTURK
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

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

Srikanth.

Thanks for Sharing this.

Regards,

Very Nice Document

Regards,

Sushant

Yasemin ULUTURK
Blog Post Author

Hi all,

Thank you for all of your nice responses.

Cheers

Yasemin...

Good document..!

Regds,

Naveen

Yasemin ULUTURK
Blog Post Author

Thank you Naveen...

Good Document..and useful information

Regards

Naresh

Yasemin ULUTURK
Blog Post Author

Thank you Naresh...

Nicely explored.

Yasemin ULUTURK
Blog Post Author

Thak you Venu...

Useful Info...Many Thanks.

Best Regards,

Naresh K.

Yasemin ULUTURK
Blog Post Author

Thank you Naresh...

Thanks for sharing .

Good to know this property of Bex .

How does it calculates for amount .

Yasemin ULUTURK
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...

Good job ... Really useful document.

Thanks for sharing...

Yasemin ULUTURK
Blog Post Author

Thank you for your nice comment Prashanth.

Hi Yasemin,

Useful info...thanks for sharing.

Regards,

Krishna Chaitanya.

Yasemin ULUTURK
Blog Post Author

Thank you Krishna...

Its good docuement......

Yasemin ULUTURK
Blog Post Author

Thank you Kodanda...

Hi Yaswmin,

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

Thanks,

KD J

Yasemin ULUTURK
Blog Post Author

Thank you very much for your nice comment ðŸ™‚

Yasemin,

Very nice and useful information.

Regards,

Venkatesh

Good Document

Useful Document.

Thanks for sharing .

regards

Sandeep

Thank you for your tip for sharing!!

Regards!

AA

Yasemin ULUTURK
Blog Post Author

Hi Antonio,

Hi Yasmein,

I missed this doc, somehow ðŸ˜‰ Nice workaround. Good efforts by you.

Regards,

Suman

Yasemin ULUTURK
Blog Post Author

Thank you for your nice comment Suman ðŸ™‚

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.

Yasemin ULUTURK
Blog Post Author

Thank you Kumar. It is very nice to have such comments to start a beautiful day ðŸ™‚

Helpful INFO, Thanks for sharing with detail steps.

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

Kind Regards

Manna Das

Really helpful into... Thanks for sharing.... ðŸ™‚

Regards,

Rajesh

Useful Document.